QWEST DUF

SPLITTER
PROJECT

Submitted By: Project Guide:

ANKITA AGARWAL Mr. JAISH MISHRA

1
BE(III YR) CHIEF TECHNICAL OFFICER
COMPUTER ENGG. PLANETWIRE INC.
MITS (Laxmangarh) NEW DELHI

ACKNOWLEDGEMENTS

The completion of this training would have been impossible without the help of
several people inside and outside CIT(Centre of Information and
Technology),Delhi and CDAC(Centre for development of advanced
computing),Noida.

I wish to express my deepest gratitude for Mr.Jaish Mishra , for his utmost
concern, guidance and encouragement during my training period at CDAC. I
would like to thank him for his pleasant and insightful interaction with me. I am
extremely grateful for their constant guidance and encouragement and for their
help in gaining access to the resources essential for the successful completion of
this assignment. I would like to thank himfor sharing their valuable knowledge
and resources with me and showed utmost co-operation and understanding.

ANKITA AGARWAL
MITS, Laxmangarh

2
 BRIEF CONTENTS

S. No. DESCRIPTION PAGE NO.

1. EXECUTIVE SUMMARY 1
2. ABOUT ORGANIZATIONS 3
3. INTRODUCTION 7
4. METHODOLOGIES 8
5. ABOUT PROJECT 36
6. REFERENCES 44
Appendix-A: Project Code 45
Appendix-B: Output of the project 116
Appendix-B: Monthly ASCII for DUF 125

3
EXECUTIVE SUMMARY

QWEST DUF SPLITTER PROJECT is designed to keep record of all the data of
the DUF’s, i.e., (DAILY USAGE FILES). These files are used by telecom
companies. They keep the data in numeric form, which shows the time taken by
particular customer.

Main objective of this project is to fetch the data from the DUF’s and then store
them in a separate database. It also informs all the customers about the data on
their email id.

NEED OF THE PROJECT

This project has been designed for the company United Telecom LLC and the
distributed networks which work under this company by the planetwire company.
Initially all the proceedings were very cumbersome and difficult.

Main Telecom server of the Qwest company gives the data in the form of DUF’s
(Daily Usage Files). Then company maintaining the data fetches the files through
FTP (File Transfer Protocol) after that data is maintained in Excel sheets and
with lots of effort its formatting is done. Then this data is passed to distributed-
network to use it to prepare the bills and keep the data of all the customers
separately. This whole procedure to maintain the data used to take place
everyday. This is a very long and difficult process, which requires more
employees also. Therefore there is need of some efficient way to overcome this
problem. Application should be efficient as well as it should save the time and
manpower. Thus this project has been implemented which gives solution to all
the problems.

FUNCTIONING OF QWEST DUF SPLITTER PROJECT

Thus this project has been developed to overcome all these problems.

This project has been implemented to help in keeping data of all the customers
who are availing the services of United Telecom LLC company in New York. This
company uses the telecom server of Qwest company for all the proceedings.
Data from this server comes to United Telecom company in the form of DUF
(Daily Usage Files). These DUF’s contain the data in numeric form as these are

4
output from an electronic device. Therefore the foremost function of project is to
read all DUF’s. There is an ASCII code file in which meaning of the sequence of
numbers from a particular location upto a particular location is defined. According
to data in ASCII file DUF is splitted and final data is sent to the email id of the
customer. File which contains all the details of the duration to which customer
has talked is called one Ani file. Each customer has more than one ANI files. In
this project client is the distributed-network, which works under the United LLC
company. They need data to be properly organized so that proper bill calculation
can be performed. Thus here a proper backend is also used which keeps all the
data of customer in the database. Main steps followed in the project preparation
are:

• Read all the DUF Files.

• Split the file according to the position of data to be matched with the data
in ASCII file.
• Read all the ani files and store them in one array.
• Read all the info of the customer.
• Add it to the file and database.
• Edit the customer file on user’s choice.
• Finally process the file of any customer and send email with attached
formatted call details.
.

TECHNICAL IMPLEMENTATION OF QWEST DUF SPLITTER PROJECT

This project has been implemented using advance java. Java servlets and Java
Server Pages technologies have been used to implement it technically. The main
reason to choose this language is its platform-independency and to use its
features of server side programming. For server side programming tomcat server
has been chosen. For the backend MySQL database has been used. Creation
and compilation of all the programs have been implemented in Eclipse IDE
(Integrated Development Environment). Also HTML, XML files have been used
as well as some java scripting has also done in JSP files. This is the brief
introduction of the whole project. Now let’s explore the things in depth.

5
1.0 ABOUT ORGANIZATIONS

1.1 QWEST

Qwest is committed to providing quality voice, Internet, data and video solutions
to benefit residential, business and government customers across the country.
They are deeply entrenched in their local 14-state western and mid-western
service territory and strive to provide innovative, quality products combined with
superior customer service. In addition, Corporate Sponsorship and Qwest
Foundation opportunities demonstrate their commitment to rewarding the
communities they serve.

QWEST Network Maps

The Qwest® Network puts dependable global communication within our reach.
With Qwest, we receive access to one of the largest, most technically advanced
voice and data networks in the world ensuring we can stay in touch, anytime and
anywhere.

For years, Qwest’s state-of-the-art IP network has been transferring voice, video,
and data across the globe for businesses of all sizes. The Qwest network has the
capacity and advanced capabilities to support today’s mission critical applications
such as Voice over IP (VoIP), as well as bandwidth-intensive business
applications such as Enterprise Resource Planning, Customer Relationship
Management, and other business-to-business functions. These business
applications require a network that is flexible to be tuned to our exact
specifications for reliability, performance, and service.
From a technical perspective, the Qwest OC-192 IP network backbone is
engineered for minimal packet loss and network congestion. MPLS fast re-route
is used between every core router in the network ensuring a quicker recovery
time over synchronous optical network (SONET). Networking Ports are available
from 56 Kilobits per second (Kbps) to 2.4 Gigabits per second (Gbps). While the
Qwest public and private peering arrangements are extensive (86 Gbps per
second utilizing 99 percent private connections), cold potato routing (Qwest
transmits customer traffic on the Qwest network for as long as possible) virtually
eliminates most of the latency issues faced by other service providers. The
Qwest OC-192 IP network is one of the most advanced networks available,

6
offering best–in–class service level agreements and some of the highest
customer access speeds in the industry today.

1.2 PLANETWIRE

Planetwire is a leading Internet Telecom Service Provider. It establishes the

business relationship with major carriers in Los Angles, New York, Miami,
London, Hong Kong and Sydney for the Global Telephony operations. They are
professionally engaged in the field of Internet Telephony solutions, Carrier
Solutions and VOIP based Call Center solutions. They are collaborated with
major US, UK and Australia based companies in the area of IP Telephony and
Call Center related business. Planetwire is having its corporate office in New
York, USA to operate Global Telephony Network. They are having highly skilled
professionals who manage the whole network efficiently. In USA they are having
their network control facility powered with SoftSwitches and having business
development office. In India they are having their support center with well
equipped with expert team.

Planetwire is a VoIP based Telecommunication Company having its International

Calling Card and Telephony Carrier Business in USA. Network Design and
Security Innovations, USA is the partner of Planetwire for the market executions,
financial management and project implementation in USA. It is having major
operations in countries like USA, UK, Australia, Gulf countries, African countries
and Latin countries and getting continuous high growth in telecom business.

They provide the support to start and run Calling Card business anywhere in the
globe. For this they provide all technology support and operational support to get
into high profitability business. They manage the LCR (least call rate) facility to
their calling card business partner.

Main services provided by Planetwire are:

• Graphics designing
• Call centre solutions
• SMS
• Planetwire Messaging solutions
• Telecom
• Designing
• Customized software solutions
• Web designing

Planetwire acts as carrier of carriers in international wholesale telephony traffic.

They provide the direct POP for the specific route required. They are having
ample routes available to give the best deal to the carriers. Same to provide the
best rate to direct route providers globally.

7
Beyond this telecommunication area they have also expanded their business in
the area of SMS (Sort Messaging Service) and High-end Software Development.
Planetwire provides the technical solutions of Bulk SMS which can be send
globally to Hand held devices (mobiles, communicators, palm devices, pagers).
They have well experienced team for Software Development and Web
Designing. They are providing customized applications for Handheld Devices.

1.3 UNITED TELECOM LLC

Founded in 1996, United Telecom has established itself as a leading provider of

Telecommunications services in the New York Tri-State-Area. United Telecom
has attributed its success primarily to is commitment to providing superior
telecommunication expertise and customer service. United Telecom has grown in
sales, staff and business focus since its inception, and has established itself as
an extremely reliable and resourceful company. Goal at United Telecom is to
provide the highest level of customer service to all the clients.

1.3.1 Services

LOCAL SERVICES

Local Service boasts an impressive list of products and services, including

standard services like 911, Directory Listing, Operator Service, and Equal
Access, designed to address all of business's unique needs. Local Service is
truly an integral part of their total integration package, and a key component to
providing all of the price, service, and technological advantages offered through
United Telecom.

LOCAL AND LONG DISTANCE CALLING PACKAGES

One of the services is “Unlimited calls for one monthly price”. It is all local, local
tolls, and long distance calls anywhere in the U.S. Call anywhere, anytime and
talk as long as customer want. Some other popular features include Call Waiting,
Caller ID, and Three-Way Calling.

OUTBOUND LONG DISTANCE

Outbound Long Distance has extensive outbound calling features that connect
customers’ calls to locations across the country and around the world. Call
management tools and virtual network capabilities can help simplify their
outbound long distance communications and increase their productivity.

DEDICATED INTERNET ACCESS

United Telecom's Dedicated Internet products are high-speed solutions for

growing businesses that need high performance and full-time dedicated Internet

8
access. This product suite offers a range of options to suit customers’ access
needs and support all their mission-critical communications. Dedicates services
provide connectivity to a world-class infrastructure, 24 X 8 monitoring and
notification and the peace of mind that comes with outstanding service.

CALLING CARDS

United Telecom's calling card is any easy-to-use, feature-rich calling card

designed to meet the needs of traveling employees by combining a basic feature
set with outstanding reliability and competitive rates. This card provides toll-free
access from Canada and anywhere in the U.S. including Alaska, Hawaii, Puerto
Rico, and the U.S. Virgin Islands to many direct-dial countries worldwide.

Calling cards provide business with voice and data connections from over 140
countries via toll-free access numbers. It has no capacity constraints and
features one consistent dialing sequence.

TOLL FREE

Flexible toll free services enable all customers to conveniently communicate with
their business. Company’s intelligent network lets them route incoming toll-free
calls according to business needs and, in most cases, without any special
equipment or significant expense.

FULL SERVICE T1

United Telecom's Full Service T1 product is a T1 trunk delivering 24 multi-use

channels. With United Telecom's Full Service T1 product, T1 becomes the single
interface for all their telecommunications needs. It is meant for Medium Size
Business customers needing an integrated communications solution and Medium
Size Business customers looking for true one stop shopping.

9
2.0 INTRODUCTION

NEED OF THE PROJECT

File Splitter is designed to keep record of all the data of the DUF’s, i.e., (DAILY
USAGE FILES). These files are used by telecom companies. They keep the data
in numeric form, which shows the time taken by particular customer. To keep the
track for the purpose of billing, initially some different approach was followed.
Main Telecom server gives the data in the form of DUF’s. Then company
maintaining the data fetches the files through FTP (File Transfer Protocol).after
that data is maintained in Excel sheets and with lots of effort it’s formatting is
done. This was a very long and difficult process, which requires more employees
also. Therefore there was need of some efficient way to overcome this problem.
Application should be efficient as well as it should save the time and manpower.
Thus this project has been implemented which gives solution to all the problems.

OBJECTIVE OF THE PROJECT

Main objective of this project is to fetch the data from the DUF’s and then store
them in a separate database. It also informs all the customers about the data on
their email id.

IMPLEMENTATION OF THE PROJECT:

This project has been implemented using advance java technologies. Main
reason of opting java is its platform independency and to utilize its client server
techniques. It uses the basic concepts of core java, but its main implementation
has been done through the servlets and JSP (java Server pages) and servlet
runs in the tomcat environment. It also uses database MySql as its backend.

10
3.0 METHODOLOGIES

The main tools which have been used in this project are as follows:

 Core Java
 J2EE
 JDBC(Java Database Connectivity)
 Servlets
 JSP(Java Server Pages)
 Jakarta-Tomcat environment
 MySql
 MS Front Page
 Javascript

3.1 CORE JAVA

What Is Java?

Java is an object-oriented programming language developed by Sun

Microsystems, a company best known for its high-end UNIX workstations.
Modeled after C++, the Java language was designed to be small, simple, and
portable across platforms and operating systems, both at the source and at the
binary level (more about this later). Java is often mentioned in the same breath
as Hot Java, a World Wide Web browser from Sun like Netscape or Mosaic (see
Figure 1.1). What makes Hot Java different from most other browsers is that, in
addition to all its basic Web features, it can also download and play applets on
the reader’s system. Applets appear in a Web page much in the same way as
images do, but unlike images, applets are dynamic and interactive. Applets can
be used to create animations, figures, or areas that can respond to input from the
reader, games, or other interactive effects on the same Web pages among the
text and graphics. Although Hot Java was the first World Wide Web browser to
be able to play Java applets, Java support is rapidly becoming available in other
browsers. Netscape 2.0 provides support for Java applets, and other browser
developers have also announced support for Java in forthcoming products.

Java’s Past, Present, and Future

11
The Java language was developed at Sun Microsystems in 1991 as part of a
research project to develop software for consumer electronics devices—
television sets, VCRs, toasters, and the other sorts of machines we can buy at
any department store. Java’s goals at that time were to be small, fast, efficient,
and easily portable to a wide range of hardware devices. It is those same goals
that made Java an ideal language for distributing executable programs via the
World Wide Web, and also a general-purpose programming language for
developing programs that are easily usable and portable across different
platforms.

Java Is Platform-Independent

Platform independence is one of the most significant advantages that Java has
over other programming languages, particularly for systems that need to work on
many different platforms. Java is platform-independent at both the source and
the binary level. Platform-
independence is a program’s
capability of moving easily
from one computer system to
another.

Java binary files are actually

in a form called bytecodes.
Bytecodes are a set of
instructions that looks a lot
like some machine codes, but
that is not specific to any one
processor.

Figure 3.1 Traditional compiled programs.

Normally, when we compile a program written in C or in most other languages,

the compiler translates our program into machine codes or processor
instructions. Those
instructions are specific to
the processor our
computer is running—so,
for example, if we compile
our code on a Pentium
system, the resulting
program will run only on
other Pentium systems. If
we want to use the same
program on another
system, we have to go

12
back to our original source, get a compiler for that system, and recompile our
code.

Figure 3.2 Java programs.

Figure 3.1 shows the result of this system: multiple executable programs for
multiple systems. Things are different when we write code in Java. The Java
development environment has two parts: a Java compiler and a Java interpreter.
The Java compiler takes our Java program and instead of generating machine
codes from our source files, it generates bytecodes.

To run a Java program, we run a program called a bytecode interpreter, which in

turn executes our Java program (see Figure 3.2). We can either run the
interpreter by itself, or—for applets—there is a bytecode interpreter built into
HotJava and other Java-capable browsers that runs the applet for us.

Java Is Object-Oriented

To some, object-oriented programming (OOP) technique is merely a way of

organizing programs, and it can be accomplished using any language. Working
with a real object-oriented language and programming environment, however,
enables we to take full advantage of object-oriented methodology and its
capabilities of creating flexible, modular programs and reusing code. Many of
Java’s object-oriented concepts are inherited from C++, the language on which it
is based, but it borrows many concepts from other object-oriented languages as
well. Like most object-oriented programming languages, Java includes a set of
class libraries that provide basic data types, system input and output capabilities,
and other utility functions. These basic classes are part of the Java development
kit, which also has classes to support networking, common Internet protocols,
and user interface toolkit functions. Because these class libraries are written in
Java, they are portable across platforms as all Java applications are.

3.2 J2EE

3.2.1 Overview

Today, more and more developers want to write distributed transactional

applications for the enterprise and thereby leverage the speed, security, and
reliability of server-side technology. If we are already working in this area, we
know that in the fast-moving and demanding world of e-commerce and
information technology, enterprise applications must be designed, built, and
produced for less money, with greater speed, and with fewer resources than ever
before.

13
To reduce costs and fast-track application design and development, the Java 2
Platform, Enterprise Edition (J2EE ) provides a component-based approach to
the design, development, assembly, and deployment of enterprise applications.
The J2EE platform offers a multitiered distributed application model, reusable
components, a unified security model, flexible transaction control, and web
services support through integrated data interchange on Extensible Markup
Language (XML)-based open standards and protocols.

Not only can we deliver innovative business solutions to market faster than ever,
but also our platform-independent J2EE component-based solutions are not tied
to the products and application programming interfaces (APIs) of any one
vendor. Vendors and customers enjoy the freedom to choose the products and
components that best meet their business and technological requirements.

3.2.2 J2EE Containers

Normally, thin-client multitiered applications are hard to write because they

involve many lines of intricate code to handle transaction and state management,
multithreading, resource pooling, and other complex low-level details. The
component-based and platform-independent J2EE architecture makes J2EE
applications easy to write because business logic is organized into reusable
components. In addition, the J2EE server provides underlying services in the
form of a container for every component type. Because we do not have to
develop these services ourself, we are free to concentrate on solving the
business problem at hand.

Container Services

Containers are the interface between a component and the low-level platform-
specific functionality that supports the component. Before a web component,
enterprise bean, or application client component can be executed, it must be
assembled into a J2EE module and deployed into its container.

The assembly process involves specifying container settings for each component
in the J2EE application and for the J2EE application itself. Container settings
customize the underlying support provided by the J2EE server, including services
such as security, transaction management, Java Naming and Directory Interface
(JNDI) lookups, and remote connectivity. Here are some of the highlights:

 The J2EE security model lets us configure a web component or enterprise

bean so that system resources are accessed only by authorized users.

 The J2EE transaction model lets us specify relationships among methods

that make up a single transaction so that all methods in one transaction
are treated as a single unit.

14
  JNDI lookup services provide a unified interface to multiple naming and
directory services in the enterprise so that application components can
access naming and directory services.

 The J2EE remote connectivity model manages low-level communications

between clients and enterprise beans. After an enterprise bean is created,
a client invokes methods on it as if it were in the same virtual machine.
Because the J2EE architecture provides configurable services, application
components within the same J2EE application can behave differently
based on where they are deployed. For example, an enterprise bean can
have security settings that allow it a certain level of access to database
data in one production environment and another level of database access
in another production environment. The container also manages non-
configurable services such as enterprise bean and servlet life cycles,
database connection resource pooling, data persistence, and access to
the J2EE platform APIs section. Although data persistence is a non-
configurable service, the J2EE architecture lets us override container-
managed persistence by including the appropriate code in our enterprise
bean implementation when we want more control than the default
container-managed persistence provides. For example, we might use
bean-managed persistence to implement our own finder (search) methods
or to create a customized database cache.

Container Types

The deployment process installs J2EE application components in the J2EE

containers illustrated in Figure 1-5. The different types of container types are as
follows:

Figure 3.3 J2EE Server and Containers

J2EE server

15
J2EE server is the runtime portion of a J2EE product. A J2EE server provides
EJB and web containers.

Enterprise JavaBeans (EJB) container

EJB container manages the execution of enterprise beans for J2EE applications.
Enterprise beans and their container run on the J2EE server.

Web container

Web container manages the execution of JSP page and servlet components for
J2EE applications. Web components and their container run on the J2EE server.

Application client container

Applicant client container manages the execution of application client

components. Application clients and their container run on the client.

Applet container

Applet container manages the execution of applets. It consists of a web browser

and Java Plug-in running on the client together.

3.2.3 J2EE 1.4 APIs

Figure 3.4 illustrates the availability of the J2EE 1.4 platform APIs in each J2EE
container type. The following sections give a brief summary of the technologies
required by the J2EE platform and the J2SE enterprise APIs that would be used
in J2EE applications.

16
 Figure 3.4 J2EE Platform APIs
Enterprise JavaBeans Technology

An Enterprise JavaBeans (EJB ) component, or enterprise bean, is a body of

code having fields and methods to implement modules of business logic. We can
think of an enterprise bean as a building block that can be used alone or with
other enterprise beans to execute business logic on the J2EE server.

As mentioned earlier, there are three kinds of enterprise beans: session beans,
entity beans, and message-driven beans. Enterprise beans often interact with
databases. One of the benefits of entity beans is that we do not have to write any
SQL code or use the JDBC API directly to perform database access
operations; the EJB container handles this for us. However, if we override the
default container-managed persistence for any reason, we will need to use the
JDBC API. Also, if we choose to have a session bean access the database, we
must use the JDBC API.

Java Servlet Technology

Java servlet technology lets us define HTTP-specific servlet classes. A servlet

class extends the capabilities of servers that host applications that are accessed
by way of a request-response programming model. Although servlets can
respond to any type of request, they are commonly used to extend the
applications hosted by web servers.

JavaServer Pages Technology

17
JavaServer Pages (JSP ) technology lets us put snippets of servlet code
directly into a text-based document. A JSP page is a text-based document that
contains two types of text: static data (which can be expressed in any text-based
format such as HTML, WML, and XML) and JSP elements, which determine how
the page constructs dynamic content.

Java Message Service API

The Java Message Service (JMS) API is a messaging standard that allows J2EE
application components to create, send, receive, and read messages. It enables
distributed communication that is loosely coupled, reliable, and asynchronous.

Java Transaction API

The Java Transaction API (JTA) provides a standard interface for demarcating
transactions. The J2EE architecture provides a default auto commit to handle
transaction commits and rollbacks. An auto commit means that any other
applications that are viewing data will see the updated data after each database
read or write operation. However, if our application performs two separate
database access operations that depend on each other, we will want to use the
JTA API to demarcate where the entire transaction, including both operations,
begins, rolls back, and commits.

JavaMail API

J2EE applications use the JavaMail API to send email notifications. The
JavaMail API has two parts: an application-level interface used by the application
components to send mail, and a service provider interface. The J2EE platform
includes JavaMail with a service provider that allows application components to
send Internet mail.

JavaBeans Activation Framework

The JavaBeans Activation Framework (JAF) is included because JavaMail uses

it. JAF provides standard services to determine the type of an arbitrary piece of
data, encapsulate access to it, discover the operations available on it, and create
the appropriate JavaBeans component to perform those operations.

Java API for XML Processing

The Java API for XML Processing (JAXP) supports the processing of XML
documents using Document Object Model (DOM), Simple API for XML (SAX),
and Extensible Stylesheet Language Transformations (XSLT). JAXP enables
applications to parse and transform XML documents independent of a particular
XML processing implementation.

18
JAXP also provides namespace support, which lets us work with schemas that
might otherwise have naming conflicts. Designed to be flexible, JAXP lets us use
any XML-compliant parser or XSL processor from within our application and
supports the W3C schema.

Java API for XML-Based RPC

The Java API for XML-based RPC (JAX-RPC) uses the SOAP standard and
HTTP, so client programs can make XML-based remote procedure calls (RPCs)
over the Internet. JAX-RPC also supports WSDL, so we can import and export
WSDL documents. With JAX-RPC and a WSDL, we can easily interoperate with
clients and services running on Java-based or non-Java-based platforms such as
.NET. For example, based on the WSDL document, a Visual Basic .NET client
can be configured to use a web service implemented in Java technology, or a
web service can be configured to recognize a Visual Basic .NET client.

JAX-RPC relies on the HTTP transport protocol. Taking that a step further, JAX-
RPC lets we create service applications that combine HTTP with a Java
technology version of the Secure Socket Layer (SSL) and Transport Layer
Security (TLS) protocols to establish basic or mutual authentication. SSL and
TLS ensure message integrity by providing data encryption with client and server
authentication capabilities.

Authentication is a measured way to verify whether a party is eligible and able to

access certain information as a way to protect against the fraudulent use of a
system or the fraudulent transmission of information. Information transported
across the Internet is especially vulnerable to being intercepted and misused, so
it's very important to configure a JAX-RPC web service to protect data in transit.

SOAP with Attachments API for Java

The SOAP with Attachments API for Java (SAAJ) is a low-level API on which
JAX-RPC depends. SAAJ enables the production and consumption of messages
that conform to the SOAP 1.1 specification and SOAP with Attachments note.
Most developers do not use the SAAJ API, instead using the higher-level JAX-
RPC API.

Java API for XML Registries

The Java API for XML Registries (JAXR) lets we access business and general-
purpose registries over the web. JAXR supports the ebXML Registry and
Repository standards and the emerging UDDI specifications. By using JAXR,
developers can learn a single API and gain access to both of these important
registry technologies.

19
Additionally, businesses can submit material to be shared and search for material
that others have submitted. Standards groups have developed schemas for
particular kinds of XML documents; two businesses might, for example, agree to
use the schema for their industry's standard purchase order form. Because the
schema is stored in a standard business registry, both parties can use JAXR to
access it.

J2EE Connector Architecture

The J2EE Connector architecture is used by J2EE tools vendors and system
integrators to create resource adapters that support access to enterprise
information systems that can be plugged in to any J2EE product. A resource
adapter is a software component that allows J2EE application components to
access and interact with the underlying resource manager of the EIS. Because a
resource adapter is specific to its resource manager, typically there is a different
resource adapter for each type of database or enterprise information system.

The J2EE Connector architecture also provides a performance-oriented, secure,

scalable, and message-based transactional integration of J2EE-based web
services with existing EISs that can be either synchronous or asynchronous.
Existing applications and EISs integrated through the J2EE Connector
architecture into the J2EE platform can be exposed as XML-based web services
by using JAX-RPC and J2EE component models. Thus JAX-RPC and the J2EE
Connector architecture are complementary technologies for enterprise
application integration (EAI) and end-to-end business integration.

JDBC API

The JDBC API lets we invoke SQL commands from Java programming language
methods. We use the JDBC API in an enterprise bean when we override the
default container-managed persistence or have a session bean access the
database. With container-managed persistence, database access operations are
handled by the container, and our enterprise bean implementation contains no
JDBC code or SQL commands. We can also use the JDBC API from a servlet or
a JSP page to access the database directly without going through an enterprise
bean.

The JDBC API has two parts: an application-level interface used by the
application components to access a database, and a service provider interface to
attach a JDBC driver to the J2EE platform.

Java Naming and Directory Interface

The Java Naming and Directory Interface (JNDI) provide naming and directory
functionality. It provides applications with methods for performing standard
directory operations, such as associating attributes with objects and searching for

20
objects using their attributes. Using JNDI, a J2EE application can store and
retrieve any type of named Java object.

J2EE naming services provide application clients, enterprise beans, and web
components with access to a JNDI naming environment. A naming environment
allows a component to be customized without the need to access or change the
component's source code. A container implements the component's environment
and provides it to the component as a JNDI naming context.

A J2EE component locates its environment naming context using JNDI

interfaces. A component creates a javax.naming.InitialContext object and looks up the
environment naming context in InitialContext under the name java:comp/env. A
component's naming environment is stored directly in the environment naming
context or in any of its direct or indirect subcontexts.

A J2EE component can access named system-provided and user-defined

objects. The names of system-provided objects, such as JTA UserTransaction
objects, are stored in the environment naming context, java:comp/env. The J2EE
platform allows a component to name user-defined objects, such as enterprise
beans, environment entries, JDBC DataSource objects, and message connections.
An object should be named within a subcontext of the naming environment
according to the type of the object. For example, enterprise beans are named
within the subcontext java:comp/env/ejb, and JDBC DataSource references in the
subcontext java:comp/env/jdbc.

Because JNDI is independent of any specific implementation, applications

can use JNDI to access multiple naming and directory services, including
existing naming and directory services such as LDAP, NDS, DNS, and NIS.
This allows J2EE applications to coexist with legacy applications and
systems.

Java Authentication and Authorization Service

The Java Authentication and Authorization Service (JAAS) provide a way for a
J2EE application to authenticate and authorize a specific user or group of users
to run it.

JAAS is a Java programming language version of the standard Pluggable

Authentication Module (PAM) framework, which extends the Java 2 Platform
security architecture to support user-based authorization.

Simplified Systems Integration

The J2EE platform is a platform-independent, full systems integration solution

that creates an open marketplace in which every vendor can sell to every
customer. Such a marketplace encourages vendors to compete, not by trying to

21
lock customers into their technologies but instead by trying to outdo each other in
providing products and services that benefit customers, such as better
performance, better tools, or better customer support.

The J2EE APIs enable systems and applications integration through the
following:

 Unified application model across tiers with enterprise beans

 Simplified request-and-response mechanism with JSP pages and servlets
 Reliable security model with JAAS
 XML-based data interchange integration with JAXP, SAAJ, and JAX-RPC
 Simplified interoperability with the J2EE Connector architecture
 Easy database connectivity with the JDBC API
 Enterprise application integration with message-
driven beans and JMS, JTA, and JNDI

3.3 JDBC (JAVA DATABASE CONNECTIVITY)

3.3.1 Introduction to JDBC

SQL is a language used to create, manipulate, examine and manage relational

databases. ODBC, a C-based interface to
SQL-based database engines, provides a consistent interface for communicating
with a database and for accessing database metadata.

JDBC is Java version of ODBC, and is itself a growing standard. Database

vendors are already busy creating bridges from the JDBC API to their particular
systems. The JDBC-ODBC bridge driver is one of the four types of drivers
available to support JDBC connectivity.

Some Industry Standard databases are:

 SQL Server
 DB2
 My SQL
 MS Access
 Sybase

3.3.2 JDBC Driver Types

22
Type 1: JDBC-ODBC Bridge plus ODBC Driver

This driver is provided by JDK. Its program is written completely in C language.

Java
Application
ODBC
JDBC-
ODBC
Bridge

Database working as
middleware

Type 2: Native API Partly Java Driver

Java
Native Application
Database
Library
JDBC-
Driver

Database working as
middleware

The Native to API driver converts JDBC commands to DBMS. It converts specific
native calls. Here no driver is written by Java unlike ODBC where JDBC-ODBC
driver written by Java. Here partial difference is that driver is written by specific
company only. This is a native application.

Type 3: JDBC-Net Pure Java Driver

JDBC-Net is three tier solutions. It translates JDBC calls into database

independent network that sent to a middleware server. Unlike type2 which is OS
dependent this is OS independent and a Net application.

23
 Java
Middleware
Application

JDBC driver JDBC-

Driver

Database working as
middleware

24
Type 4: Native-protocol Pure Java Driver

This is a pure java driver that communicates directly to database. Like oracle has
made its own driver.

Java
Application

JDBC-
Driver

Database

3.3.3 Create database

To create the database, we can feed SQL statements to an ODBC data source
via the JDBC-ODBC bridge. First, we will have to create an ODBC data source.
We have many choices – we could, for example, connect an Access, SQL
Server, Oracle or Sybase database.

To enter the data into the TestDB database, create a Java application that
follows these steps:

1. Load the JDBC-ODBC bridge: It tells the JDBC classes how to talk to a data
source. For the class JDBC-ODBC driver

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

2. Connect to a data source: A URL is used to connect to a particular JDBC data

source. Using the Driver Manager Class, we request a connection to a URL and
the Driver Manager selects the appropriate driver.

Connectioncon=DriverManager.getConnection((URL,username,password);

where the username and password are empty strings, in this case because text
files acting as ODBC data source cannot have such attributes.

25
3. Send SQL statements to create the table: Ask the connection object for a
statement object.

Statement stmt =con.createStatement();

To create the table:

Stmt.execute(“create table Test(programmer varchar(10),computer_no int));

4. To insert the values:

Insert into test values(“Ankita”,”10”);

Metadata: We can access the information about the database as a hole, or about
a particular query ResultSet using Stored Procedures.

JDBC Exception Types:

SQL Exceptions

The SQLException is the basis of all JDBC Exceptions. It consists of all three
pieces of information a, String message, another String containing the XOPEN
SQL state, and adiver/source specific int for an additional error code.
In addition, multiple SQL Exception instances can be chained together.

SQL Warnings

The SQLWarning class is similar to SQLException, however it is considered a

non-critical error and is not thrown .It is upto the programmer to poll for
SQLWarning messages through the getWarnings methods of Connection,
ResultSet, and Statement.

3.4 SERVLETS

A servlet is a Java programming language class used to extend the capabilities

of servers that host applications accessed via a request-response programming
model. Although servlets can respond to any type of request, they are commonly
used to extend the applications hosted by Web servers. For such applications,
Java Servlet technology defines HTTP-specific servlet classes.

The javax.servlet and javax.servlet.http packages provide interfaces and classes

for writing servlets. All servlets must implement the Servlet interface, which
defines life-cycle methods.

26
When implementing a generic service, we can use or extend the GenericServlet
class provided with the Java Servlet API. The HttpServlet class provides
methods, such as doGet and doPost, for handling HTTP-specific services.

3.4.1 Servlet Life Cycle

The life cycle of a servlet is controlled by the container in which the servlet has
been deployed. When a request is mapped to a servlet, the container performs
the following steps.

 If an instance of the servlet does not exist, the Web container

 Loads the servlet class.
 Creates an instance of the servlet class.
 Initializes the servlet instance by calling the init method.
 Invokes the service method, passing a request and response object.

If the container needs to remove the servlet, it finalizes the servlet by calling the
servlet's destroy method.

3.4.2 What is the Advantage of Servlets Over "Traditional" CGI?

Servlets are Java technology's answer to CGI programming. They are programs
that run on a Web server and build Web pages. Building Web pages on the fly is
useful (and commonly done) for a number of reasons:

 The Web page is based on data submitted by the user. For example the
results pages from search engines are generated this way, and programs
that process orders for e-commerce sites do this as well.
 The data changes frequently. For example, a weather-report or news
headlines page might build the page dynamically, perhaps returning a
previously built page if it is still up to date.
 The Web page uses information from corporate databases or other such
sources. For example, we would use this for making a Web page at an on-
line store that lists current prices and number of items in stock.

Java servlets are more efficient, easier to use, more powerful, more portable, and
cheaper than traditional CGI and than many alternative CGI-like technologies.

 Efficient. With traditional CGI, a new process is started for each HTTP
request. If the CGI program does a relatively fast operation, the overhead
of starting the process can dominate the execution time. With servlets, the
Java Virtual Machine stays up, and each request is handled by a
lightweight Java thread, not a heavyweight operating system process.
Similarly, in traditional CGI, if there are N simultaneous request to the
same CGI program, then the code for the CGI program is loaded into
memory N times. With servlets, however, there are N threads but only a

27
 single copy of the servlet class. Servlets also have more alternatives than
do regular CGI programs for optimizations such as caching previous
computations, keeping database connections open, and the like.
 Convenient. Besides the convenience of being able to use a familiar
language, servlets have an extensive infrastructure for automatically
parsing and decoding HTML form data, reading and setting HTTP
headers, handling cookies, tracking sessions, and many other such
utilities.
 Powerful. Java servlets let we easily do several things that are difficult or
impossible with regular CGI. For one thing, servlets can talk directly to the
Web server (regular CGI programs can't). This simplifies operations that
need to look up images and other data stored in standard places. Servlets
can also share data among each other, making useful things like database
connection pools easy to implement. They can also maintain information
from request to request, simplifying things like session tracking and
caching of previous computations.
 Portable. Servlets are written in Java and follow a well-standardized API.
Consequently, servlets written for, say I-Planet Enterprise Server can run
virtually unchanged on Apache, Microsoft IIS, or WebStar. Servlets are
supported directly or via a plugin on almost every major Web server.
 Inexpensive. There are a number of free or very inexpensive Web servers
available that are good for "personal" use or low-volume Web sites.
However, with the major exception of Apache, which is free, most
commercial-quality Web servers are relatively expensive. Nevertheless,
once we have a Web server, no matter the cost of that server, adding
servlet support to it (if it doesn't come preconfigured to support servlets) is
generally free or cheap.

3.5 JSP (Java Server Pages)

Java Server Pages (JSP) is a technology that lets we mix regular, static HTML
with dynamically-generated HTML. Many Web pages that are built by CGI
programs are mostly static, with the dynamic part limited to a few small locations.
But most CGI variations, including servlets, make we generate the entire page
via our program, even though most of it is always the same. JSP lets we
create the two parts separately. Here's an example:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML>
<HEAD><TITLE>Welcome to Our Store</TITLE></HEAD>
<BODY>
<H1>Welcome to Our Store</H1>
<SMALL>Welcome,
<!-- User name is "New User" for first-time visitors -->
<% out.println(Utils.getUserNameFromCookie(request)); %>

28
To access our account settings, click
<A HREF="Account-Settings.html">here.</A></SMALL>
<P>
Regular HTML for all the rest of the on-line store's Web page.
</BODY></HTML>

3.5.1 What are the Advantages of JSP?

 vs. Active Server Pages (ASP). ASP is a similar technology from

Microsoft. The advantages of JSP are twofold. First, the dynamic part is
written in Java, not Visual Basic or other MS-specific language, so it is
more powerful and easier to use. Second, it is portable to other operating
systems and non-Microsoft Web servers.
 vs. Pure Servlets. JSP doesn't give us anything that we couldn't in
principle do with a servlet. But it is more convenient to write (and to
modify!) regular HTML than to have a zillion println statements that
generate the HTML. Plus, by separating the look from the content we can
put different people on different tasks: our Web page design experts can
build the HTML, leaving places for our servlet programmers to insert the
dynamic content.
 vs. Server-Side Includes (SSI). SSI is a widely-supported technology for
including externally-defined pieces into a static Web page. JSP is better
because it lets we use servlets instead of a separate program to generate
that dynamic part. Besides, SSI is really only intended for simple
inclusions, not for "real" programs that use form data, make database
connections, and the like.
 vs. JavaScript. JavaScript can generate HTML dynamically on the client.
This is a useful capability, but only handles situations where the dynamic
information is based on the client's environment. With the exception of
cookies, HTTP and form submission data is not available to JavaScript.
And, since it runs on the client, JavaScript can't access server-side
resources like databases, catalogs, pricing information, and the like.
 vs. Static HTML. Regular HTML, of course, cannot contain dynamic
information. JSP is so easy and convenient that it is quite feasible to
augment HTML pages that only benefit marginally by the insertion of small
amounts of dynamic data. Previously, the cost of using dynamic data
would preclude its use in all but the most valuable instances.

3.6 JAKARTA TOMCAT ENVIRONMENT

3.6.1 Apache Tomcat

Apache Tomcat is the servlet container that is used in the official Reference
Implementation for the Java Servlet and JavaServer Pages technologies. The
Java Servlet and JavaServer Pages specifications are developed by Sun under
the Java Community Process.

29
Apache Tomcat is developed in an open and participatory environment and
released under the Apache Software License. Apache Tomcat is intended to be a
collaboration of the best-of-breed developers from around the world.

3.6.2 The Tomcat 4 Servlet/JSP Container

Directories and Files

$JAVA_HOME represents the root of our Tomcat installation. When we say,

"This information can be found in our $JAVA_HOME/README.txt file" we mean
to look at the README.txt file at the root of our Tomcat install.

For the complete description of the Tomcat distribution, each folder can be found
in the README.txt file, residing in the root directory of our Tomcat installation.

 /bin - Startup, shutdown, and other scripts. The *.sh files (for Unix
systems) are functional duplicates of the *.bat files (for Windows systems).
Since the Win32 command-line lacks certain functionality, there are some
additional files in here.
 /conf - Configuration files and related DTDs. The most important file in
here is server.xml. It is the main configuration file for the container.
 /logs - Log files are here by default.
 /webapps - This is where our webapps go.

Configuring Tomcat

This section will acquaint we with the basic information used during the
configuration of the container.

All of the information in the configuration files is read at startup, meaning that any
change to the files necessitates a restart of the container.

Basic architecture of TOMCAT

Tomcat

bin

conf

Lib

Logs

30 Webapp
Architecture of programmer’s application

Webapps
3.7 MYSQL
Yourapp.
MySQL is an SQL based relational database management system (DBMS) that
runs under a broad array of operating systems. MySQL is frequently used by
PHP and Perl scripts. The SQL commands apply to images
MySQL operates under all
Examples
operating systems. Only the installation instructions are Windows specific. The
focus is on Windows XP Professional and Windows 2000JSP Professional machines.
3.7.1 The Main Features of MySQL
WEB_INF
The following list describes some of the important characteristics of the MySQL
Database Software.

 Internals and Portability Classes

 Written in C and C++.
 Tested with a broad range of different compilers. Lib
 Works on many different platforms.
 Uses GNU Automake, Autoconf, and Libtool for portability.
Web.xml
 APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are
available.
 Fully multi-threaded using kernel threads. It can easily use multiple CPUs
if they are available.
 Provides transactional and non-transactional storage engines.
 Uses very fast B-tree disk tables (MyISAM) with index compression.
 Relatively easy to add another storage engine. This is useful if we want to
add an SQL interface to an in-house database.
 A very fast thread-based memory allocation system.
 Very fast joins using an optimized one-sweep multi-join.
 In-memory hash tables, which are used as temporary tables.

SQL functions are implemented using a highly optimized class library and should
be as fast as possible. Usually there is no memory allocation at all after query
initialization. The MySQL code is tested with Purify (a commercial memory
leakage detector) as well as with Valgrind, a GPL tool.

The server is available as a separate program for use in a client/server

networked environment. It is also available as a library that can be embedded
(linked) into standalone applications. Such applications can be used in isolation
or in environments where no network is available.

31
Column Types

 Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long,

FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME,
DATETIME, TIMESTAMP, YEAR, SET, ENUM, and OpenGIS spatial types.
 Fixed-length and variable-length records.
Statements and Functions

 Full operator and function support in the SELECT and WHERE clauses of
queries. For example:
mysql> SELECT CONCAT(first_name, ' ', last_name)
FROM citizen
WHERE income/dependents > 10000 AND age > 30;
 Full support for SQL GROUP BY and ORDER BY clauses. Support for
group functions (COUNT(), COUNT(DISTINCT ...), AVG(), STD(),
SUM(), MAX(), MIN(), and GROUP_CONCAT()).
 Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with both
standard SQL and ODBC syntax.
 Support for aliases on tables and columns as required by standard SQL.
 DELETE, INSERT, REPLACE, and UPDATE return the number of rows that
were changed (affected). It is possible to return the number of rows
matched instead by setting a flag when connecting to the server.
 The MySQL-specific SHOW command can be used to retrieve information
about databases, tables, and indexes. The EXPLAIN command can be
used to determine how the optimizer resolves a query.
 Function names do not clash with table or column names. For example,
ABS is a valid column name. The only restriction is that for a function call,
no spaces are allowed between the function name and the `(' that
follows it.
 We can mix tables from different databases in the same query (as of
MySQL 3.22).

Security

A privilege and password system that is very flexible and secure, and that allows
host-based verification. Passwords are secure because all password traffic is
encrypted when we connect to a server.

Scalability and Limits

 Handles large databases. We use MySQL Server with databases that

contain 50 million records. We also know of users who use MySQL Server
with 60,000 tables and about 5,000,000,000 rows.

32
  Up to 64 indexes per table are allowed (32 before MySQL 4.1.2). Each
index may consist of 1 to 16 columns or parts of columns. The maximum
index width is 1000 bytes (500 before MySQL 4.1.2). An index may use a
prefix of a column for CHAR, VARCHAR, BLOB, or TEXT column types.

Connectivity

 Clients can connect to the MySQL server using TCP/IP sockets on any
platform. On Windows systems in the NT family (NT, 2000, or XP), clients
can connect using named pipes. On UNIX systems, clients can connect
using UNIX domain socket files.
 The Connector/ODBC (MyODBC) interface provides MySQL support for
client programs that use ODBC (Open Database Connectivity)
connections. For example, we can use MS Access to connect to our
MySQL server. Clients can be run on Windows or UNIX. MyODBC source
is available. All ODBC 2.5 functions are supported, as are many others.
 The Connector/J interface provides MySQL support for Java client
programs that use JDBC connections. Clients can be run on Windows or
Unix. Connector/J source is available.

Localization

 The server can provide error messages to clients in many languages.

 Full support for several different character sets, including latin1 (ISO-
8859-1), german, big5, ujis, and more. For example, the Scandinavian
characters `@^a', `@"a' and `@"o' are allowed in table and column
names. Unicode support is available as of MySQL 4.1.
 All data is saved in the chosen character set. All comparisons for normal
string columns are case-insensitive.
 Sorting is done according to the chosen character set (using Swedish
collation by default). It is possible to change this when the MySQL server
is started. To see an example of very advanced sorting, look at the Czech
sorting code. MySQL Server supports many different character sets that
can be specified at compile time and runtime.

Clients and Tools

 The MySQL server has built-in support for SQL statements to check,
optimize, and repair tables. These statements are available from the
command line through the @command{mysqlcheck} client. MySQL also
includes @command{myisamchk}, a very fast command-line utility for
performing these operations on MyISAM tables.
 All MySQL programs can be invoked with the --help or -? options to
obtain online assistance

33
3.7.2 Main Commands in MYSQL

A) CREATE Command

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]
[table_options] [select_statement]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(] LIKE old_tbl_name [)];

create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]
| CHECK (expr)

34
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_definition]

type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT
| TEXT
| MEDIUMTEXT
| LONGTEXT
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type

index_col_name:
col_name [(length)] [ASC | DESC]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

35
table_options: table_option [table_option] ...

table_option:
{ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|
MYISAM}
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| CHECKSUM = {0 | 1}
| COMMENT = 'string'
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| DELAY_KEY_WRITE = {0 | 1}
| ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }
| RAID_TYPE = { 1 | STRIPED | RAID0 }
RAID_CHUNKS = value
RAID_CHUNKSIZE = value
| UNION = (tbl_name[,tbl_name]...)
| INSERT_METHOD = { NO | FIRST | LAST }
| DATA DIRECTORY = 'absolute path to directory'
| INDEX DIRECTORY = 'absolute path to directory'
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

B) SELECT Command

MySQL Server doesn't support the Sybase SQL extension: SELECT ... INTO
TABLE .... Instead, MySQL Server supports the standard SQL syntax INSERT
INTO ... SELECT ..., which is basically the same thing.

INSERT INTO tbl_temp2 (fld_id)

SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

C) INSERT Command

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

36
 SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]
SELECT ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES and
INSERT ... SET forms of the statement insert rows based on explicitly specified
values. The INSERT ... SELECT form inserts rows selected from another table or
tables. The INSERT ... VALUES form with multiple value lists is supported in
MySQL 3.22.5 or later. The INSERT ... SET syntax is supported in MySQL
3.22.10 or later

tbl_name is the table into which rows should be inserted. The columns for which
the statement provides values can be specified as follows:

• The column name list or the SET clause indicates the columns explicitly.
• If we do not specify the column list for INSERT ... VALUES or INSERT ...
SELECT, values for every column in the table must be provided in the
VALUES() list or by the SELECT. If we don't know the order of the
columns in the table, use DESCRIBE tbl_name to find out.

D) UPDATE Command

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]

SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

The UPDATE statement updates columns in existing table rows with new values.
The SET clause indicates which columns to modify and the values they should
be given. The WHERE clause, if given, specifies which rows should be updated.
Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows

37
will be updated in the order that is specified. The LIMIT clause places a limit on
the number of rows that can be updated.

The UPDATE statement supports the following modifiers:

• If we specify the LOW_PRIORITY keyword, execution of the UPDATE is

delayed until no other clients are reading from the table.
• If we specify the IGNORE keyword, the update statement will not abort
even if duplicate-key errors occur during the update. Rows for which
conflicts occur are not updated.

3.8 FRONT PAGE:

• Microsoft FrontPage is a rapid web pages development tool to help users

create great-looking websites exactly the way they want.
• Users can extend the functionality of FrontPage by downloading add-ins
created by 3rd party developers, including web templates, e-commerce
shopping carts, multimedia, etc.
• FrontPage makes site management easy. It automatically fixes hyperlinks
when files are renamed or moved.
• FrontPage also makes adding forms and databases into sites easier than
ever.
• FrontPage was designed to function more like Microsoft Office so that
users can get up and running with FrontPage more quickly than ever.

3.9 JAVASCRIPT

JavaScript is used in millions of Web pages to improve the design, validate

forms, and much more. JavaScript was developed by Netscape and is the most
popular scripting language on the internet. JavaScript works in all major browsers
that are version 3.0 or higher.

3.9.1 What is JavaScript?

• JavaScript was designed to add interactivity to HTML pages
• JavaScript is a scripting language - a scripting language is a lightweight
programming language
• A JavaScript is lines of executable computer code
• A JavaScript is usually embedded directly in HTML pages
• JavaScript is an interpreted language (means that scripts execute without
preliminary compilation)
• Everyone can use JavaScript without purchasing a license
• JavaScript is supported by all major browsers, like Netscape and Internet

38
 Explorer

3.9.2 What can a JavaScript Do?

• JavaScript gives HTML designers a programming tool - HTML authors
are normally not programmers, but JavaScript is a scripting language with
a very simple syntax! Almost anyone can put small "snippets" of code into
their HTML pages
• JavaScript can put dynamic text into an HTML page - A JavaScript
statement like this: document.write("<h1>" + name + "</h1>") can write a
variable text into an HTML page
• JavaScript can react to events - A JavaScript can be set to execute
when something happens, like when a page has finished loading or when
a user clicks on an HTML element
• JavaScript can read and write HTML elements - A JavaScript
can read and change the content of an HTML element
• JavaScript can be used to validate data - A JavaScript can be used to
validate form data before it is submitted to a server, this will save the
server from extra processing

example:

<html>
<body>
<script type="text/javascript">
document.write("Hello World!")
</script>
</body>
</html>

Result: Hello World!

39
4.0 ABOUT PROJECT

File Splitter is designed to keep record of all the data of the DUF’s, i.e., (DAILY
USAGE FILES). These files are used by telecom companies. They keep the data
in numeric form, which shows the time taken by particular customer.

Main objective of this project is to fetch the data from the DUF’s and then store
them in a separate database. It also informs all the customers about the data on
their email id.

4.1 Details of the project and related terms:

This project has been implemented to help in keeping data of all the customers
who are availing the services of United Telecom LLC company in New York. This
company uses the telecom server of Qwest company for all the proceedings.
Data from this server comes to United Telecom company in the form of DUF
(Daily Usage Files). These DUF’s contain the data in numeric form as these are
output from an electronic device. Therefore the foremost function of project is to
read all DUF’s . There is an ASCII* code file in which meaning of the sequence of
numbers from a particular location upto a particular location is defined. According
to data in ASCII file DUF is splitted and final data is sent to the email id of the
customer. File which contains all the details of the duration to which customer
has talked is called one Ani file. Each customer has more than one ani files. In
this project client is the distributed network, which works under the United LLC
company. They need data to be properly organized so that proper bill calculation
can be performed. Thus here a proper backend is also used which keeps all the
data of customer in the database. Main steps followed in the project preparation
are:

 Read all the DUF Files.

 Split the file according to the position of data to be matched with the data
in ASCII file.
 Read all the ani files and store them in one array.
 Read all the info of the customer.
 Add it to the file and database.
 Edit the customer file on user’s choice.
 Finally process the file of any customer and send email with attached
formatted call details.

It has been implemented using servlets and JSP technologies of java.

MYSQL database is used as the backend.

*ASCII file is attached in appendix with the source code and outptut of the
program

40
4.2 Main Packages and classes used:

 Package names: The hierarchy is like this->

Planetwire
|
Splitter

There are five packages which import them. They are:

 Util: containing all utility classes which provide all assertion

exceptions.
 Exceptions: contain all classes throwing all kinds of
exceptions.
 ao: It contains all abstract objects.
 eo: it contains all entity objects.
 teo: It contains all table entity objects.
 Servlets: It contains all the servlets.

 Class names: All classes import these packages. Classes are:

a) Util package:

Assert: It provides all the assertion exceptions.

AssertionException: It declares AssertionException which inherits all the

features of run time exception.

DAO Base: It is the main class in which all the functions related to the
database like creation, insertion, updation, deletion from the table takes
place.

DAO Constants: This is the interface which declares the basic variables
and is implemented by DAOBase class.

Session Objects: It declares all the session variables which may be used
during the session.

b) Exceptions:

AOException: It declares the exception for abstract objects and extends

the Exception class.

41
EOException: It declares the exception for entity objects and extends the
Exception class.

c) ao (abstract object) package:

AO Factory: This class takes input customer ao and returns instance of

the constructor.

BaseAO: This is the abstract class.

CustomerAO: It extends BaseAO and declares object of Customer entity

object. and then it takes its instance.

d) eo (entity object) package:

EO Factory: This class takes input customer eo and returns instance of

the constructor.

BaseEO: This is the abstract class.

CustomerEO: It extends BaseEO and declares constructor of Customer

entity object.

ControlEO: In this class function splits the files and extracts the required
information about client.

e) teo(table entity objects) package:

TEOani_row_table: It declares all the variables used in this table. It

extends the DAOBase class.

TEOclient: It declares all the variables used in the client table. It also
inherits the DAOBase class.

TEOclient_ani_details: it declares all the variables used in this table. It

extends the DAOBase class.

TEOunique_ani_temp: It declares ani_id and ani-name and the

constructor for the class.

f) servlets package:

42
InitServlet: It initializes all the variables declaring database username,
password, url and driverclass.

AddUserServlet: It takes all the details about the client like name, city,
country, companyname, emailed and send it add_client.jsp.

EditUserServlet: It takes all the details about the client like name, city,
country, companyname, emailed and send it to edit_client.jsp.

SplitterServlet: It splits the file by calling function of ControlEOClass.

 JSP files: These are:

add_client: It adds the information client by taking values through form.

edit_client: It edits the information client by taking values through form.
view_client: It shows the information about client.
ani_page: it shows details about anis.
Leftbar: This is the menu which provides all the choices of adding or
editing or viewing the details of the client.
process_file: In this file all the process of sending email and details to the
client has been implemented.
split_file: from this file we select the client to which we want to email the
information.

 SQL Tables:
There are 4 tables:
1. ani_row_table: It contains details of ani.its main fields are:
id integer PrimaryKey autoincrement
ani_id integer
row_details longtext

2.client : It contains details of client. Its main fields are :

client_id integer PrimaryKey autoincrement
client_name varchar
company_name varchar
client_city varchar
country varchar
client_emailed varchar

3.client_ani_details : Its main fields are:

id integer PrimaryKey autoincrement
client_id integer
ani_info varchar

4.unique_ani_temp: Its main fields are:

ani_id integer

43
 ani_name varchar

4.3 Detailed information about the main classes

Very important classes are:

1) DAOBase:
It performs all the SQL queries on the database entries. Its main
methods and their function:

 public DAOBase()

This constructor should never be invoked from any DAO class. It has been
added to avoid compilation errors. It will throw an Assertion exception if
invoked implying that the keys have not been set. If there are no primary
keys in the table, then invoke the alternate constructor as
<code>super(new String[0])</code>

 public DAOBase(String tableName)

Identify the primaryKeys and also the table name. This is done in cases
where the table name does not match the DAO. @param tableName:
Send in null if the derived class name matches the table name (- the DAO
prefix) Otherwise send in the appropriate table name from the DAO.

 private void mapProperties()

Converts all the fields in the derived class to internal properties for efficient
access

 private boolean isKey (String[] keys, PropertyMapper property)

Returns true if the property is a key in the current operation

 private PropertyMapper findMapper(String columnName)

Find the internal mapper corresponding to the column name (the attribute
of the class) specified as the parameter

 public void ignoreAttributes(String[] attributesToIgnore)

ignores the attributes in any of the insert, update or read calls. Helpful
when the attributes in the table are far more than the attributes required.

44
 public void setRequiredAttributes(String[] attributesToAdd)

adds the attributes in read calls. Helpful when the attributes in the table
are far more than the attributes required. If this call is not made, then all
attributes are included by default in a sql get.

 public DAOBase[] readAll(Connection conn, String[] keys)

Returns all the dao's appropriately initialized in an array. It is assumed that

all the keys are appropriately initialized Returns all the results
appropriately initialized on the basis of the keys supplied as the
parameter.

 public DAOBase[] readAll(Connection conn, String

whereClause)

Returns all the dao's appropriately initialized in an array. This code is for a
pass thru SQL Query, for cases where we need to get data based on a
custom sql query.

 public int create(Connection conn) throws SQLException

Tries to create a new row in the database. It initializes the primary key
after the insert in this object only. The call assumes that the primary keys
are not initialized and will be initialized after the call

 public void update(Connection conn, String[] updateKeys,

String[] primaryKeys) throws SQLException

Tries to update a row in the database on the basis of the key attributes
@param updateKeys The keys to be used for update (where clause in
update) @param primaryKeys The keys to be ignored in the set clause.

 public void update(Connection conn, String[] updateKeys, String

primaryKeys[], String whereClause) throws SQLException

Tries to update a row in the database on the basis of the where clause.
the whereClause would help update multiple rows at the same time.
@param whereClause Gives the condition for updation.

 public void delete(Connection conn, String[] keys) throws

SQLException

Deletes on the basis of the values of the primary keys

45
 public int count(Connection conn, String[] keys) throws
SQLException

Counts the no of rows that follow a particular criteria

 public int count(Connection conn, String whereClause) throws

SQLException

Counts the no of rows that follow the conditions specified through the
where clause.

 private String createWhereClause(String[] keys) throws

IllegalAccessException

This method creates where clause for all CRUD functions in this class that
doesn’t provide prebuilt whereClause.

 public void delete(Connection conn,//String[] keys, String

whereClause) throws SQLException

Deletes on the basis of the values of the where clause specified

 class PropertyMapper implements DAOConstants

This class maps the fields for a DAO to the appropriate datatypes and
provides methods such that these can be used in SQL strings. At the
same time it allows the setting of the field from the result set object. Allows
efficiency by mapping class fields just once in their lifetime.

 PropertyMapper(Field field, int index)

Initializes the PropertyMapper by using the field passed in.

@param field. The field that is being used in the property mapper
@param index. The index of the field in the table. This is with the
expectation that java will return the fields in the same order as the cols in
the database and that is based on the creation strategy in the table. to true
to manage the primary keys.

 void initializeDaoAttribute(Object dao, ResultSet result)

throws SQLException, IllegalAccessException

Set the attribute for the dao object based on the resultSet results

 String getSQLString(Object dao, boolean whereClause) throws

IllegalAccessException

46
 Returns the name=value in appropriate sql format. For eg, for dates and
strings it is appropriately quoted and for booleans also it is converted
appropriately Will be used in the where clause or the insert / update
clause

2) ControlEO :

public void getSplit(String filename)

There is only one method in this class and it performs the splitting of files
according the position of numbers in the files. Meaning of particular
position is read from the ASCII file. Thus it uses the substring function to
split the file.

These are the two major and most important classes which perform the
major functions.

47
REFERENCES

1)Complete Reference to java2 by Herbert Shield and Patrick Naughton

2)www.java.sun.com
3)www.w3schools.com
4)www.Microsoft.com
5)www.Msfrontpage.com
6)www.qwest.com
7)www.unitedllc.com
8)www.planetwire.net
9)www.bbcentric.com

ADDRESSES

Corporate Office of Planetwire

71 Storm Street, 4A
Tarrytown, NY 10591 USA

Fax & Voice: +1 801 848 5608

[email protected]

Corporate Office of United Telecom LLC

Customer Service: 800-535-6000

Main Number: 516-487-8730

Fax: 516-487-5692

Toll Free Fax Number: 866-456-1100

Address:175,GREATNECKROAD

Suite404
Great Neck, NY 11021

48
 APPENDIX – A
PROJECT CODE:
1) CLASSES

Util package:

Assert

package planetwire.splitter.util;
public class Assert {

public Assert() {
}

public static void assertFalse(boolean expression){

if(!expression)
throw new AssertionException("A false expression encountered where it
was not expected.");
}
public static void assertFalse(boolean expression, String msg){
if(!expression)
throw new AssertionException("A false expression encountered where it
was not expected. Message=" +
msg);
}

public static void assertNull(Object val){

if(val == null)
throw new AssertionException("A null expression encountered where it
was not expected.");
}
public static void assertNull(Object val, String msg){
if(val == null)
throw new AssertionException("A null expression encountered where it
was not expected. Message=" +
msg);
}

public static void assertParsingError(String msg) {

throw new AssertionException("A Parsing Error occured while reading the report
XML Document=" +msg);
}
}

AssertionException

package planetwire.splitter.util;
public class AssertionException extends RuntimeException {
public AssertionException(String msg){
super(msg);
}

49
}

DAOBase

package planetwire.splitter.util;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Hashtable;
import java.util.Vector;

public class DAOBase implements DAOConstants {

/**
* If the DAOs are mapped exactly in the same order as in the database, then we may
* try to retrieve them by column numbers. The default is false
*/
public static boolean INDEX_MAPPING = false;
private static Hashtable propertyMap = new Hashtable();

/**
* All the field names are mapped to properties for easier access
*/
PropertyMapper[] arPropertyMapper = null;

/**
* The attributes that should be ignored during a read, insert or update
* @see ignoreAttributes
*/
private String[] attributesToIgnore;

/**
* The name of the table which the DAO links to. In the future this can be made a view
* on the database. Let's see how successful the DAO class structure is
*/
private String tableName;

public static String dbURL = null;

public static String dbUser= null;
public static String dbPassword = null;
public static String driverClass = null;

private static Connection connection = null;

/**
* once set to true, the connection will not be initialized again.
* ensures that we (vm)don't spend our entire time trying to connect even when
* we can't do so successfully
*/

private static boolean noMoreConnects = false;

50
 /**
* This constructor should never be invoked from any DAO class. It has been added to
* avoid compilation errors. It will throw an Assertion exception if invoked
* implying that the keys have not been set. If there are no primary keys
* in the table, then invoke the alternate constructor as
* <code>super(new String[0])</code>
*/
public DAOBase(){
this(null);
}

public static Connection getConnection(){

if(connection != null)
return connection;
synchronized(DAOBase.class){
if(connection == null && noMoreConnects == false){
noMoreConnects = true;
try{
dbURL = "jdbc:mysql://localhost:3306/splitter";
dbUser = "root";
dbPassword="ankita";
driverClass="com.mysql.jdbc.Driver";
Assert.assertNull(dbURL, "Please specify a valid
database URL.");
Assert.assertNull(dbUser, "Please specify a valid user
name for database access.");
Assert.assertNull(dbPassword, "Please specify a valid
password for database access.");
Assert.assertNull(driverClass, "Please specify a valid
driver for database access.");

Class.forName(driverClass);
connection = DriverManager.getConnection(dbURL,
dbUser, dbPassword);
} catch(Exception ex)
{
ex.printStackTrace();
Assert.assertNull(connection, "The database connection
is currently unavailable.");
}
}
return connection;
}
}

protected String getTableName(){

return tableName;
}
/**
* A NOP as of now but we have to ensure that for every getConnection we have a
releaseConnection
*/
public static void releaseConnection(Connection conn) throws SQLException{
//conn.close();

51
 }
/**
* Identify the primaryKeys and also the table name. This is done in cases
* where the table name does not match the DAO.
* @param tableName: Send in null if the derived class name matches the table name (-
the DAO prefix)
* Otherwise send in the appropriate table name from the DAO.
*/
public DAOBase(String tableName){
if(tableName != null)
this.tableName = tableName.toUpperCase();
else {
String className = getClass().getName();
int index = className.lastIndexOf('.');
className = className.substring(index + 1);
Assert.assertFalse(className.startsWith(CLASS_PREFIX),
"Don't know how to derive the table name from the class=" +
className);
this.tableName = className.substring(3).toUpperCase(); // gives the
class name after the dao
}
mapProperties();
//debug();
}

/**
* Converts all the fields in the derived class to internal properties for efficient access
*/
private void mapProperties(){
Field[] fields = super.getClass().getDeclaredFields();
arPropertyMapper = (PropertyMapper[])propertyMap.get(getClass().getName());
if(arPropertyMapper == null) {
arPropertyMapper = new PropertyMapper[fields.length];
PropertyMapper elem;
for(int i = 0; i < fields.length; i++){
if(INDEX_MAPPING)
elem = new PropertyMapper(fields[i], i);
else
elem = new PropertyMapper(fields[i]);
arPropertyMapper[i] = elem;
}
propertyMap.put(getClass().getName(), arPropertyMapper);
}
//System.out.println("the no of keys::"+propertyMap.size());
}

/**
* Returns true if the property is a key in the current operation
*/
private boolean isKey(String[] keys, PropertyMapper property){
for(int i = 0; i < keys.length; i++)
if(keys[i].equalsIgnoreCase(property.name))
return true;
return false;
}

52
 /**
* Find the internal mapper corresponding to the column name (the attribute of the class)
* specified as the parameter
*/
private PropertyMapper findMapper(String columnName){
for(int i = 0; i < arPropertyMapper.length; i++)
if(arPropertyMapper[i].name.equalsIgnoreCase(columnName))
return arPropertyMapper[i];
return null;
}

/**
* ignores the attributes in any of the insert, update or read calls. Helpful when the
* attributes in the table are far more than the attributes required.
*/
public void ignoreAttributes(String[] attributesToIgnore){
this.attributesToIgnore = attributesToIgnore;
}

/**
* adds the attributes in read calls. Helpful when the
* attributes in the table are far more than the attributes required.
* If this call is not made, then all attributes are included by default in a sql get
*/

public void setRequiredAttributes(String[] attributesToAdd)

{
Vector ignoreAttrib = new Vector();
for(int i = 0; i < arPropertyMapper.length; i++)
{
boolean found = false;
for(int jj=0; jj<attributesToAdd.length;jj++)
{
if
(arPropertyMapper[i].name.equalsIgnoreCase(attributesToAdd[jj]))
{
found = true;
break;
}
}
if (!found) {
//System.out.println("\narPropertyMapper[i]"+arP
ropertyMapper[i].name);

ignoreAttrib.addElement(arPropertyMapper[i].name);
}
}
String[] attributesToIgnore = new String[ignoreAttrib.size()];
//System.out.println("ignoreAttrib"+ignoreAttrib);
ignoreAttrib.copyInto(attributesToIgnore);
// make the call to ignore the attributes
ignoreAttributes(attributesToIgnore);
}

53
 /**
* Returns true if the attribute is to be ignored
*/
private boolean isAttributeIgnored(String attribName){
if(attributesToIgnore == null)
return false;
for(int i = 0; i < attributesToIgnore.length; i++)
if(attributesToIgnore[i].equalsIgnoreCase(attribName))
return true;
return false;
}

/**
* Returns all the dao's appropriately initialized in an array. It is assumed that
* all the keys are appropriately initialized
* Returns all the results appropriately initialized on the basis of the keys supplied
* as the parameer
*
* The code can be executes as follows:
* <code>DAOMyTable dao = new DAOMyTable();
* dao.property1 = <some value>
* dao.property2 = <some other value>;
* String keys[] = {"property1", "property2"};
* DAOMyTable[] daos= dao.readAll(MyConnectionManager.getConnection(), keys);
* if(daos.length > 0) {// record(s) have been found
* .
* .
* }else{ // no record has been found
* .
* .
* }</code>
*
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*
*/
public DAOBase[] readAll(Connection conn, String[] keys)
throws SQLException {
Connection connOnParameter = conn;

try
{

StringBuffer sql = new StringBuffer("SELECT ");

boolean foundFirst = false;
if(attributesToIgnore == null)
sql.append("*");
else{
for(int i = 0; i < arPropertyMapper.length; i++){
if(!
isAttributeIgnored(arPropertyMapper[i].name) ){
if(foundFirst)
sql.append(",");
else
foundFirst = true;
sql.append(arPropertyMapper[i].name);

54
 }
}
}
sql.append(" FROM " + tableName);
String whereClause = createWhereClause(keys);
sql.append(whereClause);
System.out.println("SQL String is " + sql.toString());

// stmt is created. Now we execute it and initialize the results.

if (conn==null)
conn = getConnection();
Vector vec = new Vector();
Class objClass = getClass();
// run the database query
Statement st = conn.createStatement();
ResultSet results = st.executeQuery(sql.toString());

//System.out.println("SELECT QUERY:"+sql.toString());
// create a new instance of
while(results.next()){
DAOBase newObject =
(DAOBase)objClass.newInstance();
for(int i = 0; i < arPropertyMapper.length; i++)
if(!isAttributeIgnored(arPropertyMapper[i].name))

arPropertyMapper[i].initializeDaoAttribute(newObject, results);
vec.add(newObject);
}
st.close();
DAOBase[] resultAr = new DAOBase[vec.size()];
vec.copyInto(resultAr);
return resultAr;
} catch(IllegalAccessException il)
{
il.printStackTrace();
}catch (InstantiationException ex)
{
ex.printStackTrace();
}
finally{
if(connOnParameter == null) // a connection has been obtained
releaseConnection(conn);
}
return new DAOBase[0];
}

/**
* Returns all the dao's appropriately initialized in an array.
* This code is for a pass thru SQL Query, for cases where we need to
* get data based on a custom sql query.
*
* The code can be executes as follows:
* <code>DAOMyTable dao = new DAOMyTable();
* String WHERE = "attribute1 LIKE "" AND attribute2 BETWEEN date1 and date2";
* DAOMyTable[] daos= dao.readAll(MyConnectionManager.getConnection(), WHERE);
* if(daos.length > 0) {// record(s) have been found

55
 * .
* .
* }else{ // no record has been found
* .
* .
* }</code>
*
*
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
public DAOBase[] readAll(Connection conn, String whereClause)
throws SQLException {
Connection connOnParameter = conn;

try
{
StringBuffer sql = new StringBuffer("SELECT ");
boolean foundFirst = false;
if(attributesToIgnore == null)
sql.append("*");
else{
for(int i = 0; i < arPropertyMapper.length; i++){
if(!isAttributeIgnored(arPropertyMapper[i].name))
{
if(foundFirst)
sql.append(",");
else
foundFirst = true;
sql.append(arPropertyMapper[i].name);
}
}
}
sql.append(" FROM " + tableName);
if (!whereClause.equals(""))
{

sql.append(" WHERE " + whereClause);

}

System.out.println("SQL String is " + sql.toString());

// stmt is created. Now we execute it and initialize the results.
if (conn==null)
conn = getConnection();
Vector vec = new Vector();
Class objClass = getClass();
// run the database query
Statement st = conn.createStatement();
ResultSet results = st.executeQuery(sql.toString());
// create a new instance of
while(results.next()){
DAOBase newObject =
(DAOBase)objClass.newInstance();
for(int i = 0; i < arPropertyMapper.length; i++)
if(!isAttributeIgnored(arPropertyMapper[i].name))

56
 arPropertyMapper[i].initializeDaoAttribute(newObject, results);
vec.add(newObject);
}
st.close();
DAOBase[] resultAr = new DAOBase[vec.size()];
vec.copyInto(resultAr);
return resultAr;
} catch(IllegalAccessException il)
{
il.printStackTrace();
}catch (InstantiationException ex)
{
ex.printStackTrace();
}finally{
if(connOnParameter == null) // a new connection was allotted
releaseConnection(conn);
}
return new DAOBase[0];
}

public String[] readAll(Connection conn, String Distinct_key, String whereClause)

throws SQLException {
Connection connOnParameter = conn;

try
{
StringBuffer sql = new StringBuffer("SELECT ");

boolean foundFirst = false;

if(attributesToIgnore == null)
{

//sql.append(Distinct_key);
}
else{
sql.append("DISTINCT ");
for(int i = 0; i < arPropertyMapper.length; i++){
if(!
isAttributeIgnored(arPropertyMapper[i].name)){
if(foundFirst)
sql.append(",");
else
foundFirst = true;

sql.append(arPropertyMapper[i].name);
}
}
}
sql.append(" FROM " + tableName);
if (!whereClause.equals(""))
{

sql.append(" WHERE " + whereClause);

}

57
 System.out.println("SQL String is " + sql.toString());
// stmt is created. Now we execute it and initialize the
results.
if (conn==null)
conn = getConnection();
Vector vec = new Vector();
Class objClass = getClass();
Statement st = conn.createStatement();
// run the database query

int j=0;

ResultSet results = st.executeQuery(sql.toString());

//results.first();
while(results.next())
{

vec.add(results.getString(1));
j++;

}
String[] resultAr = new String[j];
st.close();
vec.copyInto(resultAr);
for(int i=0;i<resultAr.length;i++)
System.out.println(resultAr[i]);
return resultAr;
} catch (Exception ex)
{
ex.printStackTrace();
}finally{
if(connOnParameter == null) // a new connection was
allotted
releaseConnection(conn);
}
return new String[0];
}

public DAOBase[] readAllSorted(Connection conn, String Orderby_key, String

whereClause, String sort_order)
throws SQLException {
Connection connOnParameter = conn;

try
{
StringBuffer sql = new StringBuffer("SELECT ");
boolean foundFirst = false;
if(attributesToIgnore == null)
{
sql.append("*");
}
else{
for(int i = 0; i <
arPropertyMapper.length; i++){
if(!
isAttributeIgnored(arPropertyMapper[i].name)){

58
 if(foundFirst)
sql.append(",");
else
foundFirst =
true;

sql.append(arPropertyMapper[i].name);
}
}
}
sql.append(" FROM " + tableName);
if (!whereClause.equals(""))
{

sql.append(" WHERE " + whereClause);

}

sql.append(" ORDER BY ");

sql.append(Orderby_key);
sql.append(" "+sort_order);
System.out.println("SQL String is " +
sql.toString());
// stmt is created. Now we execute it and
initialize the results.
if (conn==null)
conn = getConnection();
Vector vec = new Vector();
Class objClass = getClass();
// run the database query
Statement st = conn.createStatement();
ResultSet results =
st.executeQuery(sql.toString());
// create a new instance of
while(results.next()){
DAOBase newObject =
(DAOBase)objClass.newInstance();
for(int i = 0; i <
arPropertyMapper.length; i++)
if(!
isAttributeIgnored(arPropertyMapper[i].name))

arPropertyMapper[i].initializeDaoAttribute(newObject, results);
vec.add(newObject);
}
st.close();
DAOBase[] resultAr = new DAOBase[vec.size()];
vec.copyInto(resultAr);

return resultAr;
} catch(IllegalAccessException il)
{
il.printStackTrace();
}catch (InstantiationException ex)
{
ex.printStackTrace();
}finally{

59
 if(connOnParameter == null) // a new connection
was allotted
releaseConnection(conn);
}
return new DAOBase[0];
}

/**
* Tries to create a new row in the database. It initializes the primary key after the insert
* in this object only. The call assumes that the primary keys are not initialized and will
* be initialized after the call
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
public int create(Connection conn) throws SQLException {
// create the statement
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("INSERT INTO " +
tableName);
StringBuffer names = new StringBuffer(), values= new
StringBuffer();
boolean firstFound = false;
for(int i = 0; i < arPropertyMapper.length; i++){
PropertyMapper mapper = arPropertyMapper[i];
if(isAttributeIgnored(mapper.name))
continue;
if(firstFound){
names.append(",");
values.append(",");
}
names.append(mapper.name);
values.append(mapper.getValue(this));
if(!firstFound)
firstFound = true;
}
sql.append(" (").append(names.toString()).append(") VALUES
(").append(values.toString()).append(")");

System.out.println(sql.toString());
if (conn==null)
conn = getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql.toString());
// ------------------------------------------------------
// get the new identity row -- specific for SQL Server
//java.util.Vector vec = new java.util.Vector();
//Class objClass = getClass();
// run the database query
Statement st = conn.createStatement();

//ResultSet rowId = st.executeQuery("SELECT @@IDENTITY

AS 'PKEY' from " + tableName);
// create a new instance of

60
 // System.out.println("---------Before----------------");
// int pkey = -1;
// while (rowId.next())
// pkey = rowId.getInt("PKEY");
// return pkey;
// //-------------------------------------------------------

// TODO to check how to get the value of the primary keys and to
initialize them
// in the current class.
//conn.commit();
} catch(IllegalAccessException il){
il.printStackTrace();
}finally{
if(connOnParameter == null) // conn would have been allocated in this
method.
releaseConnection(conn);
}
return -1;
}

/**
* Tries to create a new row in the database. It initializes the primary key after the insert
* in this object only. The call assumes that the primary keys are not initialized and will
* be initialized after the call Returns the pkey after
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
/*public int createPrimary(Connection conn, String[] primaryKeys) throws SQLException{
// create the statement
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("INSERT INTO " +
tableName);
StringBuffer names = new StringBuffer(), values= new
StringBuffer();
boolean firstFound = false;
for(int i = 0; i < arPropertyMapper.length; i++){
PropertyMapper mapper = arPropertyMapper[i];
if(isAttributeIgnored(mapper.name) || isKey(primaryKeys,
mapper))
continue;
if(firstFound){
names.append(",");
values.append(",");
}
names.append(mapper.name);
values.append(mapper.getValue(this));
if(!firstFound)
firstFound = true;
}
sql.append(" (").append(names.toString()).append(") VALUES
(").append(values.toString()).append(")");

System.out.println(sql.toString());

61
 if (conn==null)
conn = getConnection();
CallableStatement stmt = conn.prepareCall("{call
insertNewRow(?,?)}"); //prepares the call
//setting the values to be passed
stmt.setString(1,sql.toString());
stmt.registerOutParameter(2, Types.INTEGER);
//TODO - tb - make sure somehow(!!!!) that this code works and
actually
// synchronizes the stored procedure call.
synchronized(DAOBase.class){
stmt.execute(); //executes the procedure
}
int retVal = stmt.getInt(2);
return retVal;
//-------------------------------------------------------

// TODO to check how to get the value of the primary keys and to
initialize them
// in the current class
} catch(IllegalAccessException il)
{
new saigun.pms.util.ErrorLogger("Illegal Access Exception", il);
}finally{
if(connOnParameter == null) // conn setup in this method
releaseConnection(conn);
}
return -1;
}
*/
/**
* Tries to update a row in the database on the basis of the key attributes
* @param updateKeys The keys to be used for update (where clause in update)
* @param primaryKeys The keys to be ignored in the set clause.
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
public void update(Connection conn, String[] updateKeys, String[] primaryKeys) throws
SQLException{
// create the statement
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("UPDATE " + tableName + "
SET ");
// create the set clause
StringBuffer values= new StringBuffer();
boolean firstFound = false;
for(int i = 0; i < updateKeys.length; i++){
PropertyMapper mapper = findMapper(updateKeys[i]);
Assert.assertNull(mapper, "Mapper could not be
obtained for the key=" + updateKeys[i]);
if(isAttributeIgnored(mapper.name) || isKey(primaryKeys,
mapper)) // a primary key cannot be updated
continue;
if(firstFound)

62
 values.append(",");
values.append(mapper.getSQLString(this, false));
if(!firstFound)
firstFound = true;
}
sql.append(values.toString()).append("");
values = new StringBuffer();
firstFound = false;

String whereClause = createWhereClause(primaryKeys);

sql.append(whereClause);

System.out.println(sql.toString());
if (conn==null)
conn = getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql.toString());
//conn.commit();
} catch(IllegalAccessException il)
{
il.printStackTrace();
}finally{
if(connOnParameter == null) // the connection was created in this method
releaseConnection(conn);
}
}

//update with where clause.

/**
* Tries to update a row in the database on the basis of the where clause.
* the whereClause would help update multiple rows at the same time.
* @param whereClause Gives the condition for updation.
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/

public void update(Connection conn, String[] updateKeys, String primaryKeys[], String

whereClause) throws SQLException {
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("UPDATE " + tableName + "
SET ");
// create the set clause
StringBuffer values= new StringBuffer();
boolean firstFound = false;
for(int i = 0; i < updateKeys.length; i++){
PropertyMapper mapper = findMapper(updateKeys[i]);
Assert.assertNull(mapper, "Mapper could not be
obtained for the key=" + updateKeys[i]);
if(isAttributeIgnored(mapper.name) || isKey(primaryKeys,
mapper)) // a primary key cannot be updated
continue;
if(firstFound)
values.append(",");
values.append(mapper.getSQLString(this, false));

63
 if(!firstFound)
firstFound = true;
}

sql.append(values.toString());
sql.append(" WHERE ").append(whereClause);

System.out.println(sql.toString());

if (conn==null)
conn = getConnection();
Statement stmt = conn.createStatement();
//System.out.println(sql);
stmt.executeUpdate(sql.toString());
//conn.commit();
} catch(IllegalAccessException il) {
il.printStackTrace();
}finally{
if(connOnParameter == null) // the connection was created in this method
releaseConnection(conn);
}
}

/**
* Deletes on the basis of the values of the primary keys
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
public void delete(Connection conn, String[] keys) throws SQLException {
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("DELETE FROM " +
tableName);
String whereClause = createWhereClause(keys);

sql.append(whereClause);

System.out.println(sql.toString());

if (conn == null)
conn = getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql.toString());
//conn.commit();
}catch(IllegalAccessException il)
{
il.printStackTrace();
}finally{
if(connOnParameter == null)
releaseConnection(conn);
}
}

/**
* Counts the no of rows that follow a particular criteria

64
 */
public int count(Connection conn, String[] keys) throws SQLException {
int count = -1;
Connection connOnParameter = conn;
try {
StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM " +
tableName);
String whereClause = createWhereClause(keys);

sql.append(whereClause);

System.out.println(sql.toString());

if (conn==null)
conn = getConnection();

Statement stmt = conn.createStatement();

ResultSet results = stmt.executeQuery(sql.toString());
//System.out.println(sql);
while(results.next()){
count = results.getInt(1);
}

results.close();
stmt.close();
} catch(IllegalAccessException il) {
il.printStackTrace();
}finally {
if(connOnParameter == null) // a new connection was allotted
releaseConnection(conn);
}
return count;
}

/**
* Counts the no of rows that follow the conditions specified through
* the where clause.
*/
public int count(Connection conn, String whereClause) throws SQLException {
int count = -1;
Connection connOnParameter = conn;
try{
StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM " +
tableName);
sql.append(" WHERE ").append(whereClause);

System.out.println(sql.toString());

if (conn==null)
conn = getConnection();

Statement stmt = conn.createStatement();

ResultSet results = stmt.executeQuery(sql.toString());
//System.out.println(sql);
while(results.next()){
count = results.getInt(1);

65
 }

results.close();
stmt.close();
}catch(SQLException sqle) {
throw new SQLException();
}finally {
if(connOnParameter == null) // a new connection was allotted
releaseConnection(conn);
}
return count;
}

/**
* This method creates where clause for all CRUD functions in this class that don't
provide
* prebuilt whereClause.
*/
private String createWhereClause(String[] keys) throws IllegalAccessException {
StringBuffer values = new StringBuffer();
boolean firstFound = false;
for(int i = 0; i < keys.length; i++) {
if(firstFound)
values.append(" AND ");
// if(isAttributeIgnored(keys[i]))
// continue;
PropertyMapper mapper = findMapper(keys[i]);
Assert.assertNull(mapper, "The mapper not available for table name="+
tableName +" and key="+keys[i]);

values.append(mapper.getSQLString(this, true));
if(!firstFound)
firstFound = true;
}
if(values.length() > 0)
return " WHERE " + values.toString();
else
return "";
}

/**
* Deletes on the basis of the values of the where clause specified
* @param conn Pass a valid connection object if this call is a part of a transaction
* updating multiple tables. Otherwise we should set this parameter to null.
*/
public void delete(Connection conn,
//String[
] keys,
String
whereClause) throws SQLException {
Connection connOnParameter = conn;
try
{
StringBuffer sql = new StringBuffer("DELETE FROM " +
tableName), values;
values = new StringBuffer();

66
 boolean firstFound = false;

sql.append(" WHERE ").append(whereClause);

System.out.println(sql.toString());
if (conn==null)
conn = getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql.toString());
}catch(Exception il)
{
il.printStackTrace();
}finally{
if(connOnParameter == null)
releaseConnection(conn);
}
}
}

/**
* This class maps the fields for a DAO to the appropriate datatypes and provides
* methods such that these can be used in SQL strings. At the same time it allows the
* setting of the field from the result set object. Allows efficiency by mapping class
* fields just once in their lifetime.
*
*/
class PropertyMapper implements DAOConstants{
private static final int STRING_CLASS = 100;
private static final int CHAR_CLASS = 101;

private static final int INTEGER_CLASS= 200;

private static final int LONG_CLASS =201;
private static final int FLOAT_CLASS =202;
private static final int DOUBLE_CLASS =203;
private static final int SHORT_CLASS = 204;

private static final int BOOLEAN_CLASS=300;

private static final int DATE_CLASS=400;

static SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("MM-dd-yyyy

HH:mm:ss");

String name;
int typeId;
int index;
Field field;

/**
* Initializes the PropertyMapper by using the field passed in.
* @param field. The field that is being used in the property mapper
* @param index. The index of the field in the table. This is with the expectation
* that java will return the fields in the same order as the cols in the database and
* that is based on the creation strategy in the table.

67
 * to true to manage the primary keys.
*/
PropertyMapper(Field field, int index){
this.field = field;
name = field.getName().toUpperCase();
Class fieldClass = field.getType();
// identify the type of the field for use later
if(fieldClass == String.class)
typeId = STRING_CLASS;
else if(fieldClass == Integer.TYPE)
typeId = INTEGER_CLASS;
else if(fieldClass == Boolean.TYPE)
typeId = BOOLEAN_CLASS;
else if(fieldClass == Short.TYPE)
typeId = SHORT_CLASS;
else if(fieldClass == Float.TYPE)
typeId = FLOAT_CLASS;
else if(fieldClass == Double.TYPE)
typeId = DOUBLE_CLASS;
else if(fieldClass == java.util.Date.class || fieldClass ==
java.sql.Date.class)
typeId = DATE_CLASS;
else if(fieldClass == Character.TYPE)
typeId = CHAR_CLASS;
else
Assert.assertFalse(false, "The class of type " +
fieldClass.getName() + " for the field " + name + " has not been mapped to a reconizable
type. FIX this problem.");
}

/**
* If DAO's are mapped according to indexes then call the other method
*/
PropertyMapper(Field field){
this(field, -1);
}

/**
* Set the attribute for the dao object based on the resultSet results
*/
void initializeDaoAttribute(Object dao, ResultSet result)
throws SQLException, IllegalAccessException{
Object value;
if(index > 0)
value = result.getObject(index);
else
value = result.getObject(name);
setValue(dao, value);
}
/**
* Returns the name=value in appropriate sql format. For eg, for dates and strings
* it is appropriately quoted and for booleans also it is converted appropriately
* Will be used in the where clause or the insert / update clause
*/
String getSQLString(Object dao, boolean whereClause) throws IllegalAccessException{
String sql = name;

68
 String value = getValue(dao);
String equalsClause = ((value == null || value.equalsIgnoreCase("null")) &&
whereClause)?
" IS " : "=";
return sql + equalsClause + value;
}

String getValue(Object dao) throws IllegalAccessException{

Object value = field.get(dao);
switch(typeId){
case STRING_CLASS:
if(value != null && value.toString().length() > 0) {
if(value.toString().indexOf('\'') != -1)
return QUOTE_CHAR +
replace(value.toString(),"'","''") + QUOTE_CHAR;
else
return QUOTE_CHAR + value.toString() +
QUOTE_CHAR;
}
else
return "null";
case CHAR_CLASS:
if(value != null) {
if(value.toString().indexOf('\'') != -1)
return QUOTE_CHAR +
replace(value.toString(),"'","''") + QUOTE_CHAR;
else
return QUOTE_CHAR + value.toString() +
QUOTE_CHAR;
}
else
return "null";
//return QUOTE_CHAR + (value !=null? value.toString():"") +
QUOTE_CHAR;
case DATE_CLASS:
if(value == null)
return "null";
else
{

return QUOTE_CHAR + dateFormat.format(value) +

QUOTE_CHAR;
}
case BOOLEAN_CLASS:
return String.valueOf((value != null &&
((Boolean)value).booleanValue())?1:0);
case INTEGER_CLASS:
return (value != null && ((Integer)value).intValue() !=
NULL_INT)?
value.toString() : "null";
case SHORT_CLASS:
return (value != null && ((Short)value).shortValue() !=
NULL_SHORT)?
value.toString() : "null";
case LONG_CLASS:

69
 return (value != null && ((Long)value).longValue() !=
NULL_LONG)?
value.toString() : "null";
case FLOAT_CLASS:
return (value != null && ((Float)value).floatValue() !=
NULL_FLOAT)?
value.toString() : "null";
case DOUBLE_CLASS:
return (value != null && ((Double)value).intValue() !=
NULL_DOUBLE)?
value.toString() : "null";
default:
Assert.assertFalse(false, "Method to convert name=" + name + "
in dao not found");
}
return "";
}

void setValue(Object obj, Object val)throws IllegalAccessException{

switch(typeId){
case BOOLEAN_CLASS:
if(val == null)
val = new Boolean(false);
else if(val instanceof Integer){
int intVal = ((Integer)val).intValue();
val = new Boolean(intVal != 0);
}else if(val instanceof Character){
char charVal = ((Character)val).charValue();
val = new Boolean(charVal == 'T' || charVal == 'Y' ||
charVal == 't' || charVal == 'y' || charVal == '1');
}else if(val instanceof Boolean == false){ // Should be a string if
nothing else
String strVal = val.toString().toUpperCase();
val = new Boolean(strVal.equals("T") ||
strVal.equals("Y")||strVal.equals("1"));
}
break;
case SHORT_CLASS:
if(val == null)
val = new Short(NULL_SHORT);
else if(val instanceof Integer)
val = new Short(((Integer)val).shortValue());
else
Assert.assertFalse(val instanceof Short, "Type
not recognized while converting short");
break;
case FLOAT_CLASS:
if(val == null)
val = new Float(NULL_FLOAT);
else if(val instanceof Double)
val = new Float(((Double)val).floatValue());
break;
case INTEGER_CLASS:
if(val == null)val = new Integer(NULL_INT);
break;
case LONG_CLASS:

70
 if(val == null)val = new Long(NULL_LONG);
break;
case DOUBLE_CLASS:
if(val == null)val = new Double(NULL_DOUBLE);
break;
default:
// nothing to be done here
}
field.set(obj, val);
}

private String replace(String str, String searchFor, String replaceWith) {

int s = 0; int e = 0;
StringBuffer result = new StringBuffer();

while ((e = str.indexOf(searchFor, s)) >= 0) {

result.append(str.substring(s, e));
result.append(replaceWith);
s = e + searchFor.length();
}
result.append(str.substring(s));
//System.out.println("the result is "+result);
return result.toString();
}
}

DAOConstants

package planetwire.splitter.util;
public interface DAOConstants {
public static int NULL_INT = Integer.MIN_VALUE;
public static boolean NULL_BOOLEAN = false;
public static short NULL_SHORT = Short.MIN_VALUE;
public static float NULL_FLOAT = Float.MIN_VALUE;
public static double NULL_DOUBLE = Double.MIN_VALUE;
public static String NULL_STRING = null;
public static long NULL_LONG = Long.MIN_VALUE;

// The quote character may change over the databases, hence a constant
public static String QUOTE_CHAR = "'";
// The prefix for all the dao classes generated corresponding to the database schema
public static String CLASS_PREFIX = "TEO";

public static String DB_WILDCARD_CHAR = "%"; // for SQL Server

}

SessionObjecs

package planetwire.splitter.util;

public class SessionObjects {

public static final String LOGIN_USER = "LOGIN_USER";

71
 public static final String MSG_DESIRE = "MSG_DESIRE";
public static final String MSG_SENT = "MSG_SENT";
public static final String TEMP = "TEMP";
//Holds Login UserTEO
}

ao package

AOFactory

package planetwire.splitter.ao;

import planetwire.splitter.util.Assert;

public class AOFactory {

private static AOFactory instance = null;

public static final String CUSTOMER_AO = "customer_ao";

public static final String SMS_AO = "sms_ao";

private AOFactory() {
}

public static AOFactory getInstance() {

if(instance == null)
instance = new AOFactory();
return instance;
}

public BaseAO getAO(String aoName) {

if(aoName.equals(CUSTOMER_AO))
return new CustomerAO();
/* else if(aoName.equals(SMS_AO))
return new SMSAO();*/
else {
Assert.assertFalse(false , "The name supplied for AO was wrong.");
return null;
}
}
}

BaseAO

package planetwire.splitter.ao;

public abstract class BaseAO {

}

CustomerAO

package planetwire.splitter.ao;

72
import planetwire.splitter.eo.CustomerEO;
import planetwire.splitter.eo.EOFactory;
import planetwire.splitter.exception.AOException;
import planetwire.splitter.exception.EOException;
import planetwire.splitter.teo.TEOclient;

public class CustomerAO extends BaseAO {

CustomerEO customerEO = (CustomerEO)
EOFactory.getInstance().getEO(EOFactory.CUSTOMER_EO);

/*public TEOclient login(String userId, String password) throws AOException {

try {
return customerEO.login(userId, password);
}catch(EOException e) {
throw new AOException();
}
}

public TEOclient getCustomer(int cust_id) throws AOException {

try {
return customerEO.getCustomer(cust_id);
}catch(EOException e) {
throw new AOException();
}
} */
}

eo package

BaseEO

package planetwire.splitter.eo;

public abstract class BaseEO {

}

CustomerEO

package planetwire.splitter.eo;

import planetwire.splitter.exception.EOException;
import planetwire.splitter.util.DAOBase;

public class CustomerEO extends BaseEO {

public CustomerEO() {
}
}

EOFactory

package planetwire.splitter.eo;

73
import planetwire.splitter.util.Assert;

public class EOFactory {

private static EOFactory instance = null;

public static final String CUSTOMER_EO = "customer_eo";

public static final String SMS_EO = "sms_eo";

private EOFactory() {
}

public static EOFactory getInstance() {

if(instance == null)
instance = new EOFactory();
return instance;
}

public BaseEO getEO(String eoName) {

if(eoName.equals(CUSTOMER_EO))
return new CustomerEO();
/*else if(eoName.equals(SMS_EO))

return new SMSEO();*/

else {
Assert.assertFalse(false , "The name supplied for EO was wrong.");
return null;
}
}
}

HelperEO

package planetwire.splitter.eo;

public class HelperEO extends BaseEO {

protected HelperEO() {
}
}

ControlEO

package planetwire.splitter.eo;

import java.io.*;
import java.util.*;

import planetwire.splitter.teo.TEOani_row_table;
import planetwire.splitter.teo.TEOunique_ani_temp;
import planetwire.splitter.util.DAOBase;

public class ControlEO extends BaseEO {

public ControlEO() {

74
}
public void getSplit(String filename)
{
int i=0,j=0,k=0;
Vector temp = new Vector(10,1);
String arr[] = null;
int verify = 0;
String[][] arr1 =null;
try{
BufferedReader file = new BufferedReader(new FileReader(filename));
String line;
file.readLine();
String match = "";
while(((line=file.readLine()) != null))
{

if(line.length()>23)
match = line.substring(14,24);
if(i==0)
{

temp.add(match);
arr = new String[temp.size()];
temp.toArray(arr);
//System.out.println(line);

//arr[0] = match;
k++;
i++;
continue;
}
if(k>0)
{
//System.out.println("l"+arr.length);
verify=0;
for(j=0;j<arr.length;j++)
{
if(match.equals(arr[j]))
{
verify=1;
break;
}
else
{
//arr[k] = match;
//temp.add(match);
continue;
}
}
if(verify==0)
temp.add(match);
//arr=null;

}
arr = null;

75
 arr = new String[temp.size()];
temp.toArray(arr);
/*System.out.println("\ni="+i);
System.out.println("\nline="+line);*/
k++;
i++;
}
file = new BufferedReader(new FileReader(filename));
arr1 = new String[arr.length-1][];
int arr2[] = new int[arr.length-1];
TEOunique_ani_temp anis = new TEOunique_ani_temp();
anis.delete(null,"1");
for(i=0;i<arr.length-1;i++)
{
arr2[i] = 0;
arr1[i] = new String[110];
anis.ani_id = i+1;
anis.ani_name = arr[i];
anis.create(null);
}
TEOunique_ani_temp[] ani_list = null;
DAOBase[] ar =null;
ar = anis.readAll(null,"");
if(ar.length!=0)
{
ani_list = new TEOunique_ani_temp[ar.length];
System.arraycopy(ar,0,ani_list,0,ar.length);

TEOani_row_table row = new TEOani_row_table();

row.delete(null,"1");
while((line=file.readLine())!=null)
{
match = "";
if(line.length()>23)
match = line.substring(14,24);
for(i=0;i<ani_list.length;i++)
{
if(match.equals(ani_list[i].ani_name))
{
row.ani_id = ani_list[i].ani_id;
row.row_details = line;
row.create(null);
arr1[i][arr2[i]] = line;
arr2[i] = arr2[i]+1;
break;
}
else
continue;
}

}
}
// System.out.println("l"+arr.length);
for(i=0;i<arr.length;i++)
{
//System.out.println("\n"+arr[i]);

76
 }
// System.out.println("rohit");
// System.out.println(line);
/*for(i=0;arr1[90][i]!=null;i++)
System.out.println(arr1[90][i]);*/
}

catch(Exception e)
{
System.out.println(e.toString());
}

exception package

AOException

package planetwire.splitter.exception;

public class AOException extends Exception {

public AOException() {
}

public AOException(String msg){

super(msg);
}
}

EOException

package planetwire.splitter.exception;

public class EOException extends Exception {

public EOException(java.sql.SQLException ex) {

super(ex.getMessage());
}

public EOException(String msg) {

super(msg);
}
}

teo package

TEOani_row_table

package planetwire.splitter.teo;

77
import planetwire.splitter.util.DAOBase;

public class TEOani_row_table extends DAOBase {

public int id = NULL_INT;

public int ani_id = NULL_INT;
public String row_details = null;

public TEOani_row_table() {
}
}

TEOclient

package planetwire.splitter.teo;

import planetwire.splitter.util.DAOBase;

public class TEOclient extends DAOBase {

public int client_id = NULL_INT;

public String client_name = null;

public String company_name = "";
public String client_city = "";
public String country = "";
public String client_emailid ="";

public TEOclient() {
}
}

TEOclient_ani_details

package planetwire.splitter.teo;

import planetwire.splitter.util.DAOBase;

public class TEOclient_ani_details extends DAOBase {

public int id = NULL_INT;
public int client_id = NULL_INT;
public String ani_info = null;

public TEOclient_ani_details() {
}
}

TEOunique_ani_temp

78
package planetwire.splitter.teo;

import planetwire.splitter.util.DAOBase;

public class TEOunique_ani_temp extends DAOBase {

public int ani_id = NULL_INT;

public String ani_name = null;

public TEOunique_ani_temp() {
}
}

servlets package

AddUserServlet

package planetwire.splitter.servlets;

import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

//import java.util.*;
import planetwire.splitter.teo.TEOclient;
import planetwire.splitter.teo.TEOclient_ani_details;

import planetwire.splitter.util.DAOBase;

public class AddUserServlet extends HttpServlet{

public void init(ServletConfig config) throws ServletException{
super.init(config);
}

public void doPost(HttpServletRequest request, HttpServletResponse response) throws

ServletException,IOException{
HttpSession session = request.getSession(true);
String user = request.getParameter("name");
String city = request.getParameter("city");
String country = request.getParameter("country");
String anis[] = request.getParameterValues("anis");
String company = request.getParameter("company");

79
 String email = request.getParameter("email");

try
{
TEOclient customer = new TEOclient();
DAOBase[] ar = null;
String where = "";
String url = "";
String send = "";
where = "client_name='"+user+"'";
ar = customer.readAll(null,where);
if(ar.length!=0)
{
url="../jsp/add_client.jsp?
a=1&mode=1&user="+user+"&company="+company+"&anis="+anis+"&country="+country+"
&city="+city+"&email="+email;
send = response.encodeRedirectURL(url);
response.sendRedirect(send);
}
else
{
customer.client_name = user;
customer.client_city = city;
customer.country = country;
customer.company_name = company;
customer.client_emailid = email;
customer.create(null);

TEOclient[] client_list = null;

ar = customer.readAll(null,where);
client_list = new TEOclient[ar.length];
System.arraycopy(ar,0,client_list,0,ar.length);
TEOclient_ani_details ani = new TEOclient_ani_details();
for(int i=0;i<anis.length;i++)
{

ani.client_id = client_list[0].client_id;
ani.ani_info = anis[i];
ani.create(null);
}
url= "../jsp/add_client.jsp?a=1&mode=2";
send = response.encodeRedirectURL(url);
response.sendRedirect(send);
}
}catch(Exception e){
e.printStackTrace();
}
}
}

EditUserServlet

package planetwire.splitter.servlets;

import java.io.IOException;

80
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import planetwire.splitter.teo.TEOclient;
import planetwire.splitter.teo.TEOclient_ani_details;

import planetwire.splitter.util.DAOBase;

public class EditUserServlet extends HttpServlet{

public void init(ServletConfig config) throws ServletException{
super.init(config);
}

public void doPost(HttpServletRequest request, HttpServletResponse response) throws

ServletException,IOException{
HttpSession session = request.getSession(true);
String user = request.getParameter("name");
String client_id = request.getParameter("client");
String city = request.getParameter("city");
String country = request.getParameter("country");
String anis[] = request.getParameterValues("anis");
String company = request.getParameter("company");
String email = request.getParameter("email");
TEOclient customer = new TEOclient();
DAOBase[] ar = null;
String where = "";
String url = "";
String send = "";
int i=0;
// Test Array

where = "client_id!="+client_id+" and client_name='"+user+"'";

try
{

ar = customer.readAll(null,where);
if(ar.length!=0)
{
TEOclient[] client_list = new TEOclient[ar.length];
System.arraycopy(ar,0,client_list,0,ar.length);
for(i=0;i<client_list.length;i++)
{
if(user.equals(client_list[i].client_name))
{
url="../jsp/edit_client.jsp?
a=2&mode=1&user="+user+"&company="+company+"&country="+country+"&city="+city+"&e
mail="+email+"&client="+client_id;
for (i=0;i<anis.length;i++)
url+="&anis="+anis[i];

81
 send=response.encodeRedirectURL(url);
response.sendRedirect(send);
}
}
}
else
{
customer.client_name = user;
customer.client_city = city;
customer.country = country;
customer.company_name = company;
customer.client_emailid = email;
where = "client_id="+client_id;
String[] up_keys
={"client_name","client_city","country","company_name","client_emailid"};
String[] pri_keys ={""};
customer.update(null,up_keys,pri_keys,where);
TEOclient_ani_details ani = new TEOclient_ani_details();
ani.delete(null,where);
for(i=0;i<anis.length;i++)
{
ani.client_id = Integer.parseInt(client_id);
ani.ani_info = anis[i];
ani.create(null);
}
url= "../jsp/edit_client.jsp?a=2&mode=2";
send = response.encodeRedirectURL(url);
response.sendRedirect(send);
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}

Initservlet

package planetwire.splitter.servlets;

import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import planetwire.splitter.util.DAOBase;

public class InitServlet extends HttpServlet {

82
 public void init(ServletConfig config) throws ServletException{
super.init(config);
String dbUserName = getInitParameter("splitter_app.dbUser"),
dbPassword =
getInitParameter("splitter_app.dbPassword"),
dbURL =
getInitParameter("splitter_app.dbURL"),
driverClass =
getInitParameter("splitter_app.driverClass");

DAOBase.dbPassword = dbPassword;
DAOBase.dbURL = dbURL;
DAOBase.dbUser = dbUserName;
DAOBase.driverClass = driverClass;
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws

ServletException,IOException{

response.sendRedirect("../jsp/index.html");
}
}

SplitterServlet

package planetwire.splitter.servlets;

import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import java.util.*;
import com.oreilly.servlet.MultipartRequest;

import planetwire.splitter.eo.ControlEO;

public class SplitterServlet extends HttpServlet{

public void init(ServletConfig config) throws ServletException{
super.init(config);
}

public void doPost(HttpServletRequest req, HttpServletResponse res) throws

ServletException,IOException{
HttpSession session = req.getSession(true);

83
 try {
MultipartRequest multi =
new MultipartRequest(req, "C:/Jakarta/webapps/splitter_app/Uploads/", 5 *
1024 * 1024);

Enumeration params = multi.getParameterNames();

while (params.hasMoreElements())
{
String name = (String)params.nextElement();
String value = multi.getParameter(name);

Enumeration files = multi.getFileNames();

while (files.hasMoreElements()) {
String name = (String)files.nextElement();
String filename = multi.getFilesystemName(name);
String type = multi.getContentType(name);
String url="";
if(!(filename.equals(null)))
{
ControlEO control = new ControlEO();
control.getSplit("C:/Jakarta/webapps/splitter_app/Uploads/"+filename);
//if()
url="http://localhost:8080/splitter_app/jsp/ani_page.jsp?a=0";
res.encodeRedirectURL(url);
}
else
{
url="http://localhost:8080/splitter_app/jsp/index.html";
res.encodeRedirectURL(url);
}
res.sendRedirect(url);
}
}catch(Exception e){
e.printStackTrace();
}
}
}

2)JSP FILES

add_client

<%@ page import="planetwire.splitter.teo.TEOclient,

planetwire.splitter.teo.TEOclient_ani_details,
planetwire.splitter.util.DAOBase,
planetwire.splitter.teo.TEOunique_ani_temp" %>

<%
try
{
request.getSession(true);
String mode= request.getParameter("mode");

84
 int i=0,j=0;
String client_anis=null;
String name="";
String city="";
String company="";
String country="";
String email="";
String where ="";
TEOunique_ani_temp ani_temp = new TEOunique_ani_temp();
TEOunique_ani_temp[] ani_list = null;
DAOBase[] ar_ani =null;

// Added by Rohit for Giving list of ANI other than of user on 9 Apr
TEOclient temp_cl = new TEOclient();
TEOclient[] tmp_cl_list = null;
ar_ani = temp_cl.readAll(null,"");

if(ar_ani.length!=0)
{
tmp_cl_list = new TEOclient[ar_ani.length];
System.arraycopy(ar_ani,0,tmp_cl_list,0,ar_ani.length);

TEOclient_ani_details tmp_detail = new TEOclient_ani_details();

TEOclient_ani_details[] tmp_de_list = null;
String tmp_where="";
where = "ani_name NOT IN(";
for(i=0;i<tmp_cl_list.length;i++)
{
tmp_where = "client_id="+tmp_cl_list[i].client_id;
ar_ani = tmp_detail.readAll(null,tmp_where);
if(ar_ani.length!=0)
{
tmp_de_list = new TEOclient_ani_details[ar_ani.length];
System.arraycopy(ar_ani,0,tmp_de_list,0,ar_ani.length);

for(j=0;j<tmp_de_list.length;j++)
{
if(j==tmp_de_list.length-1 && i==tmp_cl_list.length-1)
{
where +=tmp_de_list[j].ani_info;
}
else
{
where +=tmp_de_list[j].ani_info+",";
}
}
}
}
where +=")";
}
// End of Added by Rohit for Giving list of ANI other than of user on 9 Apr

ar_ani = ani_temp.readAll(null,where);
if(ar_ani.length!=0)
{
ani_list = new TEOunique_ani_temp[ar_ani.length];

85
 System.arraycopy(ar_ani,0,ani_list,0,ar_ani.length);
}

%>

<script language="javascript">
function validate()
{
if(document.split.name.value=="")
{
alert("Enter the name of the Client");
document.split.name.focus();
return false;
}
else if(document.split.company.value=="")
{
alert("Enter the Company name of the Client");
document.split.company.focus();
return false;
}
else if(document.split.email.value=="")
{
alert("Enter the Email ID of the Client");
document.split.email.focus();
return false;
}
else if (checkemail()==false)
{

document.split.email.focus();
document.split.email.select();
return false;
}
else if(document.split.city.value=="")
{
alert("Enter the City name of the Client");
document.split.city.focus();
return false;
}
else if(document.split.country.value=="")
{
alert("Enter the Country name of the Client");
document.split.country.focus();
return false;
}
else if(document.split.anis.value==null || document.split.anis.value=="")
{
alert("We have to select atleast 1 ANI for the Client");
document.split.anis.focus();
return false;
}
else
{
document.split.action="http://localhost:8080/splitter_app/servlet/addClient";
document.split.submit();

86
 }

function checkemail()
{
var testresults;
var str=document.split.email.value;
var filter=/^([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$/i
if (filter.test(str))
testresults=true;
else
{
alert("Please enter a valid email address!");
testresults=false;
}
return (testresults)
}

</script>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400"
style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="top">
<table bgcolor=’#0000FF’>
<tr>
<td>

<table border="0" cellpadding="0" cellspacing="0" width="586" height="456"

bgcolor="#B1E9F4">

<tr>

<td width="58" height="23"></td>

<td width="479" height="23" colspan="3"></td>

87
 <td width="43" height="23">&nbsp;</td>
</tr>
<tr>
<td width="58" height="33"></td>
</center>
<td width="475" height="33" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" colspan="3" >
<p align="center"><b><font color="#000099" size="4" face="Verdana">ADD
CLIENT PAGE</font></b></p>
</td>
<td width="43" height="33">&nbsp;</td>
</tr>
<center>
<%
if(mode.equals("1"))
{
name = request.getParameter("user");
city = request.getParameter("city");
company = request.getParameter("company");
country = request.getParameter("country");
email = request.getParameter("email");
%>
<tr>
<td width="58" height="54"></td>
<td width="53" height="54"></td>
<td colspan='2' align='center' height="54"><font color='#FF0000' face='Verdana'>
<b>
The Client Name we entered already exists<br> Please try again with new Client
Name</b></font></td>
<td width="43" height="54">&nbsp;</td>
<%
}
else if(mode.equals("2"))
{
%>
<tr>
<td width="58" height="54"></td>
<td width="53" height="54"></td>
<td colspan='2' align='center' height="54"><font color='#FF0000' face='Verdana'>
<b>
The Client Creation process was done Successfully<br> To Create other Client fill all
details below</b></font></td>
<td width="43" height="54">&nbsp;</td>
<%
}
%>

<tr>
<form name="split" method="post">
<td width="58" height="76"></td>
<td width="53" height="76" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="76" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Name

88
 of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="76" valign="bottom"><input type="text" name="name" size="20"
tabindex="1" value="<%=name%>"> </td>
<td width="43" height="76">&nbsp;</td>
</tr>
<tr>
<td width="58" height="33"></td>
<td width="53" height="33" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="33" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Company
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="33" valign="bottom"><input type="text" name="company"
size="20" tabindex="2" value="<%=company%>"> </td>
<td width="43" height="33">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp; </td>
<td width="197" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Email
ID of Client :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="email" size="20"
tabindex="3" value="<%=email%>"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="31"></td>
<td width="53" height="31" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="31" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">City
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="31" valign="bottom"><input type="text" name="city" size="20"
tabindex="4" value="<%=city%>"> </td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp;<font size="3" face="Verdana">
</font> </td>
<td width="197" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Country
of Client :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="country"
size="20" tabindex="5" value="<%=country%>"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="91"></td>
<td width="53" height="91" valign="bottom">&nbsp; </td>
<td width="197" height="91" valign="middle" align="right"><font size="3" face="Verdana"
color="#0000FF">Add
ANIs to Client :&nbsp;</font> </td>

89
 <td width="232" height="91" valign="bottom"><select name='anis' multiple size='4'
tabindex="6">
<%
if(ani_list!=null)
{
for(i=0;i<ani_list.length;i++)
{
%>
<option value="<%=ani_list[i].ani_name%>">
<%=ani_list[i].ani_name%> </option>
<%
}
}
%>
</select>
<br><font size='1' face='Verdana' color='#000000'> Use CTRL key to select Multiple
ANI</font>
</td>

<td width="43" height="91">&nbsp;</td>

</tr>
<tr>
<td width="58" height="55"></td>
<td width="251" height="55" colspan="2">
<p align="center">
&nbsp;</p>
</td>
<td width="232" height="55">
<input type="button" name="submit_button" value=" Submit " tabindex="7"
onclick="validate()">&nbsp;&nbsp;&nbsp; <input type="reset" value=" Reset " name="B2"
tabindex="8">
</td>
<td width="43" height="55">&nbsp;</td>
</form>
</tr>

</table>
</center>
</td></tr>
</table>
</td></tr>
</table>
<%
}// try
catch(Exception e)
{
System.out.println(e.toString());
}
%>
</body>
</html>

edit_client

<%@ page import="planetwire.splitter.teo.TEOclient,

planetwire.splitter.teo.TEOclient_ani_details,

90
 planetwire.splitter.util.DAOBase,
java.util.*,
planetwire.splitter.teo.TEOunique_ani_temp" %>

<%
try
{
request.getSession(true);
TEOclient customer = new TEOclient();
TEOclient customer1 = new TEOclient();
TEOclient_ani_details ani = new TEOclient_ani_details();
TEOunique_ani_temp ani_temp = new TEOunique_ani_temp();
TEOunique_ani_temp[] ani_list = null;
TEOclient_ani_details[] client_ani = null;
TEOclient[] client_list = null;
DAOBase[] ar_main = null;
DAOBase[] ar = null;
DAOBase[] ar_ani = null;
ar_main = customer.readAll(null,"");
if(ar_main.length!=0)
{
client_list = new TEOclient[ar_main.length];
System.arraycopy(ar_main,0,client_list,0,ar_main.length);
}

String mode= request.getParameter("mode");

int i=0,j=0;
String name="";
String[] client_anis=null;
String email="";
String company="";
String city="";
String country="";
String where="";
String client="";
if(mode.equals("0") || mode.equals("2"))
{
client="";
}
if(mode.equals("3") || mode.equals("1"))
{
client = request.getParameter("client");

if(mode.equals("3"))
{

where = "client_id="+Integer.parseInt(client);
ar = customer.readAll(null,where);
customer1 = (TEOclient) ar[0];
name = customer1.client_name;
email = customer1.client_emailid;
company = customer1.company_name;
country = customer1.country;
city = customer1.client_city;
ar = ani.readAll(null,where);
if(ar.length!=0)

91
 {
client_ani = new TEOclient_ani_details[ar.length];
System.arraycopy(ar,0,client_ani,0,ar.length);
client_anis = new String[client_ani.length];
for(i=0;i<client_ani.length;i++)
client_anis[i] = client_ani[i].ani_info;
}
}
else if(mode.equals("1"))
{
name = request.getParameter("user");
email = request.getParameter("email");
country = request.getParameter("country");
city = request.getParameter("city");
company = request.getParameter("company");
//client_anis = new String[request.getParameterValues("anis").length];
String client_anis1[] = request.getParameterValues("anis");

client_anis = new String[client_anis1.length];

for(i=0;i<client_anis1.length;i++)
{
client_anis[i] = client_anis1[i];

}
}

// Added by Rohit for Giving list of ANI other than of user on 9 Apr
TEOclient temp_cl = new TEOclient();
TEOclient[] tmp_cl_list = null;
if(client.equals(""))
where="";
else
where ="client_id!="+client;
ar_ani = temp_cl.readAll(null,where);

if(ar_ani.length!=0)
{
tmp_cl_list = new TEOclient[ar_ani.length];
System.arraycopy(ar_ani,0,tmp_cl_list,0,ar_ani.length);

TEOclient_ani_details tmp_detail = new TEOclient_ani_details();

TEOclient_ani_details[] tmp_de_list = null;
String tmp_where="";
where = "ani_name NOT IN(";
for(i=0;i<tmp_cl_list.length;i++)
{
tmp_where = "client_id="+tmp_cl_list[i].client_id;
ar_ani = tmp_detail.readAll(null,tmp_where);
if(ar_ani.length!=0)
{
tmp_de_list = new TEOclient_ani_details[ar_ani.length];
System.arraycopy(ar_ani,0,tmp_de_list,0,ar_ani.length);

92
 for(j=0;j<tmp_de_list.length;j++)
{
if(j==tmp_de_list.length-1 && i==tmp_cl_list.length-1)
{
where +=tmp_de_list[j].ani_info;
}
else
{
where +=tmp_de_list[j].ani_info+",";
}
}
}
}
where +=")";
}
// End of Added by Rohit for Giving list of ANI other than of user on 9 Apr

ar_ani = ani_temp.readAll(null,where);
if(ar_ani.length!=0)
{
ani_list = new TEOunique_ani_temp[ar_ani.length];
System.arraycopy(ar_ani,0,ani_list,0,ar_ani.length);
}

%>

<script language="javascript">
function validate()
{
if(document.split.name.value=="")
{
alert("Enter the name of the Client");
document.split.name.focus();
return false;
}
else if(document.split.company.value=="")
{
alert("Enter the Company name of the Client");
document.split.company.focus();
return false;
}
else if(document.split.email.value=="")
{
alert("Enter the Email ID of the Client");
document.split.email.focus();
return false;
}
else if (checkemail()==false)
{

document.split.email.focus();
document.split.email.select();
return false;
}
else if(document.split.city.value=="")
{

93
 alert("Enter the City name of the Client");
document.split.city.focus();
return false;
}
else if(document.split.country.value=="")
{
alert("Enter the Country name of the Client");
document.split.country.focus();
return false;
}
else if(document.split.anis.value==null || document.split.anis.value=="")
{
alert("We have to select atleast 1 ANI for the Client");
document.split.anis.focus();
return false;
}
else
{
document.split.action="http://localhost:8080/splitter_app/servlet/editClient";
document.split.submit();

function checkemail()
{
var testresults;
var str=document.split.email.value;
var filter=/^([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$/i
if (filter.test(str))
testresults=true;
else
{
alert("Please enter a valid email address!");
testresults=false;
}
return (testresults)
}

</script>

<script language="javascript">
function test_select()
{
/*var in = document.client_sel.client.options.selectedIndex;
var value_sel = document.client_sel.client.options[in].value;*/
if(document.client_sel.client.value=="")
{
document.client_sel.action= "edit_client.jsp?a=2&mode=0";
document.client_sel.submit();
}
else
{
document.client_sel.action= "edit_client.jsp?a=2&mode=3";
document.client_sel.submit();

94
 }
}

</script>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400"
style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="top">
<table bgcolor=’#0000FF’>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="586" height="24"
bgcolor="#B1E9F4">

<tr>

<td width="58" height="23"></td>

<td width="479" height="23" colspan="3"></td>

<td width="43" height="23">&nbsp;</td>

</tr>
<tr>
<td width="58" height="33"></td>
</center>
<td width="475" height="33" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" colspan="3" >
<p align="center"><b><font color="#000099" size="4" face="Verdana">EDIT
CLIENT PAGE</font></b></p>
</td>
<td width="43" height="33">&nbsp;</td>
</tr>
<center>
<tr>
<form name="client_sel" method='post'>

95
 <td width="58" height="74"></td>
<td width="250" height="74" valign="bottom" colspan="2" align="right">&nbsp;<font
size="3" face="Verdana">&nbsp;
</font>
<p><font size="3" face="Verdana" color="#0000FF">Select one Client to
view :&nbsp;</font></p>
</td>
<td width="232" height="74" valign="bottom">

<select name="client" size="1" tabindex="1" onchange="test_select()">

<option <% if(client.equals("")){ %>selected <% }else{ %> ''<% } %> value=''>Select
Client Name</option>
<%
if((client_list.length!=0))
{
int client_id = 0;
if(mode.equals("1") || mode.equals("3"))
{
client_id = Integer.parseInt(client);

for(i=0;i<client_list.length;i++)
{
%>
<option value="<%=client_list[i].client_id%>" <% if(client_list[i].client_id==client_id){ %>
selected<%} else{ %>''<% } %> > <%=client_list[i].client_name%> </option>
<%
}
}
else
{
for(i=0;i<client_list.length;i++)
{
%>
<option value="<%=client_list[i].client_id%>"> <%=client_list[i].client_name%> </option>
<%
}
}
}
%>
</select>

</td>
<td width="43" height="74">&nbsp;</td>
</form>
</tr>
<%
if(mode.equals("1"))
{
%>
<tr>
<td width="58" height="54"></td>
<td width="53" height="54"></td>
<td colspan='2' align='center' height="54"><font color='#FF0000' face='Verdana'>
<b>
The Client Name we entered already exists<br> Please try again with new Client
Name</b></font></td>

96
 <td width="43" height="54">&nbsp;</td>
<%
}
else if(mode.equals("2"))
{
%>
<tr>
<td width="58" height="54"></td>
<td width="53" height="54"></td>
<td colspan='2' align='center' height="54"><font color='#FF0000' face='Verdana'>
<b>
The Edit Client process was done Successfully<br> To Edit other Client Select Client
Name from Top</b></font></td>
<td width="43" height="54">&nbsp;</td>
<%
}
%>

<tr>
<form name="split" method="post">
<input type="hidden" name="client" value="<%=client%>">
<td width="58" height="49"></td>
<td width="53" height="49" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="49" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Name
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="49" valign="bottom"><input type="text" name="name" size="20"
tabindex="1" value="<%=name%>"> </td>
<td width="43" height="49">&nbsp;</td>
</tr>
<tr>
<td width="58" height="33"></td>
<td width="53" height="33" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="33" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Company
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="33" valign="bottom"><input type="text" name="company"
size="20" tabindex="2" value="<%=company%>"> </td>
<td width="43" height="33">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp; </td>
<td width="197" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Email
ID of Client :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="email" size="20"
tabindex="3" value="<%=email%>"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="31"></td>

97
 <td width="53" height="31" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="31" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">City
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="31" valign="bottom"><input type="text" name="city" size="20"
tabindex="4" value="<%=city%>"> </td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp;<font size="3" face="Verdana">
</font> </td>
<td width="197" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Country
of Client :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="country"
size="20" tabindex="5" value="<%=country%>"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<%
if(mode.equals("3") || mode.equals("1"))
{
%>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp; </td>
<td width="197" height="30" valign="middle" align="right"><font size="3" face="Verdana"
color="#0000FF">List
of ANIs :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><select name='anis' multiple size='4'
tabindex="6">
<%
if(ani_list!=null)
{
for(i=0;i<ani_list.length;i++)
{
%>
<option value="<%=ani_list[i].ani_name%>" <% if(client_anis!=null)
{ for(j=0;j<client_anis.length;j++){ if(client_anis[j].equals(ani_list[i].ani_name)){%>selected<%
break;}else
continue;}}%>>
<%=ani_list[i].ani_name%> </option>
<%
}
}
%>
</select>
<br><font size='1' face='Verdana' color='#000000'> Use CTRL key to select Multiple
ANI</font>
</td>
<td width="43" height="30">&nbsp;</td>
</tr>
<%
}

98
%>
<tr>
<td width="58" height="55"></td>
<td width="251" height="55" colspan="2">
<p align="center">
&nbsp;</p>
</td>
<td width="232" height="55">
<input type="button" name="submit_button" value=" Update " tabindex="7"
onclick="validate()">&nbsp;&nbsp;&nbsp; <input type="reset" value=" Reset " name="B2"
tabindex="8">
</td>
<td width="43" height="55">&nbsp;</td>
</form>
</tr>

</table>
</center>
</td></tr>
</table>
</td></tr>
</table>
</div>
<%
}//try
catch(Exception e)
{
System.out.println(e.toString());
}
%>
</body>

</html>

view_client

<%@ page import="planetwire.splitter.teo.TEOclient,

planetwire.splitter.teo.TEOclient_ani_details,
planetwire.splitter.util.DAOBase,
java.util.*,
planetwire.splitter.teo.TEOunique_ani_temp" %>

<%
try
{
request.getSession(true);
TEOclient customer = new TEOclient();
TEOclient customer1 = new TEOclient();
TEOclient_ani_details ani = new TEOclient_ani_details();
TEOclient_ani_details[] client_ani = null;
TEOclient[] client_list = null;
DAOBase[] ar_main = null;
DAOBase[] ar = null;
DAOBase[] ar_ani = null;
ar_main = customer.readAll(null,"");
if(ar_main.length!=0)

99
 {
client_list = new TEOclient[ar_main.length];
System.arraycopy(ar_main,0,client_list,0,ar_main.length);
}
String mode= request.getParameter("mode");
int i=0;
String name="";
String[] client_anis=null;
String email="";
String company="";
String city="";
String country="";
String where="";
String client="";
if(mode.equals("0"))
{
client="";
}
if(mode.equals("1"))
{
client = request.getParameter("client");

if(mode.equals("1"))
{

where = "client_id="+Integer.parseInt(client);
ar = customer.readAll(null,where);
customer1 = (TEOclient) ar[0];
name = customer1.client_name;
email = customer1.client_emailid;
company = customer1.company_name;
country = customer1.country;
city = customer1.client_city;
ar = ani.readAll(null,where);
if(ar.length!=0)
{
client_ani = new TEOclient_ani_details[ar.length];
System.arraycopy(ar,0,client_ani,0,ar.length);
client_anis = new String[client_ani.length];
for(i=0;i<client_ani.length;i++)
client_anis[i] = client_ani[i].ani_info;
}
}
}
%>
<script language="javascript">
function test_select()
{

if(document.client_sel.client.value=="")
{
document.client_sel.action= "view_client.jsp?a=3&mode=0";
document.client_sel.submit();
}
else
{

100
 document.client_sel.action= "view_client.jsp?a=3&mode=1";
document.client_sel.submit();

}
}

</script>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400"
style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="top">
<table bgcolor=’#0000FF’>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="586" height="20"
bgcolor="#B1E9F4">

<tr>

<td width="58" height="23"></td>

<td width="479" height="23" colspan="3"></td>

<td width="43" height="23">&nbsp;</td>

</tr>
<tr>
<td width="58" height="33"></td>
</center>
<td width="475" height="33" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" colspan="3" >
<p align="center"><b><font color="#000099" size="4" face="Verdana">VIEW CLIENT
PAGE</font></b></p>
</td>
<td width="43" height="33">&nbsp;</td>

101
 </tr>
<center>
<tr>
<form name="client_sel" method='post'>
<td width="58" height="74"></td>
<td width="250" height="74" valign="bottom" colspan="2" align="right">&nbsp;<font
size="3" face="Verdana">&nbsp;
</font>
<p><font size="3" face="Verdana" color="#0000FF">Select one Client to
view :&nbsp;</font></p>
</td>
<td width="232" height="74" valign="bottom">

<select name="client" size="1" tabindex="1" onchange="test_select()">

<option <% if(client.equals("")){ %>selected <% }else{ %> ''<% } %> value=''>Select
Client Name</option>
<%
if((client_list.length!=0))
{
int client_id = 0;
if(mode.equals("1"))
{
client_id = Integer.parseInt(client);

for(i=0;i<client_list.length;i++)
{
%>
<option value="<%=client_list[i].client_id%>" <% if(client_list[i].client_id==client_id){ %>
selected<%} else{ %>''<% } %> > <%=client_list[i].client_name%> </option>
<%
}
}
else
{
for(i=0;i<client_list.length;i++)
{
%>
<option value="<%=client_list[i].client_id%>"> <%=client_list[i].client_name%> </option>
<%
}
}
}
%>
</select>

</td>
<td width="43" height="74">&nbsp;</td>
</form>
</tr>
<tr>
<td width="58" height="57"></td>
<td width="52" height="49" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="198" height="57" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Name

102
 of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="57" valign="bottom"><input type="text" name="name" size="20"
tabindex="1" value="<%=name%>" readonly="true"> </td>
<td width="43" height="57">&nbsp;</td>
</tr>
<tr>
<td width="58" height="33"></td>
<td width="52" height="33" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="198" height="33" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Company
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="33" valign="bottom"><input type="text" name="company"
size="20" tabindex="2" value="<%=company%>" readonly="true"> </td>
<td width="43" height="33">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="52" height="30" valign="bottom">&nbsp; </td>
<td width="198" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">email
ID of Client :&nbsp;</font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="email" size="20"
tabindex="3" value="<%=email%>" readonly="true"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="31"></td>
<td width="52" height="31" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="198" height="31" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">City
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="31" valign="bottom"><input type="text" name="city" size="20"
tabindex="4" value="<%=city%>" readonly="true"> </td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="52" height="30" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="198" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Country
of Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="country"
size="20" tabindex="5" value="<%=country%>" readonly="true"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<%
if(mode.equals("1"))
{
%>
<tr>

103
 <td width="58" height="30"></td>
<td width="52" height="30" valign="bottom">&nbsp;<font size="3" face="Verdana">
</font> </td>
<td width="198" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">List
of ANIs :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><select name='anis' multiple size='4'
tabindex="6">
<%
if(client_anis!=null)
{
for(i=0;i<client_anis.length;i++)
{
%>
<option> <%=client_anis[i]%> </option>
<%
}
}
%>
</select></td>
<td width="43" height="30">&nbsp;</td>
</tr>
<%
}
%>
<tr>
<td width="58" height="41"></td>
<td width="251" height="41" colspan="2">
<p align="center">
&nbsp;</p>
</td>
<td width="232" height="41">
&nbsp;
</td>
<td width="43" height="41">&nbsp;</td>

</tr>

</table>
</center>
</td></tr>
</table>
</td></tr>
</table>

</div>
<%
}//try
catch(Exception e)
{
System.out.println(e.toString());
}
%>

</body>

104
</html>

ani_page

<%@ page import="planetwire.splitter.teo.TEOunique_ani_temp,

planetwire.splitter.util.DAOBase" %>

<%
try
{
DAOBase[] ar = null;
TEOunique_ani_temp[] ani_list = null;
TEOunique_ani_temp anis = new TEOunique_ani_temp();
ar = anis.readAll(null,"");
%>
<html>
<head>
<title>DUF Result Page</title>
</head>
<body>
<center>
<br><br>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400" style="border-
collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="middle">
<table bgcolor=’#0000FF’>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="586" height="400"
bgcolor="#B1E9F4">
<tr><td height='10'>&nbsp;</td></tr>
<tr>
<td valign='middle' align='center'>
<b>These are the result ANI from the File :</b>
<tr><td height='10'>&nbsp;</td></tr>
</td></tr>
<tr>
<td align='center'>
<select name='anies' size='20' multiple>
<option> The List of ANIS </option>
<%
if(ar.length!=0)
{
ani_list = new TEOunique_ani_temp[ar.length];
System.arraycopy(ar,0,ani_list,0,ar.length);
for(int i=0;i<ani_list.length;i++)
{
%>
<option value='<%= ani_list[i].ani_id%>'><%= ani_list[i].ani_name%></option>
<%

105
}
%>
</select><br>
<a href='http://localhost:8080/splitter_app/jsp/index.html'>Go Back to Index Page</a>
<%
}
}
catch(Exception e)
{
System.out.println(e.toString());
}
%>
</td></tr>
</table>
</td></tr>
</table>
</td></tr>
</table>

</body>
</html>

process_file

<%@ page import="planetwire.splitter.teo.TEOclient,

planetwire.splitter.util.DAOBase,
java.io.*,
javax.activation.*,
javax.mail.*,
javax.mail.Message,
javax.mail.internet.InternetAddress,
javax.mail.Message.RecipientType,
javax.mail.internet.*,
java.util.*,
planetwire.splitter.teo.TEOunique_ani_temp,
planetwire.splitter.teo.TEOclient_ani_details,
planetwire.splitter.teo.TEOani_row_table" %>

<%
try
{
request.getSession(true);
String mode= request.getParameter("mode");
String client= request.getParameter("client");
int i=0;
String filename = request.getParameter("filename");
String tempname = filename;
String client_anis=null;
String name="";
String city="";
String company="";
String country="";
String email="";
TEOclient_ani_details ani = new TEOclient_ani_details();

106
 TEOclient_ani_details[] client_ani_list = null;
DAOBase[] ar = null;
int client_id = Integer.parseInt(client);
filename="c:/jakarta/webapps/splitter_app/readable/"+filename+".txt";
FileWriter buf = new FileWriter(filename);
String where ="client_id="+client_id;
ar = ani.readAll(null,where);
if(ar.length!=0)
{
client_ani_list = new TEOclient_ani_details[ar.length];
System.arraycopy(ar,0,client_ani_list,0,ar.length);
}
TEOunique_ani_temp u_temp = new TEOunique_ani_temp();
TEOani_row_table row_t = new TEOani_row_table();
TEOani_row_table[] row_list = null;
for(i=0;i<client_ani_list.length;i++)
{
where = "ani_name='"+client_ani_list[i].ani_info+"'";
ar = u_temp.readAll(null,where);
u_temp = (TEOunique_ani_temp) ar[0];
where = "ani_id="+u_temp.ani_id;
ar = row_t.readAll(null,where);
row_list = new TEOani_row_table[ar.length];
System.arraycopy(ar,0,row_list,0,ar.length);
for(int j=0;j<row_list.length;j++)
{
buf.write(row_list[j].row_details+"\n");
}

}
System.out.println("test "+filename);
buf.close();
FileDataSource fds = new FileDataSource(filename);
File f=fds.getFile();
FileInputStream fs = new FileInputStream(f);
InputStreamReader file = new InputStreamReader(fs);
BufferedReader b = new BufferedReader(file);
String line="";
String output="";

// Mail Sending

String host="210.7.88.226";
Properties props =System.getProperties();
props.put("mail.host",host);
props.put("mail.transport.protocol","smtp");

TEOclient customer = new TEOclient();

where = "client_id="+client;
ar = customer.readAll(null,where);
customer = (TEOclient) ar[0];

String Subject="ANI Details from DUF Application";

String Message ="Hello "+ customer.client_name +"\r\n The file Containing ANIs are
attached";

107
 Session mailSession= Session.getDefaultInstance(props,null);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress("[email protected]"));
InternetAddress[] address = {new InternetAddress(customer.client_emailid)};

msg.setRecipients(RecipientType.TO,address);
msg.setSubject(Subject);

// Attaching File

Multipart multiPart = new MimeMultipart();

MimeBodyPart bodyPart = new MimeBodyPart();

FileDataSource fds1 = new FileDataSource(filename);

bodyPart.setDataHandler(new DataHandler(fds));
bodyPart.setFileName(tempname+".txt");
multiPart.addBodyPart(bodyPart);
MimeBodyPart main = new MimeBodyPart();
main.setText(Message);
multiPart.addBodyPart(main);

msg.setContent(multiPart);
Transport.send(msg);

// Mail Sending ended

while ( (line = b.readLine()) != null )
output += line+"\n";
%>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400"
style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="top">
<table bgcolor=’#0000FF’ height="396">

108
 <tr>
<td height="392">
<table border="0" cellpadding="0" cellspacing="0" width="586" height="410"
bgcolor="#B1E9F4">

<tr>
<td width="58" height="58"></td>
</center>
<td width="475" height="58" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" colspan="3" >
<p align="center"><b><font color="#000099" size="4" face="Verdana">SPLIT
FILE PAGE</font></b></p>
</td>
<td width="43" height="58">&nbsp;</td>
</tr>
<center>

<tr>

</tr>
<tr>
<td width="58" height="31">&nbsp;</td>
<td width="53" height="31" valign="bottom">&nbsp; </td>
<td width="197" height="31" valign="bottom" align="right">&nbsp;
<textarea name='area' rows='20' cols='50'><%=output%> </textarea></td>
<td width="232" height="31" valign="bottom">&nbsp;</td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="31"></td>
<td width="482" height="31" valign="bottom" colspan="3">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font>
<p align="center"><font size="3" face="Verdana" color="#0000FF"><b>The
File split Process is completed</b> &nbsp;
&nbsp; <br>The mail is sent with Split DUF attachment.<br>
To Go on Split page <a href="split_file.jsp?a=4&mode=0">Click Here</a> </font></p>
</td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="482" height="30" valign="bottom" colspan="3">&nbsp;<font size="3"
face="Verdana">
</font> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="1">&nbsp;</td>
<td width="483" height="1" colspan="3">
&nbsp;
</td>
<td width="43" height="1">&nbsp;</td>
</tr>

<tr>

109
 <td width="58" height="104"></td>
<td width="251" height="104" colspan="2">
<p align="center">
&nbsp;</p>
</td>
<td width="232" height="104">
&nbsp;&nbsp;&nbsp;
</td>
<td width="43" height="104">&nbsp;</td>

</tr>

</table>
</center>
</td></tr>
</table>
</td></tr>
</table>
<%
}// try
catch(Exception e)
{
System.out.println(e.toString());
}
%>
</body>
</html>

split_file

<%@ page import="planetwire.splitter.teo.TEOclient,

planetwire.splitter.util.DAOBase,
planetwire.splitter.teo.TEOunique_ani_temp" %>

<%
try
{
request.getSession(true);
String mode= request.getParameter("mode");
int i=0;
String client_anis=null;
String name="";
String city="";
String company="";
String country="";
String email="";
TEOclient client = new TEOclient();
TEOclient[] client_list = null;
DAOBase[] ar = null;
ar = client.readAll(null,"");
if(ar.length!=0)
{
client_list = new TEOclient[ar.length];

110
 System.arraycopy(ar,0,client_list,0,ar.length);
}

%>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table border="0" cellspacing="0" width="780" id="AutoNumber1" height="400"
style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<tr>
<td width="21%" height="400" bgcolor="#B1E9F4" rowspan="3" valign="top">
&nbsp;&nbsp;
<br><br>
<jsp:include page="leftbar.jsp" flush="true"/>
</td>
<td width="79%" height="338" align="center" valign="top">
<table bgcolor=’#0000FF’ height="396">
<tr>
<td height="392">
<table border="0" cellpadding="0" cellspacing="0" width="586" height="410"
bgcolor="#B1E9F4">

<tr>
<td width="58" height="58"></td>
</center>
<td width="475" height="58" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" colspan="3" >
<p align="center"><b><font color="#000099" size="4" face="Verdana">SPLIT
FILE PAGE</font></b></p>
</td>
<td width="43" height="58">&nbsp;</td>
</tr>
<center>

<tr>
<form name='split' method='post' action="process_file.jsp?a=0">
</tr>
<tr>
<td width="58" height="31">&nbsp;</td>
<td width="53" height="31" valign="bottom">&nbsp; </td>
<td width="197" height="31" valign="bottom" align="right">&nbsp; </td>
<td width="232" height="31" valign="bottom">&nbsp;</td>
<td width="43" height="31">&nbsp;</td>

111
 </tr>
<tr>
<td width="58" height="31"></td>
<td width="53" height="31" valign="bottom">&nbsp;<font size="3"
face="Verdana">&nbsp;
</font> </td>
<td width="197" height="31" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Select
Client :&nbsp;&nbsp;</font> </td>
<td width="232" height="31" valign="bottom"><select name='client' size='1'>
<option value=''> Select a Client </option>
<%
for(i=0;i<client_list.length;i++)
{
%>
<option value="<%=client_list[i].client_id%>"> <%=client_list[i].client_name%>
</option>
<%
}
%>
</select></td>
<td width="43" height="31">&nbsp;</td>
</tr>
<tr>
<td width="58" height="30"></td>
<td width="53" height="30" valign="bottom">&nbsp;<font size="3" face="Verdana">
</font> </td>
<td width="197" height="30" valign="bottom" align="right"><font size="3" face="Verdana"
color="#0000FF">Write
File Name :&nbsp; </font> </td>
<td width="232" height="30" valign="bottom"><input type="text" name="filename"
size="20" tabindex="5"> </td>
<td width="43" height="30">&nbsp;</td>
</tr>
<tr>
<td width="58" height="1">&nbsp;</td>
<td width="251" height="1" colspan="2">
&nbsp;
</td>
<td width="232" height="1">
<input type='submit' name='submit' value=' Split ' tabindex="7">
</td>
<td width="43" height="1">&nbsp;</td>
</tr>

<tr>
<td width="58" height="104"></td>
<td width="251" height="104" colspan="2">
<p align="center">
&nbsp;</p>
</td>
<td width="232" height="104">
&nbsp;&nbsp;&nbsp;
</td>
<td width="43" height="104">&nbsp;</td>
</form>

112
 </tr>

</table>
</center>
</td></tr>
</table>
</td></tr>
</table>
<%
}// try
catch(Exception e)
{
System.out.println(e.toString());
}
%>
</body>
</html>

Leftbar

<%
try
{

String url="";
%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title></title>
</head>

<body topmargin="0" leftmargin="0">

<table border="0" cellspacing="0" width="100%" id="AutoNumber1" cellpadding="0">

<tr>
<td width="99%" height="100%" bgcolor="#B1E9F4">
<p align="center"><font face="Verdana" size="2">
<%
String c="";

c=request.getParameter("a");

if(c.equals("1"))
{
%>
</font>

<b>
<font face="Verdana" size="3"
color="#0000FF">Create&nbsp;&nbsp;Client</font></b><font face="Verdana" size="2"><br>
<br>

113
<br>
<br>
<%
url = response.encodeRedirectURL("edit_client.jsp?a=2&mode=0");
%>
<a href="<%=url%>">
Edit Client</a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("view_client.jsp?a=3&mode=0");
%>
<a href="<%=url%>">

View Clients</a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("split_file.jsp?a=4&mode=0");
%>
<a href="<%=url%>">

Split File</a><br>

<%
}

else if(c.equals("2"))
{
%>

<b>
<font face="Verdana" size="3" color="#0000FF">Edit&nbsp;&nbsp;Client</font></b><br>
<br>
<br>
<br>
<%
url = response.encodeRedirectURL("add_client.jsp?a=1&mode=0");
%>
<a href="<%=url%>">
<font face="Verdana" size="2">
Create Client</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("view_client.jsp?a=3&mode=0");
%>
<a href="<%=url%>">
<font face="Verdana" size="2">
View Clients</font></a><br>

<br>
<br>
<%
url = response.encodeRedirectURL("split_file.jsp?a=4&mode=0");
%>

114
 <a href="<%=url%>">

Split File</a><br>

<%
}

else if(c.equals("3"))
{
%>

<b>
<font face="Verdana" size="3"
color="#0000FF">View&nbsp;&nbsp;Clients</font></b><br>
<br>
<br>
<br>
<%
url = response.encodeRedirectURL("add_client.jsp?a=1&mode=0");
%>
<a href="<%=url%>">
<font face="Verdana" size="2">
Create Client</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("edit_client.jsp?a=2&mode=0");
%>
<a href="<%=url%>"><font face="Verdana" size="2">
Edit Client</font></a><br>

<br>
<br>
<%

url = response.encodeRedirectURL("split_file.jsp?a=4&mode=0");
%>
<a href="<%=url%>">

Split File</a><br>

<%
}

else if(c.equals("4"))
{
%>

<b>
<font face="Verdana" size="3" color="#0000FF">Split&nbsp;&nbsp;File</font></b><br>
<br>
<br>
<br>
<%
url = response.encodeRedirectURL("add_client.jsp?a=1&mode=0");

115
 %>
<a href="<%=url%>">
<font face="Verdana" size="2">
Create Client</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("edit_client.jsp?a=2&mode=0");
%>
<a href="<%=url%>"><font face="Verdana" size="2">
Edit Client</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("view_client.jsp?a=3&mode=0");
%>
<a href="<%=url%>">

<font face="Verdana" size="2">

View Clients</font></a><br>

<%
}

else
{
%>
<font face="Verdana" color="#0000FF" size="6"> D U F App</font><br><br><br>
<%
url = response.encodeRedirectURL("add_client.jsp?a=1&mode=0");
%>
<a href="<%=url%>">
<font face="Verdana" size="2">
Create Client
</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("edit_client.jsp?a=2&mode=0");
%>
<a href="<%=url%>">
<font face="Verdana" size="2">
Edit Client</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("view_client.jsp?a=3&mode=0");
%>
<a href="<%=url%>">
<font size="2" face="Verdana">
View Clients</font></a><br>
<br>
<br>
<%
url = response.encodeRedirectURL("split_file.jsp?a=4&mode=0");
%>

116
 <a href="<%=url%>">

Split File</a><br>
<%
}
%>

</td>
<td width="1%" height="100%">&nbsp;</td>
</tr>
</table>
<%

}
catch(Exception e)
{
System.out.println(e.toString());
}
%>

</body>

</html>

3) HTML FILE

Index

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>DUF Application</title>
</head>

<body>

<p align="center">&nbsp;</p>
<div align="center">
<center>
<table bgcolor=’#0000FF’>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="586" height="187"
bgcolor="#B1E9F4">

<tr>
<form name='split' method='post' ENCTYPE="multipart/form-data"
action="http://localhost:8080/splitter_app/servlet/split">
<td width="58" height="23"></td>
<td width="479" height="23"></td>

117
 <td width="43" height="23">&nbsp;</td>
</tr>
<tr>
<td width="58" height="33"></td>
</center>
<td width="475" height="33" style="border-bottom-style: solid; border-bottom-color:
#1AD8E1" >
<p align="center"><b>
<font color="#000099" size="4" face="Verdana">FILE SPLITTER</font></b></p>
</td>
<td width="43" height="33">&nbsp;</td>
</tr>
<center>
<tr>
<td width="58" height="76"></td>
<td width="475" height="76" valign="bottom">&nbsp;<font size="3" face="Verdana"><font
color="#0000FF">Select the File For splitting :</font>
</font><input type='file' name='upload'> </td>
<td width="43" height="76">&nbsp;</td>
</tr>
<tr>
<td width="58" height="40"></td>
<td width="475" height="40">
<p align="center">
<input type='submit' name='submit' value=' Submit '></p>
</td>
<td width="43" height="40">&nbsp;</td>
</form>
</tr>
<tr>
<td width="58" height="40"></td>
<td width="475" height="40">
<p align="center">
<a href="http://localhost:8080/splitter_app/jsp/ani_page.jsp?a=0"
target="_blank"><b>Manage Clients</b></a></p>
</td>
<td width="43" height="40">&nbsp;</td>

</table>
</td></tr>
</table>

</div>

</body>

</html>

4)XML FILE

Web

118
<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2.2.dtd">

<web-app>
<servlet>
<servlet-name>initialization</servlet-name>
<servlet-class>planetwire.smsapp.servlets.InitServlet</servlet-class>
<init-param>
<param-name>splitter_app.dbURL</param-name>
<param-value>jdbc:mysql://localhost:3306/splitter</param-value>
<!--param-
value>jdbc:weblogic:mssqlserver4:sms_app_db@pwserver:1433</param-value!-->
</init-param>
<init-param>
<param-name>splitter_app.dbUser</param-name>
<param-value>root</param-value>
</init-param>
<init-param>
<param-name>splitter_app.dbPassword</param-name>
<param-value>ankita</param-value>
</init-param>
<init-param>
<param-name>splitter_app.driverClass</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
<!--param-value>weblogic.jdbc.mssqlserver4.Driver</param-
value!-->
</init-param>
</servlet>

<servlet>
<servlet-name>addClient</servlet-name>
<servlet-class>planetwire.splitter.servlets.AddUserServlet</servlet-class>
</servlet>

<servlet>
<servlet-name>editClient</servlet-name>
<servlet-class>planetwire.splitter.servlets.EditUserServlet</servlet-class>
</servlet>

<servlet>
<servlet-name>split</servlet-name>
<servlet-class>planetwire.splitter.servlets.SplitterServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>logon</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
</web-app>

119
 APPENDIX – B

OUTPUT OF THE PROJECT

116
117
118
119
120
121
122
123
124
125
 APPENDIX – C

2.271 Monthly ASCII for DUF

New Startin
Field Name g Length Description Justified
Number Position
Call Service Type: This field contains one of the following call service
code:

1 = Dial One (including non-equal access Dial One)

2 = Dedicated Access
3 = Travel Card Space filled and
1 Call Service Type 1 2
4 = 8XX Switch Service Left Justified.
5 = 8XX Dedicated Access
6 = Home 8XX
8 = PIN 8XX
12 = New Service
13 = World Card

116
 Component Group Code: This field contains one of the following groups
of service elements:

AC = Authcode
AN = ANI (Originating number)
BC = Business Card (Calling Card business customers)
DL = Dedicated Line
Space filled and
2 component_group_cd 3 2 HM = Home 8XX Number
Left Justified.
IW = Inbound Watts (8XX-XXX-XXXX)
RD = Ring to level billing for Dedicated 8XX
RT = Ring to level billing for Switched 8XX
WP = World Card Plus (Calling Cards for consumer customers)

Note: This field is used in combination with the component group value
field.

Component Group Value: This field contains and identifies individual

service elements associated with component group code. For example,
ANI for component group code AN, Business Card Number for
Space filled and
3 component_grp_val 5 24 component group code BC, etc.
Left Justified.
Note: This field is used in combination with the component group code
field.
Product Account Id: This field contains the Qwest numeric product Zero filled and
4 product_acct_id 29 12
account id. Right Justified.
Customer Account number: This field contains the Qwest internal Zero filled and
5 Customer Number 41 10
account number, example (56592210). Right Justified
Originating date: The field contains the originating date of the call, Zero filled and
6 orig_dt 51 8
format yyyymmdd, example (20030221). Right Justified.
Disconnect date: The field contains the disconnect date of the call, format Zero filled and
7 discn_dt 59 8
yyyymmdd, example (20030221). Right Justified.

117
 Origination Time: This field contains the originating time of the call, Zero filled and
8 orig_time 67 6
format hhmmss, (hours, minutes, secs), example (082145). Right Justified.
Disconnect Time: This field contains the disconnect time of the call, Zero filled and
9 discn_time 73 6
format hhmmss, (hours, minutes, secs), example (092552). Right Justified.
Call Duration Minutes: This field contains the length of the call in full Zero filled and
10 Call Duration Minutes 79 5
minutes. Right Justified
Call Duration Seconds: This field contains the remaining seconds from
the "Call Duration Minutes" field. Zero filled and
11 Call Duration Seconds 84 2
Right Justified
Note: contents of this field will not exceed a value of "59".
Dialed Number: This field contains the number entered by the originating Space filled and
12 dialedno 86 15
party. Left Justified.
Called Number: This field contains the physical terminating point for the
Space filled and
13 calledno 101 15 connection (ring to number for 800). This is the number called translated
Left Justified.
by the switch.
Automatic Number Identification: This field contains the number from
where the call originates, if available from the switch.
Space filled and
14 ani 116 15
Left Justified.
Note: Used to determine call origination and to determine pricing
NPA/NXX.

118
 Answer Type: This field contains the type of answer detected. Call
processing stores one of the following values:

0 = Nil value (For example, caller abandon during origination.)

1 = Software answer, no voice detected.
2 = Software answer, voice detected.
3 = Software answer, audio ringback detected.
4 = Hardware answer.
5 = Software answer, silence detected.
6 = Software answer, persistent ringing.
Zero filled and
15 anstype 131 6 7 = Audio tone detector (ATD) hardware error or forced disconnect and
Right Justified.
continuous tone at remote switch answer.
8 = Software answer, busy tone detected.
9 = Software answer, reorder tone detected.
10 = IMT software answer (billable, converted to a code of 2).
11 = IMT hardware answer (billable, converted to a code of 4).
12 = Early billing active with no answer.
13 = Early billing active with answer.
14–15 = Not used.

Note: Most common values are: 1, 2, 4, 10 and 11.

Personal Identification Number Digits: This field contains personal

identification number digits unique to each subscriber.
Space filled and
16 pindigs 137 4
Note: This field is an optional level of screening beyond authorization Left Justified.
code and automatic number identification and is available for Calling
Cards Only.
Information Digits: This field contains the two digit codes which precede
the destination number which informs Qwest about the type of line that Space filled and
17 infodig 141 2
originated the call, any special characteristics of the billing number, or Left Justified.
certain service classes. Example: Payphone, operator service etc.

119
 Surcharge field: This field contains one of the following surcharge
indicators when applicable:
18 Surcharge 143 1 Space filled
P = Payphone surcharge applies.
O = Operator surcharge applies, (Card Calls only).
B = Both Payphone and Operator surcharges apply.

Completion Code: This field contains the call completion code, which
describes the reason for call termination.

0 = Normal completed call

1 = Treated call
2 = Abandoned call
3 = Abnormal call
4 = Signaling system error
5 = Forced disconnect invoked
6 = Off-net route advance invoked
7 = Test call Zero filled and
19 compcode 144 6
8 = Recorded promotion Right Justified.
9 = TCAP DCP response time-out
10 = Not used
11 = Not used
12 = Abnormal release call
13 = Completed answer CDR
14 = Not used
15 = COS failure

Note: Calls with a completion code of 0, 1, 5, 6 and 12 are marked as

billable.

120
 Prefix Digit: This field contains the three bit converted to ASCII character
from UCS015 that defines the dialed (or called) number’s prefix digits.

What type of call was placed:

20 predig 150 1 Space filled
0= 1 prefix (domestic 1+call)
1= 0 prefix (domestic operator service)
2= 01 prefix (international operator service)
3= 011 prefix (international 1 + call)
4= 1 prefix (domestic 1+call)

Treatment Code: This field contains the call treatment code associated
with the call. The treatment may result from events detected within the
switch, or may arise from cause values contained within signaling
messages received by a trunk agency. Software converts cause values to
Zero filled and
21 trtmtcd 151 6 treatment values before it stores them in this field.
Right Justified.
000 = UNDT UNDEFINED_TRTMT (billable)
019 = BUSY BUSY_LINE (billable)
093 = NOSR NO_SOFTWARE_RESOURCE (billable)
Originating Trunk Group Name: This field contains the trunk group
Space filled and
22 orig_trunk_group_name 157 12 name of the trunk the call originated from. The originating trunk group
Left Justified.
name is retrieved from the Qwest "TRUNK_GROUP" table.
Originating Trunk Group Member: This field contains the originating Zero filled and
23 origmem 169 6
trunk group member where the call originated. Right Justified.
Terminating Trunk Group Name: This field contains the trunk group
Space filled and
24 term_trunk_group_name 175 12 name of the trunk the call terminated to. The terminating trunk group
Left Justified.
name is retrieved from the Qwest "TRUNK_GROUP" table.
Terminating Trunk Group Member: This field contains the terminating Zero filled and
25 termmem 187 6
trunk group member where the call terminated. Right Justified.

121
 Intra LATA Indicator: This field contains one of the following LATA
indicators:
26 intra_lata_ind 193 1 Space filled
0 = Inter LATA
1 = Intra LATA
Call Area: This field contains one of the following call area indicators:

1 = Intrastate
27 Call Area 194 1 Space filled
2 = Interstate
3 = International
4 = Other (i.e., Directory Assistance)
City Calling: This field contains the name of the city the call originated
from.
Space filled and
28 City Calling 195 10 Note #1: For domestic calls, this field is populated with calling city name.
Left Justified
Note #2: For international calls, this field is populated with calling
country name.
State Calling: This field contains the two character U.S. State code that
the call originated from, example (CA, OH, NY, etc).
Space filled and
29 State Calling 205 2
Right Justified
Note: For international calls, this field will contain the following value:
"IT".
City Called: This field contains the name of the city the call terminated
to.
Space filled and
30 City Called 207 10
Note #1: For domestic calls, this field is populated with called city name. Left Justified
Note #2: For international calls, this field is populated with called country
name.
State Called: This field contains the two character U.S. State code that the
call terminated to, example (CA, OH, NY, etc).
Space filled and
31 State Called 217 2
Right Justified
Note: For international calls, this field will contain the following value:
"IT"

122
 Rate Period: This field contains one of the following rate period values
for both domestic and international calls:

32 Rate Period 219 1 Domestic International Space filled

1 = Day 1 = Standard
2 = Evening 2 = Economy
3 = Night 3 = Discount
Terminating Country Code: This field contains the IDDD Country Code
number, for Terminating International calls only.
Terminating Country Zero filled and
33 220 4
Code Right Justified
Note: If call Originates International and Terminates U.S. or CAN, field
will be blank.
Originating Country Code: This field contains the IDDD Country Code
number, for Originating International calls only.
Originating Country Zero filled and
34 224 4
Code Right Justified
Note: If call Originates U.S. or CAN and Terminates International, field
will be blank.
Space filled and
35 PAC Codes 228 12 PAC Codes: This field contains PAC Codes.
Right Justified
Originating Pricing NPA: This field contains the NPA, (Area Code), Space filled and
36 orig_pricing_npa 240 3
from the originating location. Left Justified.
Originating Pricing NXX: This field contains the NXX, (Prefix), from Space filled and
37 orig_pricing_nxx 243 3
the originating location. Left Justified.
Terminating Pricing NPA: This field contains the NPA, (Area Code), Space filled and
38 term_pricing_npa 246 3
from the terminating location. Left Justified.
Terminating Pricing NXX: This field contains the NXX, (Prefix), from Space filled and
39 term_pricing_nxx 249 3
the Terminating location. Left Justified.
Authorization Code: This field contains the full Authorization Code for
Dedicated Access with Authorization Code and Calling Card calls.
Space filled and
40 Authorization Code Full 252 14
Right Justified
Note: If call is Dedicated Access without an Authorization Code, then the
originating trunk number will be populated in this field.

123
 Universal Access Number: This field contains the 8XX number or 950-
Space filled and
41 univacc 266 10 WXXXX number out-pulsed to the carrier. Also can contain the universal
Left Justified.
access number for calling cards.

Product Component ID: This field contains the product component id that has been identified for the
call. The following id's are the product component codes that will appear in this field based on the type of
call made:

1 = CAN Canadian Call, (Originating or Terminating).

3 = DA Directory Assistance Call.
4 = EMER Emergency numbers.
5 = IDDD International Call, (Originating or Terminating).
6 = IDDTVL International Origination Card Calls only.
7 = MEX Mexican Call, (Originating or Terminating).
11 = USAGE Domestic Usage Call.
234 = International to International Card call, including Mexico and Canada.

3611 = Touch America CAN Canadian Call (Originating or Terminating)

3612 = Touch America DA Directory Assistance Cal
3613 = Touch America EMER Emergency numbers
3614 = Touch America IDDD International Call, (Originating or Terminating)
3616 = Touch America MEX Mexican Call, (Originating or Terminating)
3617 = Touch America USAGE Domestic Usage Call
4435* = Touch America Queued Telecommunications Access Method (QTAM) Zero filled and
42 prcmp_id 276 6
*Touch America can not have International origination calls on the Qwest network. Right Justified.
Orig country Term country prcmp_id Description prcmp_id
DOMESTIC IDDD 5 International Usage
CANADA IDDD 5 International Usage
DOMESTIC CANADA 1 Canadian Usage
CANADA DOMESTIC 1 Canadian Usage
CANADA CANADA 1 Canadian Usage
IDDD DOMESTIC 6 International to U.S.
MEXICO DOMESTIC 6 International to U.S.
DOMESTIC MEXICO 7 Mexico Usage
CANADA MEXICO 7 Mexico Usage
IDDD IDDD 234 International to International
IDDD MEXICO 234 International to International
IDDD CANADA 234 International to International
MEXICO IDDD 234 International to International
MEXICO MEXICO 234 International to International
MEXICO CANADA 234 International to International

Note: Used to help determine what type of call is being placed.

124
 Carrier Selection: This field contains one of the following 5 different
values, from the DMS 250 switch, which helps clarify the Carrier
Identification Code:

0 = No indication
1 = Pre-subscribed & 432 not input by calling party
43 carrsel 282 1 2 = Pre-subscribed & 432 input by calling party Space filled
3 = Pre-subscribed, 432 input by calling party Undetermined
4 = Not Pre-subscribed & input the CIC 432 for this call

Note: Qwest uses this field to help identify casual callers. Different
values are populated for calls originating on a NGS switch. (Internal use
only)
Carrier Identification Code: This field identifies the long distance Zero filled and
44 cic 283 6
carrier from the CDR output, example (10432). Right Justified.
Originating Location Routing Number: This field contains an
originating LRN. The LRN is received in an ISUP JIP parameter with the
Space filled and
45 origlrn 289 10 SS7 IAM message, or provisioned in the JIP option of table TRKGRP.
Left Justified.
The number identifies the actual switch that owns the calling party’s
number.
Ported Number: This field contains the actual dialed number if the dialed
number has been moved to a new geographical location, i.e. PORTED. Space filled and
46 portedno 299 10
The “Called Number” field will be populated with a local routing number Left Justified.
from the new geographical location.

125
 Local Number Portability Check: This field contains one of the
following integers which indicates the use of LNP functionality in the call
and if the SCP was queried for LNP:

0 = No LNP (default)
1 = Incoming bit M set (query made on a previous leg)
47 lnpcheck 309 1 Space filled.
2 = Spare
3 = LNP trigger check, Continue response
4 = LNP trigger check, no query
5 = LNP originating query to SCP made
6 = Incoming Home LRN
7 = Home LRN per-originating query to SCP
Originating IDDD City Originating IDDD City Code: This field contains the international city Zero filled and
48 310 8
code code for originating international calls if provided in the switch file. Right Justified.
Terminating IDDD City Terminating IDDD City Code: This field contains the international city Zero filled and
49 318 8
code code for terminating international calls if provided in the switch file. Right Justified.
Originating LATA: This field contains the numeric LATA value for the Zero filled and
50 Originating LATA 326 4
originating location of the call. Right Justified.
Terminating LATA: This field contains the numeric LATA value for the Zero filled and
51 Terminating LATA 330 4
terminating location of the call. Right Justified.
Class Type: This field contains one of the following class type values:

SPACES = NPA/NXX not present or undetermined

0 = Non-Domestic usage calls, not applicable.
1 = Class I Space filled and
52 Class Type 334 2
2 = Class II Right Justified
3 = Class III
4 = Class IV
5 = Class V
6 = All Remaining OCN's

126
 Mexico Rate Step: This field contains a value of 1 - 8 for Mexico calls,
dependent on originating and terminating NPA/NXX. Space filled and
53 Mexico Rate Step 336 2
Right Justified
Note: This field is not valid for platform and wholesale customers.
Estimated Charge: This field contains the estimated charge for the call,
excluding discounts, promotions and taxes. Zero filled and
54 Estimated Charge 338 6
Right Justified.
Note: Field format = DDDDCC.
Operating Company Number: This field contains the numeric OCN Space filled and
55 Billing OCN 344 4
value for the pricing location of the call. Right Justified
Originating / Terminating Code (Call direction indicator): This field
contains one of the following originating / terminating codes:

A = PSTN-TO-IP
B = PSTN-TO-PSTN
C = IP-TO-PSTN
D = PSTN-TO-TERM
E = IP-TO-TERM
F = TERM-TO-IP
G = TERM-TO-PSTN

0 = ATM Frame
56 Orig_term_code 348 1 1 = OFF / OFF = Call originated and terminated at a switched location Space filled
(SW / SW).
2 = OFF / ON = Call originated at a switched location and terminated at
a dedicated location (SW / DED).
3 = ON / OFF = Call originated at a dedicated location and terminated at
a switched location (DED / SW).
4 = ON / ON = Call originated at a dedicated location and terminated at a
dedicated location (DED / DED).
5 = 8XX Remote Access VNS
6 = VNS Card
7 = DTO

127
 Calling Party Number: This field identifies the calling party number of
an originating SS7 call or value of the Line Number field of the AMA
Line Number parameter when the Line Number Type is calling party
identification.

This field is populated as follows when the EDGE_SWITCH parameter in

table OFCVAR is set to Y: Space filled and
57 clgptyno 349 15
Left Justified.
For DAL trunk originations, this field is populated with the optional
CPN field from table TRKGRP.

For SS7 FGD originations, this field is populated with the contents of the
Charge Number Parameter received from the IXC or LEC/CLEC. If no
Charge Number is received, the Calling Party Number is then populated.

Calling Party Number Identifier: This field contains one of the

following calling party number identifiers:

1 = ANI interstate (10 digit ANI with orig state not equal to term state)
2 = ANI intrastate (10 digit ANI with orig state equal to term state)
58 clgptyno_identifier 364 1 3 = CPN interstate (10 digit CPN with orig state not equal to term Zero filled.
state)
4 = CPN intrastate (10 digit CPN with orig state equal to term state)
5 = ANI and CPN invalid (ANI and/or CPN are present but not 10
digits or no associated state)
6 = ANI and CPN missing (ANI and CPN are blank)
Operating Company Number: This field contains the numeric Space filled and
60 Orig OCN 365 4
originating OCN value for the location of the ANI in the call record. Right Justified
Operating Company Number: This field contains the numeric
Space filled and
61 Tern OCN 369 4 terminating OCN value for the location of the Called Number in the call
Right Justified
record.
Unrounded Price: This field contains the price of the metered traffic if
Zero filled and
62 Unrounded Price 373 10 no rounding were to take place. The field is truncated to the 6th decimal
Right Justified
place, and does not include surcharges associated with the record.
Rate per Minute: This field is calculated by dividing the Unrounded Zero filled and
63 Rate per Minute 383 8
Price by the Billing Quantity. Right Justified

128
 Final Switch ID: This field contains the NPA/NXX of the switch the call Space filled and
64 Finsid 391 6
terminated to. Right Justified
Final Trunk Group Name: This field contains the trunk group name of
Space filled and
65 Final trunk group name 397 12 the trunk the call terminated to. The terminating trunk group name is
Left Justified.
retrieved from the Qwest "TRUNK_GROUP" table.
66 carriage_return 409 1 Carriage Return: Indicates a new line for the next record. DNA

Total 410
Length

2.271 Monthly ASCII Layout, NOTES

Note # Note Contents
Fields highlighted in Gray are the fields present in the 2.271 Standard
1
Daily Extraction Layout.
- If Called Number & Dialed Number = 8XX, then CDR = Dedicated
2 8XX.
- If Dialed Number = 8XX, then CDR = Switched 8XX.
Prcmp_id 4435* : Touch America Queuing, can be defined as Queued
Telecommunications Access Method (QTAM). This is a program
component in a computer which handles some of the communications
3 processing tasks for an applications program. QTAM is employed in data
collection, message switching and many other teleprocessing applications.
Basically, this is the programmed voice you hear when you want account
information on one of your personal accounts (banking, credit cards, etc).

129