Graduation Project Documentation

In This Chapter

CHAPTER 3

Design Principals

“How good the design is doesn't matter near as much as whether the design is getting better or worse. If it is getting better, day by day, I can live with it forever. If it is getting worse, I will die.” Kent Beck The creator of extreme programming

  

Modularity Interoperability Scalability

Design Principals Modularity Problems of Monolithic Applications A monolithic application is an application where all its pieces are combined into a single application. It is designed without modularity—this means the parts are very tightly coupled and cannot be separated or replaced. The requirements for developing applications are constantly changing. This causes the application to evolve and become more complex. These changes are based on multiple factors, including changes in the market, customer expectations, and changes in technology. Development team grows to handle the new needs of the application. If the application has not been properly designed to handle this growth, the complexity will continually be unmanageable. Because we have so many moving parts that are interdependent, and a number of people working on the code, the application is much less maintainable and more fragile. Additionally, our team may be divided into independent sub-teams to support the application. Continued development in such an application is often expensive and time-consuming. If the system design is not modular, it is difficult for the team to introduce new features and the application is difficult to test and to deploy as all team members should meet each time a new feature is added to the system to perform integration testing for this feature before going on. This also will introduce another problem, which is the dependency of each team member schedule on other team members schedules. For example, if one of the team who is responsible for the “Web Administration Application” depends on a particular function in the system’s service model (the service that connects the parts of the system), his work would be delayed until the function he needs in the service model is finished. The monolithic applications approach also introduce a new difficulty in respect to building the User Interface (UI). This approach would make it difficult to handle common scenarios that need to be addressed as the application grows. This includes changing the users experience based on role, adding new functionality, and modifying existing functionality and the way the user interact with the system according to nature of the application (Web, Windows, or Mobile application) and according to the set of functionality that must be available for each user. Another problem static composition poses is that it is difficult for multiple team members to work on the UI simultaneously. 65

Introduction| Sales and Inventory Management System

The solution is Modularity For all the above problems, we as a team was very concerned about those problems and the effect of them on development effort, time, and our co-operation during the development process. So we start searching for a solution which was modularity. Modularity: is designing a system that is divided into a set of functional units (named modules) which can be composed into a larger application/system. A module represents a set of related concerns. It can include components such as views, business logic, and pieces of infrastructure, such as services for authenticating users, working with data, and communicating with other modules. Modules are independent of one another but can communicate with each other in a loosely coupled fashion using the service model that connect all the system modules/applications together.

Benefits of Modularity Modularity provides the following benefits to our development team: 

It promotes separation of concerns through allowing a high degree of separation between the application infrastructure and the business logic.



It allows different team members to independently develop each of the individual business logic and infrastructure components.



It allows parts of the application to separately evolve. And we faced this scenario a lot during tea last stages of the development which makes us think about “how this could be achieved without the separation provided by this concept??”.



It promotes code re-use and flexibility because it allows business logic components and the application infrastructure to be incorporated into multiple modules. For example we have common parts between the four main system components such as data access tier and security services.

Design Principals of a Modular System When we decided to develop the system in a modularized fashion, we structures the application into separate modules that can be individually developed, tested, and deployed by different team members. This helps us address separation of concerns by keeping a clean separation between the business functionality and the implementation and used technologies as each team member is free to choose the implementation techniques and the technologies he is familiar with. Sales and Inventory Management System | Introduction

66

3

The SIMS system modules designed based on the following rules: 1.

Modules should not directly reference or call one another.

2.

Modules should use services to communicate with other modules.

3.

Modules should not be responsible for managing their dependencies. These dependencies should be provided through dependency injection.

4.

Modules should be separately maintainable.

5.

Each module can be fully used with all its functionality without depending on any other module except the service model. For example, the Mobile Client module can be fully used without depending on the POS module.

6.

Modules should reference the service model to be able to communicate with each others

The following page illustrate the relationship between the system modules and the main components of the service models that aids the separate modules to communicate with each other.

67

Introduction| Sales and Inventory Management System

Mobile Client Module

Point of Sales Module

Service Model

Web Administartion Module

3

Figure 1: Relationship between the system modules

SIMS Mobile Service

SIMS Service

Common Data Access Tier

Figure 2: Service Model components

Sales and Inventory Management System | Introduction

68

Interoperability The evolve of the modern communication technologies and the operating systems that support them makes the task of building interoperable distributed system not an easy task. Despite of this difficulty of building interoperable system, building such system open many opportunities for the developed system to get the most of many of the available technologies and development platforms, also it extends the life cycle of the system and increases its acceptance by many organizations. For the above reasons, we accepted this challenge and insist to face the problems and difficulties of building an interoperable distributed system using the latest Microsoft distributed computing technology called Windows Communication Foundation.

Interoperability and WS-I Basic Profile WCF enables WS-I Basic Profile 1.1 through the BasicHttpBinding which was used as the main binding that connect the Mobile Client module to the Inventory Management server. In the beginning stages of the industry’s implementation of the standards, people recognized that many of the stacks had been built upon an inconsistent foundation of technologies. Some vendors had chosen different versions of WSDL or SOAP as examples. Even how SOAP faults were returned by each implementation had been done differently in each implementation. So, getting interoperability amongst different implementations was a substantial challenge. In many instances, it was impossible without a significant amount of custom coding. The early vendor of SOA frameworks (such as SUN, Microsoft, IBM) did not conform to common open standards (in other words, they were vendor specific). This was mostly because of both customer and market demands. However, this was a major obstacle to achieve true interoperability between multiple vendors. The major industry participants combined resources forming the Web Services Interoperability (WS-I) Organization to facilitate and move web service standards forward in a nonproprietary and open manner. WS-I consists of a mix of products, services, and most important, user corporations—the primary focus of why we as solution architects exist. Currently, approximately 90 organizations are participating, with nearly 30 percent comprised of user corporations. 69

Introduction| Sales and Inventory Management System

Interoperability and wsHttpBinding This binding is the main binding used to connect the POS module to the Inventory Management server. This bending defines a secure, reliable, interoperable binding suitable for non-duplex service contracts. The binding implements the following specifications: WS-Reliable Messaging for reliability, and WS-Security for message security and authentication. The transport is HTTP, and message encoding is Text/XML encoding.

Befits of Interoperability Building the system on top of interoperable technologies and paying attention to interoperability guidelines, result in a system that can be easily modified to work on different platforms such as Linux, Unix, or Apple Mac OS X. The core of the system can be used without any modifications to work with the mentioned operating systems, only the clients need to be modified or re- implemented.

Scalability Scalability It is the ability of a computer application or product (hardware or software) to continue to function well when it (or its context) is changed in size or volume in order to meet a user need.

Since we started to choose an idea for our project we gave this concept a lot of attention, as it one the main features that can increase the acceptance of the system and its ability to compete with other systems made be large vendors. As scalability is a large concept, we start to make our own definition of scalability, in other words we started to choose the goals needed to be done to achieve this concept. One of these goals is to develop a complete system using Microsoft technologies that can be entirely deployed on personal computers running client operating systems such as Microsoft Windows XP or Vista, including the main server of the system. That’s make the system usable and accepted by a small store which his IT system is only a single PC with simple configurations, and also being able to compete with systems running on a large organization with a few hundred PCs network. The second goal was to develop a system that it’s components can communicate using any type of networks. Whatever the network being used in the organization is Internet or Intranet, LAN or WLAN, private or public, the system will just work the same. Sales and Inventory Management System | Introduction

70

3

Achieving the above goals wasn’t an easy task at all. The first goal prevents us from taking advantage of any special authentication and authorization features available in Windows Server family operating systems such as Active Directory. So, we was forced to implement such functionality internally in our system. The second goal also prevents us from taking advantage of the security features available in WLAN and private networks in general. So, again we find our self’s forced to implement all the main security services – authentication, authorization, confidentiality, and integrity - internally in the system as being illustrated in the “System Components” chapter.

71

Introduction| Sales and Inventory Management System

In This Chapter

CHAPTER 4

System Components

This one of the core chapters of this documentation, it include the detailed structural and technological aspects of the system components

 Introduction  Web Administration Module  Service Model  Reports  Users Management  POS Module

Introduction Sales and Inventory Management System is not just an application, it’s a complete management system for a departmental store. SIMS is a management system which is responsible for all aspects of store operation such as inventory management, sales operations, producing sales receipts, and generating different types of reports used to identify the state of the store as a whole. The system have user friendly and initiative GUI that ease the interaction between the user and the system. The system components runs on multiple platforms as desktop, servers, or mobile devices, and multiple operating systems. Main Applications Inventory Management Server

Customer Details

Customer Details

SIMS Service

Search Request

Point Of Sales

Search Results

SIMS Mobile Service

Notification Message

SIMS Mobile Service Client search criteria

On-Site Operator

Search Result

Mobile Application

Barcode (Text / Image) Notification Message

Overview of main system components and relationship between Them

As we mentioned above the system is a complete departmental store management system, it’s one integrated system that contains both the users component- whether the user is sales man or on-site operator – and the administrator components used by

73

System Components | Sales and Inventory Management System

Search Results

Login Request

Barcode Image

System Main Database

Login Request

Order

SIMS Service Client

Stock Operations (In/Out/Damaged)

Notification Messages

Notification Message

Barcode search criteria Search Result Receipt

Inventory Management / User Management Applications

Shipment Details

Sales Person

Order

the administrators for performing admin level functions such as adding new items to the inventory, changing the price of an item etc. In the following paragraph we will gives short introductory statement about the main system applications.

Web Administration Module Introduction The web administration module is the module that cares about inventory management as well as performing some user related tasks. It integrates with the web user management module for best integration. This part will explain the technical architecture and layering that the web administration module implements, as well as the challenges that have been met and overcome thought developing this part of the system. It also includes notes on the implementation details, like how the class construction looked like, as well as the utility helpers that were created to best support the different areas of this module.

Sales and Inventory Management System | System Components

74

4

The Administration Layering This is how the web administration gets the data though in and out of the main system database.

Factories Library DB Access

LINQ

Data Access Helpers

Custom LINQ Factories

Extension Methods

User Helpers

Lookup

Messaging

Administration Website Web Utilities

UI Helpers

Navigation Helpers

UI & Theming

ASP.NET 3.5

AJAX

The module layering is set up with 3 main tiers 

Data Access This is shared within the main parts of the system (except the mobile part because it connects via services). This is simply a usage of the Language Integrated Query (LINQ) features to have a clean data access (DB connections, queries, etc), and build on them the entities needed for the system to perform (like products, users, etc…).



Business Logic This is a layer built on top of the data access to provide: o Centralization of repeated code

75

System Components | Sales and Inventory Management System

The part of coding required to get table info or save data to DB is very similar. If we needed to change any part of it during the project development, we’ll want as less places as possible. If we split the data access code and other parts of the code in every page, we’ll end up with so many duplications, and a single change will be so expensive to go through all the application pages and fix that. o Helpers for the common non-UI dependent tasks. We have been working on getting many features for the UI. Paging and sorting are the simplest examples. To be able to provide such features, we needed some helpers that make applying the features way easier. That was a challenge in creating the helpers themselves by the way, to overcome some of the limitations provided by LINQ. This is to be discussed in later part.



User Interface (Administration Website) This is the actual pages developed, as well as some parts that are needed for usability. o ASP.NET 3.5 This has been used as part of .NET framework 3.5. The usage included the most interesting features to save time like ASP.NET Themes and Skins. o AJAX There’s a part dedicated to why AJAX is important for applications. SIMS tries to leverage this as well. o UI Helpers Tasks like filling a control with specific data to display certain field and use another field as key, and other UI related tasks are very common and repeated. This is where the UI helpers come to play. Those make the closest functionality to what is needed for data. o Navigation Helpers In the web application, navigation between pages happens through creating URLs and passing IDs in them. This is easy to get mistyped and such. The navigation helpers ensure moving between pages does not have any of a set of certain problems. o Other UI helpers We have helpers for getting the logged in user information and displaying the success and error messages in consistent way (with taking benefit of

Sales and Inventory Management System | System Components

76

4

ASP.NET themes of course). Those make the other common tasks accessible from one place.

More about the business layer The business layer aims to encapsulate the data access and provide any additional functionality / validation. It uses the factory pattern that will be explained soon. The factory pattern This is simply making static classes (ones that have only static methods and properties), and making every class worry about single entity type (Product, Supplier, etc…). The static class (by definition) does not represent objects of that entity type (because that’s what LINQ entity classes are anyway). Instead, it provides static methods for all the common data access operations. Each method encapsulates the required LINQ codes and in some cases uses some functionality that is private to that class. Any DB access is not allowed except via calling a related factory.

The factory pattern in SIMS The following figure shows examples of how the administration uses the factory pattern. All the factories are in the namespace “SIMS.Management”. We have sub namespaces for inventory, users, and for common DB access features.

77

System Components | Sales and Inventory Management System

4

Using internal factories for DB access When providing listing functionality for any kind of entity, you usually seek certain of specific features:  



Sorting Being able to sort the list by any specific field or attribute. Default sorting Having the list displayed sorted by default any the most appropriate attribute. Example: displaying suppliers sorted by the supplier company name by default. Filtering This is useful for so many things. You may for example want to filter by related entity (like display products only in certain product category), or just provide a search functionality) which the system provides with searching any of the entity attributes or attributes of even a related entity).

Sales and Inventory Management System | System Components

78



Paging

When the system is used in real environment, the amount of records to display in a page can grow very large. This is where paging is required. You need to find a way to load only limited number of items in a page, and be able to get the other pages as well, each to come individually. There’re also all insert/update/delete operations that share the same logic internally. Those are encapsulated in the factories to just get called when needed passed the related entities.

The TableFactory Helper As the listed functionality is almost in every entity type, we decided to use the beauty of LINQ and C# 3.0 to provide all the functionality in code in one factory, and make all the other factories use this TableFactory internally. This is used in a way that a method that selects all product categories for example would just look like: return db.Categories.GetAll (DisplayColumn, pageSize, pageNumber, null, null, out pageCount);

All the actual coding and heavy lifting is done inside TableFactory internally. It was implemented in a hidden way so that it appears as normal method of the table (as in the Categories table in the given example) using a feature in C# called “Extension Methods”.

Other functionality Sometimes there’s also set of entity specific functionality. The clear example is deferred loading. Deferred loading is when you have to load related entities every time you load the entity. An example is products entities list. You always have to display data about the categories and suppliers. LINQ has the functionality of not loading this information for each product but load them for all products at once. However, it cannot guess which related tables it should load and which will be an overhead if loaded. It needs to be “told” what entities to load.

79

System Components | Sales and Inventory Management System

That’s where the private functionality comes to play to provide similar functionality to all product related methods for example.

The UI: ASP.NET Features ASP.NET as a web framework was the most logical one to use with all the .NET libraries used in this project. However, it was not something we had to or such without taking benefit of. Actually, it was very useful in many areas

MasterPages A master page is a template that has dynamic parts to fill in. See the next figure.

4

This is very similar to the main master page of the entire SIMS web administration. As you can see, it is a template containing all the common parts that show in every page and enabling an area (the one in white) to add a page-specific content. Sales and Inventory Management System | System Components

80

This is used even more in the SIMS website. We use nested master pages (master pages inside masterpages) so that we have a template for the entire site, that includes a template inside for each section (products, suppliers, etc) so that all pages in this section share things like the section title for example. Themes and Skins The documentation shows how important CSS is for the consistency of UI elements like different levels of headers and things like tables and input elements. ASP.NET has a great feature called Themes that we used in the website. The entire SIMS website has one theme. This means it shares a set of CSS files that we wrote to become automatically included in every page. Actually, sometimes we need a middle layer between CSS and the different ASP.NET controls that enable coding against them. This is called Skin. We have different skins when direct connection between the CSS and the ASP.NET control (like text box or grid) is itself a repeated part that we want in one centralized place in case we needed to edit it.

81

System Components | Sales and Inventory Management System

The Page Model

4

Source: http://aspnet.4guysfromrolla.com/images/pageLifeCycle.gif The ASP.NET framework enabled us to code against certain events just like any desktop application developer would do. We have events like load and click to work on. Of course the web model (XHTML and browser and such) does not have those events in real. It is the ASP.NET framework that hacks the process to raise those events for us. It also has the notation of a “Post Back” which is when the page comes from posted form (like button click or such) and will be processed. This is important to know to save performance, as for example, a page that is posted back can keep its contents without reloading all the data, so we can save database hits and not execute the code that loads the data while we do not need to.

Sales and Inventory Management System | System Components

82

The UI helpers While working with ASP.Net is actually fun, it (like any other) is not a complete nor ideal framework. We still found a number of problems and number of tasks that can be automated. Here came the idea similar to business factories to help get those managed. You can see examples of the helpers (we called them utilities) that we created for the SIMS web administration:

We’ll highlight the problems that raised the need for those right away. The user utilities This is concerned with the logged in user. ASP.NET has a great feature called session, which uses browser cookies to keep track of the same user when interacting with the website (because HTTP is by default stateless protocol. You normally do not know when you get a request whether the user of this request was in the site before or not). However, this feature is a generic one. It does not know of course what user information we need to store about the user, etc… But we need to know more about the user when he is logged in. For example, when a user uses the “Send message” feature to other users, the website needs to know which user the messages will be sent “from”. That’s why we created our users helpers, to internally check for the session and manage sorting and loading the website specific user information from it. 83

System Components | Sales and Inventory Management System

The navigation Helpers When a user does save a form, I cannot reload the form as is to him again. Otherwise if the user presses F5 in his browser to reload the page, he’ll be asked to resend the data (simulate the button click) and then does all the operation again. This is not desired in many cases, like for example inserting new record. You do not want the user to accidently try to add the same record again and again. The solution here is to use the navigation helpers that we created to enable the navigation and pass the required data to the page when reloading it (usually passed in the URL), like the ID of the entity being edited in case of editing, etc… It also handling getting rid of existing URL parts if not needed.

The “Flash” Helpers By flash here, we mean a message that flashes in front of the user at certain event, not Adobe Flash. The examples are success messages (after saving an entity for example) and error messages (like searching that has no results). The need for framework for flashing messages came from the fact that HTTP is stateless. The flash is usually used after the navigation. It shows only once and regardless of refresh or so, as long as the reason for the message no longer exists, the message is not displayed. It’s only there in the page after the operation that caused it. How it works? Each page has a unique key in the session. When the message should be displayed, a value object is saved to the session with that key, after page redirection (which happens just next, so, no problem in time in between, however we have the unique key as second defense), the flash message is displayed, and the flash is being cleared. The flash message color and style are different based on the type of message (success, error, info). Each of those maps to CSS class, so that we can fully customize the feel and look of the message just by setting the CSS style of that CSS class.

The Controls Helpers When working with ASP.NET controls, we found that certain functionality keeps repeated, and we thought we should put that in one place and just call it. Sales and Inventory Management System | System Components

84

4









85

Summary Text This is a very simple helper. In some situations, we need to display something like description of a product or so. It can be very long and the page will look so ugly. We then want a limit to text length if exceeds a number of our choice, and add “…” or anything (we should be able to choose it differently) to show the text is not complete. This is exactly what this helper does. Data Binding This deals with common operation of filling a display/key list (like in drop down for selection) that the key comes from certain field and the element displayed is from another field. The helper encapsulates doing this all in one line call, and supports any listing control, not just drop down. Select Bu Value For editing purposes, filling the drop downs is not enough. We also need to select the drop down item using the value not the displayed part. This means searching for the item and selecting it not any other item in the drop down. This helper does it in ne call Related Objects In listing, when the related entity is not loaded (like category in product listing) or not entered in DB if optional, we cannot get the display field of the related entity when not present and would get an error. This helper gets a dummy related entity when the related entity is not present so that the field value shows as missing (which is normal and expected here not a problem) instead of sending an error message in a non-error situation.

System Components | Sales and Inventory Management System

Summary

ASP.NET and .Net in general was a great framework to build on. We made use of all the features to serve real application needs not just because of being fans of the features and stuff. We have used the factory pattern to manage the DB operations, and for that went through advanced language features and extensibility features of our DB framework, LINQ. We also had the same usage for the ASP.NET features, building on very generic great features to make the usage customizable for the application specific needs.

4

Sales and Inventory Management System | System Components

86

Service Model The service model is distributed application that connects the other three applications in the system, Web administrations, Point of Sales, and Mobile Client. The service model consist of two main services SIMS Service: Is the service that connects the Point of Sales application with the Inventory Management server SIMS Mobile: Service: Is the service that connects the Mobile Client and Inventory Management server Each service in the service model is a combination of the following components 1. Messages Contracts: are standard SOAP messages used to send and receive actions requests (call a method in the service side or receive a method call from the client side), and encapsulate the data contracts used to exchange data between the system applications 2. Data Contracts: are XML serializable objects that holds the data being exchanged between the system applications 3. Fault Contracts: are standard SOAP faults that used to encapsulate .NET exceptions occurs in the service side and sent them to the clients. 4. Service Contract: ties together multiple operations into a single functional unit The following diagrams illustrate the Service Model, Data Contracts, and Fault Contracts of SIMS Service.

87

System Components | Sales and Inventory Management System

Order

OrderDetail

Message

Members

Members

Members

ID CreatedBy OrderDate ShippedDate Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry ShipVia OrderDetails CustomerID

OrderID UnitPrice Quantity Discount Product ProductID

MessageID Body SendingDate ReceivingDate Sender Receiver

OrderDetails

Customer

Orders

StockOpertationType

Messages

Product

Members

Members

Members

ID FirstName LastName Title Address City Region PostalCode Country Phone Fax Orders

ID Barcode ProductName Description UnitPrice UnitsInStock RequestedQuantity AllowReturnAfter IsTaxable

ID Title FirstName LastName IsSalesperson IsOnsiteOperator IsAdministrator

Shipment Members

Values ProductsIn ProductsOut ProductsDefatted

ItemNotFoundFault

Customers

ProductQuantityNotAvailableFa…

Products

Members

Members

Details ItemID

Details ProductID AvailableQuantity RequestedQuantity

CustomerID CustomerName

Members Details ProductID OrderID

TransactionNotCompletedFault Members Details

GeneralFault LoginFault Members Username DateTime

ID CurrentQuantity OriginalQuantity ExpirationDate Date CreatedBy Product

CustomerExistsFault

Members

ProductNotInOrderFault

User

Shipments

ReturnPeriodExceededFault

Members

Members

FullName ExceptionDetails InnerExceptionFulName InnerExceptionDetails Message InnerExceptionMessage

ProductID ProductName AllowedReturnPeriodInDays

SIMSService

SIMSServiceContract

GetProductByID

Faults ProductNotFoundFault GeneralFault

ProductRequest

Parts ProductID Barcode

ProductResponse

Parts Product

SearchProducts

Faults GeneralFault

ProductsResponse

SearchRequest

Parts

Parts

Products

Predicate

CompleteOrderRe…

CompleteOrder

Parts Order UserID

Faults ProductQuantityNotAvailableFault TransactionNotCompletedFault CompleteOrderResponse

Parts OrderID

ReturnProduct

Faults Logout

Faults GeneralFault

ProductNotInOrderFault TransactionNotCompletedFault ReturnPeriodExceededFault ItemNotFoundFault

ReturnProductRequest

Parts OrderID ProductID Quantity Reason UserID

RequestProduct

RequestProductRe…

SetCustomer

SetCustomerRequest

Faults

Parts

Faults

Parts

ProductNotFoundFault TransactionNotCompl… GeneralFault

ProductID RequestedQuantity

GeneralFault

NewCustomer IsEdit

SearchMessages GetOrderByID

Faults OrderRequest

Faults OrderNotFoundFault GeneralFault

GeneralFault

Parts OrderID MessagesResponse

Parts Messages

OrderResponse

Parts Order

Login

SearchOrders

LoginRequest

Faults

Parts

GeneralFault

Username

Faults GeneralFault

OrdersResponse

LoginResponse

Parts

Parts

Orders

LoggedUser

ChangeUserPsw

GetCustomerByID

Faults GeneralFault CustomerNotFoundFa… CustomerExistsFault

CustomerRequest

Faults GeneralFault

ChangePswRequest

Parts NewPassword

Parts CustomerID

CustomerResponse

SearchCustomers

Parts

Faults

Customer

GeneralFault

CustomersResponse

Parts Customers

Reporting services in SIMS: In our project we use the reporting services to represent the data, the numbers, characters and any details in a simple design and showing the details to each report (every one can understand it simply). We use reporting here to represent product sales (per month or year), user sales (per month or year), returned products (per month or year), and products sales per day (from it we can identify on crowded hours per days) Some explanation of our reports as follow: 

Product sales report :

1

2

3

-

91

1 - To show any product sales report we choose the product name from products list and the year and the month and clicking on ( month chart ) button to view the selected month report - or – Clicking on ( year chart ) button to view the selected year report . System Components | Sales and Inventory Management System

-

2 - Show the sales chart for selected date containing quantity and orders dates and sales curve.

-

3- - Grid view that show some details about sales orders and its dates and times.



Sales person sales report :

1

4

2 3

-

1 - To show any user sales report we choose the user name from sales person list and the year and the month and clicking on ( month chart ) button to view the selected month report - or – Clicking on ( year chart ) button to view the selected year report .

Sales and Inventory Management System | System Components

92

-

2 - Show the sales person sales chart for selected date containing quantity and orders dates and sales curve.

-

3 - - Grid view that show some details about sales orders and its dates and times.

Or we can represent the sales chart per selected year like: in the next form we find the quantity in May = 72 = all the products that sold by selected sales person in May 2008.

We can represent the sales chart per selected month in 3D like :

93

System Components | Sales and Inventory Management System

3D

4

Sales and Inventory Management System | System Components

94



Returned product report :

1

3

2

-

1 - To show any returned product report we choose the product name from products list and the year and the month and clicking on ( month chart ) button to view the selected month report - or – Clicking on ( year chart ) button to view the selected year report .

-

2 - Show the sales chart for selected date containing quantity and orders dates and sales curve.

-

3- Grid view that show some details about sales orders and its dates and times.

95

System Components | Sales and Inventory Management System

Users Management How to add , view , edit , or delete a user details:  Add a user:

1 Unique

4

2

Sales and Inventory Management System | System Components

96

1 – Required fields: You must enter the first , last and user name. And the password from 6 to 20 character and its confirmation . The birth date lass than ( the current date – 10 years ) meaning that the user age must greater than 10 years in any next years . but you can enter the hire date until the current date. You must select the position. 2 – Title , address , city , country , region , postal code , home phone , mobile phone , photo , and the notes are optional fields. -

If you enter the fields not correctly the validation message will appear for any field . After entering the user details correctly , click on submit button to save the details . if all data you have entered are correct , the next page will appear .

The first name of the User

-

In this page the user picture will appear that you have chosen . And you can change the user photo by clicking on “ Change the photo “ button .



View users: in view users page select the user to view his details as follow .

97

System Components | Sales and Inventory Management System

The photo of the selected User

4



Edit or delete a user details: You can Search for a user by his first name , last name , city , country , or ID and “click search” button and select the user like:

Sales and Inventory Management System | System Components

98

-

Click on “delete” button to delete the selected user then click on “yes delete” to confirm deleting the user as:

-

Or click on “ No Cancel” button to cancel deleting operation.

-

To edit the selected user details click on “Edit” button then the next page will appear :

99

System Components | Sales and Inventory Management System

-

To change the user picture click on the “Browse” and choose the new picture then click the “Save the photo ” link to save the new picture.

-

You can’t change the user name .

-

You can change any other details and click on “ Save Changes ” button to save the new changes.

 Returned product page:

3

2

4

1 - You can search for returned products by product name , barcode , reason , returning date , product ID , order date , or order ID . - After entering the required click on “search” button or you can click “search” directly without entering any data to view all returned products. - 1- Grid view contains the search result data. - 2- Order details of the selected returned product. - 3- Product details of the selected returned product.

Sales and Inventory Management System | System Components

100

2. Point of Sales Module

Introduction This application is the direct point of interaction between the system and the salesman. Usually this is the most used part of the system as it being used by many salespersons continuously during store work hours and it is the main entry point for orders, sales, and customer’s information. The POS application is the main tool that that the salesman uses to search the system database for products, orders, or customers information.POS application is responsible for all sales operations which includes    

101

Creating orders Adding and editing customers Searching orders, products, and customers Receiving notification messages send through the inventory server

System Components | Sales and Inventory Management System

Design and Components UI Components The components that servers the UI is divided into three categories   

Classes that provide services for the interface components User controls that compose the overall user interface Windows forms that consist of usual controls and user controls

Point of Sales Module Receipt Printing

UI Components

Data Services

GridView Operations

Service Proxy

UI Services

4

Interfaces Customer s Form

Login Form

Printing Options Form

Sales Form

Sales View part

Quick Search Part

NonFlickering Panel

Classes GridViewOperations Class This class is the main class for all the operations related to using and formatting DataGrid controls used in the application. This class provides the following functionality to the application 

Adding products to the DataGrid controls and ensuring that there is no duplicate product added to one DataGrid at any time, and if the product to be added to the DataGrid is already added before, the quantity of this product in incremented. Sales and Inventory Management System | System Components

102





Perform calculations to obtain all calculated data that is not present in the system database such as the taxes of each product, the total price of the requested quantity of the product, and the total price after applying any discounts for the user. Format the data presented in DataGrid controls to be in a clear and consistent view, this includes changing the names of some data, formatting the names of other data (such as formatting “FirstName” property of Customers entities to “First Name”), and even hiding some data that is not relevant to the user such as IDs of entities

UIServices Class This class provides services for the interface components in general. The class file contains all enumerations related to the UI such as DisplayScreen and DisplayMode enumerations. The class provides the following functionality 



Format the application different screens (Sales, Customers, Products, etc) according to the state of the application and the current functions being performed, adding or searching a customer for example. Gives the input controls different appearance in case of invalid input as a visual clue of the invalid input, which is a more friendly technique for notifying the user

Figure 2: Invalid input visual clue



103

Figure 1: Valid Input

Clear controls data or any visual indications applied to controls, in other words reset control state

System Components | Sales and Inventory Management System

Windows Forms Login Form This form is the first screen that the user interacts with upon starting the application, it prompts the user to provide his/her credentials to access the system main server and perform actions on the system data. This form offers the user to remember his/her user name to shortening the login process.

Figure 3: Login Form

Search Form This form s a common form that is used in many screens in the application, such as Orders, Customers, Products, and Messages screens. The form consist of three parts, the first one on the top is the quick search part, which is a simple text box with embedded button that can perform quick search on the data according to the current screen in use.

4

Figure 4: Search Form

The second part is Advanced Search which is expandable part, that’s means that it is not viewed by default to save the working area of the application, it is only viewed when needed using the small down arrow of the right side of the quick search part. This part provides more searching capabilities to the user, including search with many properties, and those properties changes according to the screen being currently used. It also allow the user to choose the search conditions to search by using one of the following conditions 

Contains



Equals Sales and Inventory Management System | System Components

104



Greater Than



Greater Than or Equal



Less Than



Less Than or Equal

As well as joining many conditions (until five conditions) using “And” or “Or” joins to compose more advanced search criteria. The third part on the footer of the screen contains the number of results being found and a slide bar that can be used to zoom in or out the results grid to ease visibility of results. This form also contains a toolbar that allow users to export search results to four different file formats which are 

Microsoft Word document (*.doc)



Microsoft Excel spreadsheet (*.xls)



Adobe PDF file (*.pdf)



XML Papers Specifications file (*.xps) which is standard document format that can be viewed by most web browsers without any need to install special software

Those file formats are the most common file formats documents used for such a purposes. The form also facilitates printing the search results using Printing Options Form. This form offers the user to choose the number of papers to use for printing the results and adapts the results sheet size according to that number. It also allow the user to choose the printing orientation of printing whether is normal or landscape and the title of the Figure 5: Printing Options printed sheet, then the user goes to the usual printer options screens provided by the operating system to choose printer specific options. 105

System Components | Sales and Inventory Management System

Customers Form This form is used for adding and editing customers, the form contains data valuators to validate user input. At the bottom of the form appears the orders that has been made by that customer. The form consist of expandable parts to allow the user to customize the workspace according to his/her needs.

Figure 6: Customers Form

User Controls

User controls: provide a means by which custom graphical interfaces can be created and reused. A user control is essentially a component with a visual representation. As such, it might consist of one or more Windows Forms controls, components, or blocks of code that can extend functionality by validating user input, modifying display properties, or performing other tasks required by the author of the control. User controls can be placed on Windows Forms in the same manner as other controls. Sales Part This control consist of three main parts, the first is where the userwhich is a sales man or a cashierenter the barcode of the product and the quantity of that product to be added to the order products which is the second part. Order products part allow the user to modify the quantity of the product and to apply discounts to them as needed.

Figure 7: Sales Part

Sales and Inventory Management System | System Components

106

4

The third part contains information about the customer if needed-as the system support that an order being made without specific customer info-and the total price of the order products. Quick Search Part This part appears in the task pane of the sales screen, it provide a fast mean for the user to search for a product using other criteria than the barcode of the product, it also allow a fast search for a specific customer without need to change the current screen and go to the customers screen to search for customers, and hence shortening the time needed to complete the order and make the sales man task easier.

Non-Flickering Panel It is a custom panel control inherited from the normal windows forms control “Panel”. The difference of this custom control that it uses double buffering technique to display its contents on the screen to avoid the undesired effect of flickering that happens when displaying many items in the panel.

Figure 8: Quick Search Part

Data Services This class contains the functionality needed to build dynamic LINQ expressions which is used as search query that being supplied to the service proxy to obtain data.

Service Proxy Before talking about this class I would like to a give a brief introduction about the Proxy Pattern used to implement this class.

107

System Components | Sales and Inventory Management System

Proxy Pattern Role The Proxy pattern supports objects that control the creation of and access to other objects. The proxy is often a small (public) object that stands in for a more complex (private) object that is activated once certain circumstances are clear.

Design The design of a proxy and its players is shown in the UML diagram in the following Figure.

4

Figure 9: Proxy pattern UML diagram

The players in the pattern are: ISubject A common interface for subjects and proxies that enables them to be used Interchangeably Subject The class that a proxy represents Proxy A class that creates, controls, enhances, and authenticates access to a Subject Request An operation on the Subject that is routed via a proxy. The central class, Proxy, implements the ISubject interface. The Client can use ISubject to abstract away from proxies. However, sometimes we can dispense with this interface. Each Proxy object maintains a reference to a Subject, which is where the action really takes place. The Sales and Inventory Management System | System Components

108

Proxy performs frontend work. Its value can be enhanced by making the Subject a private class so that the Client cannot get to the Subject except via the Proxy. There are several kinds of proxies, the most common of which are: Virtual proxies Hands the creation of an object over to another object (useful if the creation process might be slow or might prove unnecessary) Authentication proxies Checks that the access permissions for a request are correct Remote proxies Encodes requests and send them across a network Smart proxies Adds to or change requests before sending them on ServiceProxy Class The ServiceProxy class can be classified as authentication and remote proxy. It encapsulate the SIMSServiceClient class which is the actual class responsible for the communication between the POS application and the Inventory Management server. The responsibilities SIMSServiceClient class include the following  Authenticating the client to the server  Creating secure channel with the server  Handles asynchronous request/reply communication patterns between the POS client and the Inventory Management server.  Maintaining internal classes that represent the Data Contracts and the Service Contract of the SIMS Service.  Handles fault contracts which is received as SOAP messages from the server and then encapsulate then in FaultException generic class.

109

System Components | Sales and Inventory Management System

Receipt Printing Creating printed documents is an essential task in most business environments, but it can be a complicated and confusing task to programmers. Normal printing in visual studio is so complicated as we have to draw every pixel, where to start, where to end and even its size. Another idea we find is that we can print the bill using “REPORTS” in visual studio, reports was much easier and save much time. Our report was designed and it gets the data from a data source which is a reference to the data base in an easy way as we see below.

4

Sales and Inventory Management System | System Components

110

How the bill report designed? 

We add a new report in the SIMS project



The main table was about the product and its information “Product ID, Product name, Quantity, Unit price, Allow return after ”



In the top right of the report there is the bill “receipt” information “Order ID, Cashier name, Order date”



In the middle right, we find the delivery information “if found” which are “the customer name, address, phone”



In the bottom of the bill, the thanks you statement plus the “Product Returning conditions”.

How the report works? 

First, we define a data source or even data base that the report can generate data from it.



Then we drag & drop objects from the data source to the report, for example, in the “Order ID” we drop beside it the “Order Id” from the data source.



Finally, the values of the item in the database appear in the report.



Note that the data source acts as a reference to the database, as if are passing data by reference not by value

Why Data source not database? Because, the PC printing the bill will be the cashier PC who have a limited permissions in accessing the database, so we use a data source as reference just to read values from database.

111

System Components | Sales and Inventory Management System

In This Chapter

CHAPTER 5

Foundation of Data

In the route from the database to client modules and from client modules back to the database, the data takes many shapes and have different meaning. This chapter examines the shapes of data traveling between the system components and the full specifications of the system main database.

   

Introduction SIMS Database Data Access Tier Inventory Management Server  Client Modules  Database Tables  LINQ Entities

The Journey of Data In the route from the database to client modules and from client modules back to the database, the data takes many shapes and have different meaning. In the following section we will try to go along the journey of data in SIMS.

Client Modules POS and Mobile Client

Translator SOAP Messages Containing Data Contracts

Translator Inventory Management Server Web Administration Module + SIMS Service + SIMS Mobile Service

LINQ Entities

Common Data Access Tier

Row Data

SIMS Database 113

Foundation of Data | Sales and Inventory Management System

Data Access Tier This tier is responsible for converting row data we get from the database into LINQ entities, which are pure C# classes that represent data in object model instead of relational model used to represent data in the database.

Object/ Relational Mapping

Products Table in the Database

5

Product Entity

Inventory Management Server In the server, the data is consumed as pure C# classes then, a SIMSDBDataContext object – which derives from DataContext class – send all the changes in data – which includes inserts, deletes, and updates – to the underlying SIMS database as mentioned in details in the LINQ technology section.

Sales and Inventory Management System | Foundation of Data

114

Client Modules In the road between the Inventory Management server and the Client Modules, the data is converted into standard SOAP messages that contain serialized data contracts objects in the body of the message. The reverse operation occurred when the data need to be transferred back to the Inventory Management server.

Translator

Product Data Contract

Figure 1Product Entity

Database The following diagrams illustrate the database tables and the corresponding LINQ entities generated from the database.

115

Foundation of Data | Sales and Inventory Management System

Suppliers

Customers ID

ID

FirstName LastName Title Address City Region PostalCode Country Phone Mobile EMail Fax Notes

CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone EMail Fax

Categories ID CategoryName Description PhotoPath

Messages ID Body

Users ID

Products ID

Orders ID

Barcode ProductName Description SupplierID (FK) CategoryID (FK) UnitPrice UnitCost UnitsInStock UnitsOnOrder ReorderLevel RequestedQuantity ExpirationNotificationDays AllowReturnAfter IsTaxable

CustomerID (FK) CreatedBy (FK) OrderDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry

Username LastName FirstName Title BirthDate HireDate Address City Region PostalCode Country HomePhone MobilePhone Notes IsSalesperson IsOnsiteOperator IsAdministrator Password IsLoggedIn LastLogin

UsersMessages MsgID (FK) SourceUser (FK) DestinationUser (FK) SendingDate ReceivingDate

Shipment OrderDetails

ReturnedProducts

OrderID (FK) ProductID (FK)

ProductID (FK) OrderID (FK)

UnitPrice Quantity Discount

ReturnedQuantity ReturningDate Reason ReturnedBy (FK)

ID OriginalQuantity CurrentQuantity ExpirationDate ProductID (FK) CreatedBy (FK) Date

DamagedProducts ProductID (FK) Reason Date Quantity ReportedBy (FK)

ShipmentRequest ProductID (FK) CreatedBy (FK) RequestDate Quantity

User

OrderDetail

Order

Properties

Properties

ID

ID

Username LastName FirstName Title BirthDate

CustomerID CreatedBy OrderDate ShippedDate ShipVia

HireDate Address City Region PostalCode Country

Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode

HomePhone MobilePhone Notes IsSalesperson IsOnsiteOperator

ShipCountry

Properties OrderID ProductID UnitPrice Quantity Discount

ReturnedProduct

IsAdministrator Password IsLoggedIn LastLogin

Properties ProductID OrderID ShipmentRequest Properties ProductID CreatedBy RequestDate Quantity

DamagedProduct

ReturnedQuantity ReturningDate Reason ReturnedBy

Properties ProductID Reason Date Quantity ReportedBy Customer

Supplier

Properties UsersMessage

Properties ID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone EMail Fax

Properties MsgID SourceUser DestinationUser SendingDate ReceivingDate

Shipment Properties

Message

ID OriginalQuantity CurrentQuantity ExpirationDate ProductID CreatedBy Date

Properties ID Body

Product Category Properties ID CategoryName Description PhotoPath

Properties ID Barcode ProductName Description SupplierID CategoryID UnitPrice UnitCost UnitsInStock UnitsOnOrder ReorderLevel RequestedQuantity ExpirationNotificationDays AllowReturnAfter IsTaxable

ID FirstName LastName Title Address City Region PostalCode Country Phone Mobile EMail Fax Notes

SIMS Database In the following section you can find the full specification of the 13 table’s database that represents the base of data storage shared between all the system components

1. Table : Categories Columns Name

Data Type

Length

int

4

CategoryName

nvarchar

30

Description

nvarchar

-1

PhotoPath

nvarchar

-1

ID

NULL

Default

IsIdentity

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Categories

Referencing Tables Table

Products

Foreign Key

Primary Key or Unique Constraint

FK_Products_Categories

PK_Categories

Objects that depend on table Categories Object Name

117

Object Type

Dep Level

Products

Table

1

DamagedProducts

Table

2

OrderDetails

Table

2

ReturnedProducts

Table

2

Foundation of Data | Sales and Inventory Management System

Shipment

Table

2

ShipmentRequest

Table

2

SQL CREATE TABLE [Categories] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Description] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PhotoPath] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO

5

Sales and Inventory Management System | Foundation of Data

118

2. Table: Customers Columns Name

Data Type

Length

int

4

FirstName

nvarchar

60

LastName

nvarchar

60

Title

nvarchar

60

Address

nvarchar

120

City

nvarchar

30

Region

nvarchar

30

PostalCode

nvarchar

20

Country

nvarchar

30

Phone

nvarchar

48

Mobile

nvarchar

48

EMail

nvarchar

100

Fax

nvarchar

48

Notes

nvarchar

-1

ID

NULL

Default

IsIdentity

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Customers

119

Foundation of Data | Sales and Inventory Management System

Referencing Tables Table

Orders

Foreign Key

Primary Key or Unique Constraint

FK_Customers_Orders

PK_Customers

Objects that depend on table Customers Object Name

Object Type

Dep Level

Orders

Table

1

OrderDetails

Table

2

ReturnedProducts

Table

2

SQL CREATE TABLE [Customers] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Mobile] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EMail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Notes] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO

Sales and Inventory Management System | Foundation of Data

5

120

3. Table: Damaged Prdoucts Columns Name

Data Type

ProductID

Length NULL Default IsIdentity

int

4

nvarchar

-1

smalldatetime

4

Quantity

int

4

ReportedBy

int

4

Reason Date

((1))

Identity column Name

Seed

Increment

1

1

ProductID

Indexes Index

Primary

Unique

PK_DamagedProducts

Referenced Tables Table

Foreign Key

Primary Key or Unique Constraint

Products FK_DamagedProducts_Products Users

FK_DamagedProducts_Users

PK_Products PK_Employees

Objects that DamagedProducts table depends on Object Name

Object Type

Dep Level

Categories

Table

1

Suppliers

Table

1

Users

Table

1

Products

Table

2

SQL

121

Foundation of Data | Sales and Inventory Management System

CREATE TABLE [DamagedProducts] ( [ProductID] [int] IDENTITY (1, 1) NOT NULL , [Reason] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Date] [smalldatetime] NOT NULL , [Quantity] [int] NULL CONSTRAINT [DF_DamagedProducts_Quantity] DEFAULT ((1)), [ReportedBy] [int] NOT NULL , CONSTRAINT [PK_DamagedProducts] PRIMARY KEY CLUSTERED ( [ProductID] ) ON [PRIMARY] , CONSTRAINT [FK_DamagedProducts_Products] FOREIGN KEY ( [ProductID] ) REFERENCES [Products] ( [ID] ), CONSTRAINT [FK_DamagedProducts_Users] FOREIGN KEY ( [ReportedBy] ) REFERENCES [Users] ( [ID] ) ) ON [PRIMARY] GO

5

Sales and Inventory Management System | Foundation of Data

122

4. Table: Messages Columns Name

Data Type

ID Body

Length NULL

int

4

nvarchar

-1

Default

IsIdentity

('This message is empty')

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Messages

Referencing Tables Table

UsersMessages

Foreign Key

Primary Key or Unique Constraint

FK_UsersMessages_Messages

PK_Messages

Objects that depend on table Messages Object Name

UsersMessages

Object Type

Dep Level

Table

1

SQL CREATE TABLE [Messages] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Body] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Messages_Message] DEFAULT ('This message is empty'), CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO

123

Foundation of Data | Sales and Inventory Management System

5. Table: OrderDetails Columns Name

Data Type

Length NULL Default

OrderID

int

4

ProductID

int

4

UnitPrice

float

8

((0))

Quantity

int

4

((1))

Discount

float

8

((0))

IsIdentity

Indexes Index

Primary

Unique

PK_Order_Details FK_Order_Details_Orders FK_Order_Details_Products

5 Check Constraints Name

Expression

CK_Discount

([Discount]>=(0) AND [Discount]<=(1))

CK_Quantity

([Quantity]>(0))

CK_UnitPrice

([UnitPrice]>=(0))

Referenced Tables Table

Foreign Key

Primary Key or Unique Constraint

Orders

FK_Order_Details_Orders

PK_Orders

Products

FK_OrderDetails_Products

PK_Products

Objects that table OrderDetails depends on Object Name

Categories

Object Type

Dep Level

Table

1

Sales and Inventory Management System | Foundation of Data

124

Customers

Table

1

Suppliers

Table

1

Users

Table

1

Orders

Table

2

Products

Table

2

SQL CREATE TABLE [OrderDetails] ( [OrderID] [int] NOT NULL , [ProductID] [int] NOT NULL , [UnitPrice] [float] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT ((0)), [Quantity] [int] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT ((1)), [Discount] [float] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT ((0)), CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED ( [OrderID], [ProductID] ) ON [PRIMARY] , CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY ( [OrderID] ) REFERENCES [Orders] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY ( [ProductID] ) REFERENCES [Products] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [CK_Discount] CHECK ([Discount]>=(0) AND [Discount]<=(1)), CONSTRAINT [CK_Quantity] CHECK ([Quantity]>(0)), CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice]>=(0)) ) ON [PRIMARY] GO

125

Foundation of Data | Sales and Inventory Management System

6. Table: Orders Columns Name

Data Type

Length NULL Default

ID

int

4

CustomerID

int

4

CreatedBy

int

4

OrderDate

smalldatetime

4

ShippedDate

smalldatetime

4

ShipVia

nvarchar

100

Freight

float

8

ShipName

nvarchar

80

ShipAddress

nvarchar

120

ShipCity

nvarchar

30

IsIdentity

((0))

5 ShipRegion

nvarchar

30

ShipPostalCode

nvarchar

20

ShipCountry

nvarchar

30

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Orders FK_Customers_Orders FK_Users_Orders

Referencing Tables

Sales and Inventory Management System | Foundation of Data

126

Table

ReturnedProducts OrderDetails

Foreign Key

Primary Key or Unique Constraint

FK_ReturnedProducts_Orders

PK_Orders

FK_Order_Details_Orders

PK_Orders

Referenced Tables Table

Customers Users

Foreign Key

Primary Key or Unique Constraint

FK_Customers_Orders

PK_Customers

FK_Users_Orders

PK_Employees

Objects that table Orders depends on Object Name

Object Type

Dep Level

Customers

Table

1

Users

Table

1

Objects that depend on table Orders Object Name

Object Type

Dep Level

OrderDetails

Table

1

ReturnedProducts

Table

1

SQL CREATE TABLE [Orders] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [CustomerID] [int] NULL , [CreatedBy] [int] NOT NULL , [OrderDate] [smalldatetime] NOT NULL , [ShippedDate] [smalldatetime] NULL , [ShipVia] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Freight] [float] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT ((0)), [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ,

127

Foundation of Data | Sales and Inventory Management System

CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ( [CustomerID] ) REFERENCES [Customers] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Users_Orders] FOREIGN KEY ( [CreatedBy] ) REFERENCES [Users] ( [ID] ) ) ON [PRIMARY] GO

5

Sales and Inventory Management System | Foundation of Data

128

7. Table: Products Columns Name

Data Type Length NULL Default IsIdentity

ID

int

4

Barcode

nvarchar

200

ProductName

nvarchar

80

Description

nvarchar

-1

SupplierID

int

4

CategoryID

int

4

UnitPrice

float

8

((0))

UnitCost

float

8

((0))

UnitsInStock

int

4

((0))

UnitsOnOrder

int

4

((0))

ReorderLevel

int

4

((0))

RequestedQuantity

int

4

((0))

ExpirationNotificationDays

int

4

AllowReturnAfter

int

4

((0))

IsTaxable

bit

1

((0))

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Products

129

Foundation of Data | Sales and Inventory Management System

Description

FK_Products_Categories FK_Products_Suppliers K_Barcode

Check Constraints Name

Expression

CK_Products_UnitPrice

([UnitPrice]>=(0))

CK_ReorderLevel

([ReorderLevel]>=(0))

CK_UnitsInStock

([UnitsInStock]>=(0))

CK_UnitsOnOrder

([UnitsOnOrder]>=(0))

Referencing Tables Table

ReturnedProducts OrderDetails

Foreign Key

Primary Key or Unique Constraint

FK_ReturnedProducts_Products

PK_Products

FK_OrderDetails_Products

PK_Products

5 ShipmentRequest Shipment DamagedProducts

FK_ShipmentRequest_Products

PK_Products

FK_Shipment_Products

PK_Products

FK_DamagedProducts_Products

PK_Products

Referenced Tables Table

Categories Suppliers

Foreign Key

Primary Key or Unique Constraint

FK_Products_Categories

PK_Categories

FK_Products_Suppliers

PK_Suppliers

Objects that table Products depends on Object Name

Object Type

Dep Level

Categories

Table

1

Suppliers

Table

1

Sales and Inventory Management System | Foundation of Data

130

Objects that depend on table Products Object Name

Object Type

Dep Level

DamagedProducts

Table

1

OrderDetails

Table

1

ReturnedProducts

Table

1

Shipment

Table

1

ShipmentRequest

Table

1

SQL CREATE TABLE [Products] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Barcode] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Description] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [SupplierID] [int] NULL , [CategoryID] [int] NOT NULL , [UnitPrice] [float] NOT NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT ((0)), [UnitCost] [float] NOT NULL CONSTRAINT [DF_Products_UnitCost] DEFAULT ((0)), [UnitsInStock] [int] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT ((0)), [UnitsOnOrder] [int] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT ((0)), [ReorderLevel] [int] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT ((0)), [RequestedQuantity] [int] NOT NULL CONSTRAINT [DF_Products_RequestedQuantity] DEFAULT ((0)), [ExpirationNotificationDays] [int] NULL , [AllowReturnAfter] [int] NOT NULL CONSTRAINT [DF_Products_AllowReturnAfter] DEFAULT ((0)), [IsTaxable] [bit] NOT NULL CONSTRAINT [DF_Products_SalesTax] DEFAULT ((0)), CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [K_Barcode] UNIQUE NONCLUSTERED ( [Barcode] ) ON [PRIMARY] , CONSTRAINT [FK_Products_Categories] FOREIGN KEY ( [CategoryID] ) REFERENCES [Categories] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY ( [SupplierID]

131

Foundation of Data | Sales and Inventory Management System

) REFERENCES [Suppliers] ( [ID] ) ON UPDATE CASCADE , CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice]>=(0)), CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel]>=(0)), CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock]>=(0)), CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder]>=(0)) ) ON [PRIMARY] GO

5

Sales and Inventory Management System | Foundation of Data

132

8. Table: ReturnedProducts Columns Name

Data Type

Length

ProductID

int

4

OrderID

int

4

ReturnedQuantity

int

4

smalldatetime

4

nvarchar

-1

int

4

ReturningDate Reason ReturnedBy

NULL

Default

IsIdentity

((0))

Indexes Index

Primary

Unique

PK_ReturnedProducts

Referenced Tables Table

Orders Products Users

Foreign Key

Primary Key or Unique Constraint

FK_ReturnedProducts_Orders

PK_Orders

FK_ReturnedProducts_Products

PK_Products

FK_ReturnedProducts_Users

PK_Employees

Objects that table ReturnedProducts depends on Object Name

Object Type

Dep Level

Categories

Table

1

Customers

Table

1

Suppliers

Table

1

Users

Table

1

Orders

Table

2

Products

Table

2

133

Foundation of Data | Sales and Inventory Management System

SQL CREATE TABLE [ReturnedProducts] ( [ProductID] [int] NOT NULL , [OrderID] [int] NOT NULL , [ReturnedQuantity] [int] NOT NULL CONSTRAINT [DF_ReturnedProducts_ReturnedQuantity] DEFAULT ((0)), [ReturningDate] [smalldatetime] NOT NULL , [Reason] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ReturnedBy] [int] NULL , CONSTRAINT [PK_ReturnedProducts] PRIMARY KEY CLUSTERED ( [ProductID], [OrderID] ) ON [PRIMARY] , CONSTRAINT [FK_ReturnedProducts_Orders] FOREIGN KEY ( [OrderID] ) REFERENCES [Orders] ( [ID] ), CONSTRAINT [FK_ReturnedProducts_Products] FOREIGN KEY ( [ProductID] ) REFERENCES [Products] ( [ID] ), CONSTRAINT [FK_ReturnedProducts_Users] FOREIGN KEY ( [ReturnedBy] ) REFERENCES [Users] ( [ID] ) ) ON [PRIMARY] GO

Sales and Inventory Management System | Foundation of Data

5

134

9. Table: Shipments Columns Name

Data Type

Length NULL Default

ID

int

4

OriginalQuantity

int

4

CurrentQuantity

int

4

ExpirationDate

smalldatetime

4

ProductID

int

4

CreatedBy

int

4

smalldatetime

4

Date

IsIdentity

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Shipment

Referenced Tables Table

Products Users

Foreign Key

Primary Key or Unique Constraint

FK_Shipment_Products

PK_Products

FK_Shipment_Users

PK_Employees

Objects that table Shipment depends on Object Name

135

Object Type

Dep Level

Categories

Table

1

Suppliers

Table

1

Foundation of Data | Sales and Inventory Management System

Users

Table

1

Products

Table

2

SQL CREATE TABLE [Shipment] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [OriginalQuantity] [int] NOT NULL , [CurrentQuantity] [int] NOT NULL , [ExpirationDate] [smalldatetime] NULL , [ProductID] [int] NOT NULL , [CreatedBy] [int] NOT NULL , [Date] [smalldatetime] NOT NULL , CONSTRAINT [PK_Shipment] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [FK_Shipment_Products] FOREIGN KEY ( [ProductID] ) REFERENCES [Products] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_Shipment_Users] FOREIGN KEY ( [CreatedBy] ) REFERENCES [Users] ( [ID] ) ON UPDATE CASCADE ) ON [PRIMARY] GO

Sales and Inventory Management System | Foundation of Data

5

136

10.Table: ShipmentRequests Columns Name

Data Type

Length NULL

Default

ProductID

int

4

CreatedBy

int

4

smalldatetime

4

(getdate())

int

4

((0))

RequestDate Quantity

IsIdentity

Indexes Index

Primary

Unique

PK_ShipmentRequest_1

Referenced Tables Table

Products Users

Foreign Key

Primary Key or Unique Constraint

FK_ShipmentRequest_Products

PK_Products

FK_ShipmentRequest_Users

PK_Employees

Objects that table ShipmentRequest depends on Object Name

Object Type

Dep Level

Categories

Table

1

Suppliers

Table

1

Users

Table

1

Products

Table

2

SQL CREATE TABLE [ShipmentRequest] ( [ProductID] [int] NOT NULL , [CreatedBy] [int] NULL , [RequestDate] [smalldatetime] NOT NULL CONSTRAINT [DF_ShipmentRequest_RequestDate] DEFAULT (getdate()), [Quantity] [int] NOT NULL CONSTRAINT [DF_ShipmentRequest_Quantity] DEFAULT ((0)), CONSTRAINT [PK_ShipmentRequest_1] PRIMARY KEY CLUSTERED

137

Foundation of Data | Sales and Inventory Management System

( [ProductID] ) ON [PRIMARY] , CONSTRAINT [FK_ShipmentRequest_Products] FOREIGN KEY ( [ProductID] ) REFERENCES [Products] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_ShipmentRequest_Users] FOREIGN KEY ( [CreatedBy] ) REFERENCES [Users] ( [ID] ) ON UPDATE CASCADE ) ON [PRIMARY] GO

5

Sales and Inventory Management System | Foundation of Data

138

11.Table: Suppliers Columns Name

Data Type

ID

Length NULL Default

int

4

CompanyName

nvarchar

80

ContactName

nvarchar

60

ContactTitle

nvarchar

60

Address

nvarchar

120

City

nvarchar

30

Region

nvarchar

30

PostalCode

nvarchar

20

Country

nvarchar

30

Phone

nvarchar

48

EMail

nvarchar

100

Fax

nvarchar

48

IsIdentity

Identity column Name

ID

Seed

Increment

1

1

Not for replication

Indexes Index

Primary

Unique

PK_Suppliers

Referencing Tables Table

Products

139

Foreign Key

Primary Key or Unique Constraint

FK_Products_Suppliers

PK_Suppliers

Foundation of Data | Sales and Inventory Management System

Objects that depend on table Suppliers Object Name

Object Type

Dep Level

Products

Table

1

DamagedProducts

Table

2

OrderDetails

Table

2

ReturnedProducts

Table

2

Shipment

Table

2

ShipmentRequest

Table

2

SQL CREATE TABLE [Suppliers] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EMail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO

Sales and Inventory Management System | Foundation of Data

140

5

12.Table: Users Columns Name

Data Type

Length

int

4

Username

nvarchar

40

LastName

nvarchar

40

FirstName

nvarchar

20

Title

nvarchar

60

BirthDate

smalldatetime

4

HireDate

smalldatetime

4

Address

nvarchar

120

City

nvarchar

30

Region

nvarchar

30

PostalCode

nvarchar

20

Country

nvarchar

30

HomePhone

nvarchar

48

MobilePhone

nvarchar

48

Notes

nvarchar

-1

IsSalesperson

bit

1

((0))

IsOnsiteOperator

bit

1

((0))

IsAdministrator

bit

1

((0))

Password

binary

32

IsLoggedIn

bit

1

smalldatetime

4

ID

LastLogin

141

NULL

Default

IsIdentity

((0))

Foundation of Data | Sales and Inventory Management System

Identity column Name

ID

Seed

Increment

1

1

Indexes Index

Primary

Unique

PK_Employees K_Users

Check Constraints Name

Expression

CK_Birthdate

([BirthDate]
Referencing Tables Table

Foreign Key

Primary Key or Unique Constraint

FK_ShipmentRequest_Users

PK_Employees

FK_Users_Orders

PK_Employees

FK_ReturnedProducts_Users

PK_Employees

UsersMessages

FK_UsersMessages_Users

PK_Employees

UsersMessages

FK_UsersMessages_Users1

PK_Employees

FK_Shipment_Users

PK_Employees

FK_DamagedProducts_Users

PK_Employees

ShipmentRequest Orders ReturnedProducts

Shipment DamagedProducts

5

Objects that depend on table Users Object Name

Object Type

Dep Level

DamagedProducts

Table

1

Orders

Table

1

Shipment

Table

1

ShipmentRequest

Table

1

Sales and Inventory Management System | Foundation of Data

142

UsersMessages

Table

1

OrderDetails

Table

2

ReturnedProducts

Table

2

SQL CREATE TABLE [Users] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Username] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [BirthDate] [smalldatetime] NULL , [HireDate] [smalldatetime] NULL , [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MobilePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Notes] [nvarchar] (0) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IsSalesperson] [bit] NOT NULL CONSTRAINT [DF_Users_IsSalesperson] DEFAULT ((0)), [IsOnsiteOperator] [bit] NOT NULL CONSTRAINT [DF_Users_IsAdministrator] DEFAULT ((0)), [IsAdministrator] [bit] NOT NULL CONSTRAINT [DF_Users_IsAdministrator_1] DEFAULT ((0)), [Password] [binary] (32) NULL , [IsLoggedIn] [bit] NOT NULL CONSTRAINT [DF_Users_IsLoggedIn] DEFAULT ((0)), [LastLogin] [smalldatetime] NULL , CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [K_Users] UNIQUE NONCLUSTERED ( [ID] ) ON [PRIMARY] , CONSTRAINT [CK_Birthdate] CHECK ([BirthDate]
143

Foundation of Data | Sales and Inventory Management System

13.Table: UsersMessages Columns Name

Data Type

Length NULL Default

MsgID

int

4

SourceUser

int

4

DestinationUser

int

4

SendingDate

smalldatetime

4

ReceivingDate

smalldatetime

4

IsIdentity

Indexes Index

Primary

Unique

PK_UsersMessages

Referenced Tables Table

Foreign Key

Primary Key or Unique Constraint

FK_UsersMessages_Messages

PK_Messages

Users

FK_UsersMessages_Users

PK_Employees

Users

FK_UsersMessages_Users1

PK_Employees

Messages

5

Objects that table UsersMessages depends on Object Name

Object Type

Dep Level

Messages

Table

1

Users

Table

1

SQL CREATE TABLE [UsersMessages] ( [MsgID] [int] NOT NULL , [SourceUser] [int] NOT NULL , [DestinationUser] [int] NOT NULL , [SendingDate] [smalldatetime] NOT NULL , [ReceivingDate] [smalldatetime] NULL , CONSTRAINT [PK_UsersMessages] PRIMARY KEY CLUSTERED (

Sales and Inventory Management System | Foundation of Data

144

[MsgID], [SourceUser], [DestinationUser] ) ON [PRIMARY] , CONSTRAINT [FK_UsersMessages_Messages] FOREIGN KEY ( [MsgID] ) REFERENCES [Messages] ( [ID] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_UsersMessages_Users] FOREIGN KEY ( [SourceUser] ) REFERENCES [Users] ( [ID] ), CONSTRAINT [FK_UsersMessages_Users1] FOREIGN KEY ( [DestinationUser] ) REFERENCES [Users] ( [ID] ) ) ON [PRIMARY] GO

145

Foundation of Data | Sales and Inventory Management System

In This Chapter

CHAPTER 6

Foundation of Security

Securing distributed systems that using a public network such as the Internet is a challenging task. Malicious hackers, identity fraudsters, and disgruntled employees cost organizations millions of dollars every year.

 Introduction  Foundation of Security in the Service Model  Service Model Security in Depth  Digital Certificates  Client Authentication  User Authentication

Introduction Security is one of the fundamental features of any software platform. In fact, security requirements have grown exponentially in the past decade because of the increasing popularity of public networks. Securing distributed systems that using a public network such as the Internet is a challenging task. Malicious hackers, identity fraudsters, and disgruntled employees cost organizations millions of dollars every year. As to be mentioned in the “Design Principles”, one of the major design principles of SIMS is scalability, where our system must be able to operate on many network types including public networks. This goal makes the system prone to many forms of malicious attacks by intruders. The modern distributed software architectures leverage public networks such as the Internet to send business sensitive information. This information can be compromised at various locations (such as by packet sniffing on the wire, malicious systems administrators at routing destinations, and so on). Therefore, the security in our system wasn’t just something done to showoff, it was first place necessity.

The Foundations of Security in the Service Model The security of the system depends on four main security services which are: Confidentiality Is the information confidential between the sender and the receiver? This feature will ensure that “unauthorized” parties do not get the opportunity to view the message contents. Integrity This feature ensures that the receiver of the message gets the same information that the sender sends without any data tampering. Machine Authentication This is to verify who the sender machine is and who the receiver machine is. Are they authenticated to send or receive data? User Authentication This service ensures that the user is known to the system and allowed to access the system data. 147

Foundation of Security | Sales and Inventory Management System

Service Model Security in Depth Security Mechanism: Message-Level Security SIMS service depends on SOAP messages to implement the mentioned security services. Message-level security relies on the message itself to secure the communication. The user authentication, machine authentication, integrity, and confidentiality are met using the message data.

What Is Message Security? Message security uses the WS-Security specification to secure messages. The specification describes enhancements to SOAP messaging to ensure confidentiality, integrity, and authentication at the SOAP message level (instead of the transport level). In brief, message security differs from transport security by encapsulating the security credentials and claims with every message along with any message protection (signing or encryption). Applying the security directly to the message by modifying its content allows the secured message to be self-containing with respect to the security aspects. This enables some scenarios that are not possible when transport security is used as described in later section.

Why Message-Level Security? In message-level security, all of the security information is encapsulated in the message. Securing the message with message-level security instead of transport-level security has the following advantages:  End-to-end security A secure transport, such as Secure Sockets Layer (SSL) works only when the communication is point-to-point. If the message is routed to one or more SOAP intermediaries before reaching the ultimate receiver, the message itself is not Sales and Inventory Management System | Foundation of Security

148

6

protected once an intermediary reads it from the wire. Additionally, the client authentication information is available only to the first intermediary and must be transmitted to the ultimate received in out-of-band fashion, if necessary. This applies even if the entire route uses SSL security between individual hops. Because message security works directly with the message and secures the XML in it, the security stays with the message regardless of how many intermediaries are involved with the message before it reaches the ultimate receiver. This enables true end-to-end security scenario. 

Increased flexibility Parts of the message, instead of the entire message, can be signed or encrypted. This means that intermediaries can view the parts of the message that are intended for them. If in later scenarios we needs to make part of the information in the message visible to the intermediaries but wants to ensure that it is not tampered with, it can just sign it but leave it unencrypted. Since the signature is part of the message, the ultimate receiver can verify that the information in the message was received intact. One scenario might have a SOAP intermediary service that routes message according the Action header value. By default, WCF does not encrypt the Action value but signs it if message security is used. Therefore, this information is available to all intermediaries, but no one can change it.



Support for multiple transports We can send secured messages over many different transports, such as named pipes and TCP, without having to rely on the protocol for security. With transport-level security, all the security information is scoped to a single particular transport connection and is not available from the message content itself. Message security makes the message secure regardless of what transport we use to transmit the message, and the security context is directly embedded inside the message.



Support for a wide set of credentials and claims The message security is based on the WS-Security specification, which provides an extensible framework capable of transmitting any type of claim inside the SOAP message. Unlike transport security, the set of authentication mechanisms, or claims, that we can use is not limited by the transport capabilities. WCF message security includes multiple types of authentication and claim transmission and can be extended to support additional types as necessary. For

149

Foundation of Security | Sales and Inventory Management System

those reasons, for example, a federated credentials scenario – which might be required by some organizations - is not possible without message security.

Message Level Security Extensibility As a proof-of-concept for the flexibility and extensibility we gain by using message level security, we have implemented a user access rule to the system which defines specific times during the day where user can login and use the system. This security feature was purposed and implemented in only two days! That’s illustrating the benefits of relying on messages to implement security services.

Confidentiality Confidentiality is implemented in SIMS Service by encrypting the SOAP messages body being sent between the server and clients using

Client Authentication using X509 Digital Certificates This section describes the client (machine) authentication process using X509 digital certificates in details. Before going on in this topic, we will give a brief introduction about digital certificates in the following paragraph. 6

Digital Certificates A digital certificate is a collection of data used to verify the identity of the holder or sender of the certificate. For example, an X.509 certificate contains such information as:         

Version Serial number Certificate issuer Certificate holder Validity period (the certificate is not valid before or after this period) Attributes, known as certificate extensions, that contain additional information such as allowable uses for this certificate Digital signature from the certification authority to ensure that the certificate has not been altered and to indicate the identity of the issuer Public key of the owner of the certificate Message digest algorithm used to create the signature Sales and Inventory Management System | Foundation of Security

150

The careful reader will have noticed that a digital signature indicates the certificate of the signer, and a certificate contains a digital signature, which indicates another certificate. In general, each certificate is verified through the use of another certificate, creating a chain of certificates that ends with the root certificate. The issuer of a certificate is called a certification authority (CA). The owner of the root certificate is the root certification authority. Figure 5-2 illustrates the anatomy of a digital certificate.

Figure 5-2: Anatomy of a digital certificate The root certificate is self-signed, meaning the signature of the root certificate was created by the root certification authority themselves. Figure 5-3 and Figure 5-4 illustrate how a chain of certificates is created and used. Figure 5-3 shows how the root certification authority creates its own certificate and then creates a certificate for a secondary certification authority.

151

Foundation of Security | Sales and Inventory Management System

6

Figure 5-3 Creating the certificates for the root CA and a secondary CA

Figure 5-4 shows how the secondary certification authority creates a certificate for an end user and how the end user uses it to sign a document.

Sales and Inventory Management System | Foundation of Security

152

Figure 5-4 Creating the certificate for an end user and signing a document with it

In Figure 5-4, the creator of the document has signed the document. The signature indicates the certificate of the document’s creator (labeled “User” in the figure). The document’s creator signs the document with a private key, and the signing certificate contains the corresponding public key, which can be used to decrypt the message digest to verify the signature. This certificate—together with the private and public keys—was provided by a certification authority (CA). In order to verify the validity of the user’s certificate, the certificate is signed using the certificate of the CA. The certificate of the CA includes the public key needed to decrypt the message digest of the user’s certificate. Continuing the certificate chain, the certificate of the CA is signed using the certificate of the authority who issued that certificate. The chain can go on through any number of intermediate certificates, but in Figure 5-3 the issuer of the CA’s certificate is the root certification authority. Note that the certificate of the root CA, unlike the

153

Foundation of Security | Sales and Inventory Management System

others, is “self signed.” That is, it does not refer to a further certification authority but is signed using the root CA’s own private key. When a CA creates a certificate, it uses its private key to encrypt the certificate’s message digest. The signature of every certificate the CA issues refer to its own signing certificate. The CA’s public key is in this certificate, and the application verifying the signature must extract this key to verify the certificate of the CA. So it continues, on down the certificate chain, to the certificate of the root CA. When a root CA issues a certificate, it, too, signs the certificate. However, this signing certificate was not issued by another CA; the chain stops here. Rather, the root CA issues its own signing certificate, as shown in Figure 5-3. The certificate of the root CA can be verified by creating a digest and comparing it with one widely available. Typically, the root certificate and root CA’s public key are already stored in the application or on the computer that needs to verify the signature. The confidence we can have in a given certificate depends on the confidence we have in the certificate authorities and in their procedures for ensuring that subsequent certificate recipients in the certificate chain are fully authenticated.

Client Authentication Process in Depth Participants  Client. The client accesses the Web service, and provides credentials for authentication during the request to the Web service.  Service. The service is the Web service that requires authentication of the client to make access control decisions. The process consist of two main parts, the first one where the client initializes and sends a message with x.509 certificate information, the second one where the service authenticates a client using the x.509 certificate and signature.

Part one: The Client Initializes and Sends a Message with X.509 Certificate Information This part of the process has six steps: 1. 2. 3. 4. 5. 6.

The client retrieves the service's X.509 certificate. The client retrieves its own certificate and private key. The client attaches its X.509 certificate to a message. The client signs the message using its private key. The client encrypts the message using the service's public key. The client sends the message to the service.

Sales and Inventory Management System | Foundation of Security

154

6

The steps are summarized in Figure 5-5.

Figure 5-5. Initializing and sending a message with X.509 certificate information Step One: The Client Retrieves the Service's Certificate The client needs to access the X.509 certificate of the service to encrypt the request message. The WCF service model policy assertion on the client is configured to retrieve the service's certificate from the client's local certificate store without the need for any additional code. Step Two: The Client Retrieves Its own X.509 certificate and Private Key The client accesses its X.509 certificate and private key. It uses the private key to sign the message and the X.509 certificate to provide the service with the public key and other information about the client for verification with the service. Step Three: The Client Attaches Its X.509 Certificate to a Message WCF service model policy is configured to sign the message, and WCF service model automatically attaches the client's certificate to the request message.

155

Foundation of Security | Sales and Inventory Management System

Step Four: The Client Signs the Message Using Its Private Key The client uses its private key to sign the message. We can choose to sign one or more portions of the message, such as the address header or the message body. At a minimum, we should sign the message body, security, and addressing headers. A signature is created using a signature algorithm that computes a checksum value from the data to be signed and then encrypts the checksum value with the client's private key. When the signature is validated, the data used to create the signature is also validated to provide data origin authentication. Step Five: The Client Encrypts the Message Using the Service's Public Key We can encrypt message parts using a symmetric key that is encrypted with the public key from the service's X.509 certificate. At a minimum, ensure that the signature used to sign the encrypted data is itself encrypted to help protect it against offline attacks. When we use WCF service model policy to encrypt message data with X.509 certificates, the policy uses asymmetric encryption to encrypt a one-time symmetric key, which in turn encrypts the data. When message data is encrypted using the service's certificate information, WCF service model also adds the certificate identifier to the message. If the certificate contains a subject key identifier, this is included to identify the certificate in the message. Otherwise, the policy uses the issuer name and certificate serial number instead. The service owns the certificate, which contains all the necessary information for it to access the appropriate private key and decrypt the symmetric key, which is then in turn used to decrypt the message. Encrypting the request in this way protects sensitive data if the client is deceived into calling an illegitimate service. As the intended message recipient, only the correct Web service can decrypt the message with its private key. Step Six: The Client Sends the Message to the Service After the message is signed and encrypted, the client sends it to the service.

Part Two: The Service Authenticates a Client Using the X.509 Certificate and Signature This part of the process has six steps: 1. 2. 3. 4. 5.

The service validates the client's certificate. The service verifies the certificate trust chain. The service checks the certificate revocation status. The service decrypts the message. The service verifies the signature. Sales and Inventory Management System | Foundation of Security

156

6

6. The service initializes and sends a response to the client (optional).

The steps are summarized in the Figure 5-6.

Figure 5-6. Authenticating a client using an X.509 certificate and signature

User Authentication Process in Depth Participants  Client. The client accesses the Web service. The client provides the credentials for authentication during the request to the Web service.  Service. The service is the Web service that requires authentication of a client prior to making access control decisions.  Identity store. The entity that stores a client's credentials which is a SQL 2005 database in our case.

Figure 5-7. The user authentication process The steps for this implementation are divided into two parts, based on what happens with the client and what happens with the service: 157

Foundation of Security | Sales and Inventory Management System

 

The client generates a Web service request. The service authenticates a client and returns a response.

Part One: The Client Generates a Web Service Request

This part of the process includes three steps: 1. Initialize the UsernameToken. 2. Establish message integrity. 3. Encrypt sensitive data in the message. The Service Authenticates the Client and Returns a Response

This part of the process has five steps: 1. 2. 3. 4. 5.

Decrypt the request message. Verify message integrity. Validate the password. Establish the response integrity Encrypt the response.

6

Sales and Inventory Management System | Foundation of Security

158

In This Chapter

CHAPTER 7

 log in the application  Ring a sale  Types of

Search

User’s Guide

 Web Administration Guide  Mobile client

This chapter aims to help the user of the system to discover its functionality and capabilities as well as guide him through the system to perform different tasks in step-by-step help topics

Point of Sales Module guide Log in How to log in the application?  Enter the name  Enter the password then press log in

Figure 7-1: login screen

160

Users Guide | Sales and Inventory Management System

Figure 7-2: sales screen

Ring a sale 7

Figure 7-3: ring a sale Sales and Inventory Management System |Users Guide

161



Ring a sale used to read any barcode.

As example above: Read barcode

Add product How to add any product to the application?

Figure 7-4: add product for sales screen

When you add product, follow the next steps: 1-enter the barcode of the product 2-enter the id of the product 3-press add

As example when we enter the barcode (201040079357) and the quantity (3) then presses add, all information about this product will appear

Figure 7-5: example 3 product with total quantity

162

Users Guide | Sales and Inventory Management System

Edit discount How to edit discount for this product?

Figure 7-6: edit discount for this product

As in example above: There was not any discount about the washing machine before, but now we make discount 20% for this product (washing machine). Note: when you choose numbers of discount press this button .

Edit quantity How to edit quantity of this product? 7

Figure 7-7: edit quantity of this product

As in example above: We can change the quantity of the product. Note :When you choose the number of quantity press on the button.

Sales and Inventory Management System |Users Guide

163

Add customer How to add customer?

Figure 7-8: add customer for seals screen  Insert personal information (Title - first name - last name)  Insert address & contact information (Address - city - region - country - phone - e-mail)  Insert order  Press to save

Edit customer How to edit customer?

Figure 7-9: edit customer for sales screen  Edit id  Edit order data

164

Users Guide | Sales and Inventory Management System

This message appear if you want to delete (Mac osx leopard) product  If you sure to delete the product press (ok)  If you not sure to cancel press(cancel)

Figure 7-10: delete product confirmation message

Quick search of product How to quick search for any product?

7

Figure 7-11: quick search of product for ring a sale 1-Press enters to open advanced search. 2-when you search of product insert any alphabetic if you not found it show message no result found.

Sales and Inventory Management System |Users Guide

165

Search products How to search for any product?

Figure 7-12: search product for ring a sale  If you know the full name of the product, write the name then press (search).  If you don’t know the full name the product, write only the first alphabet of the name of the product. Then press (search).  For see example press NEXT.

Search customers How to search for any customers?

Figure 7-13: search customer for ring a sale  If you know the full name of the customer, write the name then press (search).  If you don’t know the full name the customer, write only the first alphabet of the name of the customer. Then press (search). 166

Users Guide | Sales and Inventory Management System

As in example above: We found the first and the last name of the customer who we are search for (First name & Last name)

Quick search

7 Figure 7-14: quick search for seals screen 1- Ring a sale 2- Search with customers

Figure 7-15: quick customer search 3- Search with orders

Figure 7-16: quick order search 4- Search with products

Figure 7-17: quick product search Sales and Inventory Management System |Users Guide

167

5- Search with messages 6- Insert type to search (numeral-edge) and press button search 7- Press button to open advanced search 8- Minimized & maximized page

Search Screens  Customers search How to search for any customers?

Figure 7-18: search for customer  First step Insert type here to search (alphabetic-numeric)  Second step search using by advanced search

Result to search:

Figure 7-19: result search for customer

Using advanced search:

Figure 7-20: Add conditions for advanced search  Add conditions (1 to 5). 168

Users Guide | Sales and Inventory Management System

Figure 7-21: remove conditions for advanced search  If you want to remove any data press remove conditions.  Then press search.

Customers Advanced Search How to search for any customers using by advanced search?

7 Figure 7-22: display screen title for advanced customer search

 Display Screen Customers "Title" "First Name" "Last Name" "Address" "City" "Region" "Postal Code" "Country" "Phone" "Mobile" "Email" "Fax" "Notes"

Sales and Inventory Management System |Users Guide

169

Advanced search with change equal:

Figure 7-23: display screen equal for advanced customer search

 orders Search How to search for any orders?

Figure 7-24: search for order

Orders advanced search How to search for any order using by advanced search?

Figure 7-25: search with id for order  Search order using advanced search with id  Insert id (number or digit)

170

Users Guide | Sales and Inventory Management System

Show message invalid value indicator to less than or grater than to Digit:

Figure 7-26: invalid value indicator to grater than to digit for order

Search order using advanced search with order date manual:

7

Figure 7-27: search with order date manual for order  insert date manually

Sales and Inventory Management System |Users Guide

171

Search order using advanced search with order date calendar:

Figure 7-28: search with order date to show the calendar for order  press button to show the calendar

Search order using advanced search with ship name:

Figure 7-29: Search order using advanced search with ship name  Choose the conditions  Choose the equal

172

Users Guide | Sales and Inventory Management System

Order using advanced search with change title:

Figure 7-30: display screen title for advanced order search

 Display Screen Orders: "ID" "Order Date" "Shipped Date" "Ship Via" "Freight" "Ship Name" "Ship Address" "Ship City" "Ship Region" "Ship Postal Code" "Ship Country"

Sales and Inventory Management System |Users Guide

7

173

 Search product How to search for any product?

Figure 7-31: search for product  search product  insert type to search  press button to advanced search

Products advanced search: How to search for any products using by advanced search?

Figure 7-32: advanced search for products 1. Inter the barcode then press add condition 2. Press button to search

3. can you change (barcode) when you press inter it appear: "Barcode" "Product Name" "Description" "Unit Price" "Unit Cost" "Units In Stock" "Units On Order" "Reorder Level" "Requested Quantity" "Expiration Notification Days" "Allow Return After" "Is Taxable" 174

Users Guide | Sales and Inventory Management System



message screen How to search for message?

Figure 7-33: search for message  Press the button to different search Message with (body-sender-data)  Print search

-Show for example search message with body:

Figure 7-34: example search message with body - Message number: - Body: - sending Date: - receiving date: - Sender: - receive:

7

Save search message: How to save message search?

Figure 7-35: different Save message  Press button show different Save message with (Excel files – adobe acrobat – xml paper specification)

Sales and Inventory Management System |Users Guide

175

Web Administration Guide 1. Login a. To login, enter your Username & your Password to login b. You can also mark the checkbox to be remembered when you login later.

Figure 7-36: Login to screen

2. Sending Messages a. Select users you want to send them a message. b. Type your message, and then send it.

Figure 7-37: Sending Messages Screen

176

Users Guide | Sales and Inventory Management System

3. Products search a. To add new product with its details. b. To find a product with its details. c. You can also edit any product details or even delete it.

Figure 7-38: add & find product details

4. Suppliers search a. To add and save a supplier details. d. To find a supplier with its details. b. You can also edit any supplier details or even delete it.

7

Figure 7-39: add & save a supplier details

Sales and Inventory Management System |Users Guide

177

Figure 7-40: delete supplier’s details

178

Users Guide | Sales and Inventory Management System

Mobile client guide Login screen This is the screen where you enter your credentials to access the system  Username: the username provided to you by the system administrator Note: the username may be different than you actual name. 

Password: the password provided by the system administrator Note: Keep your password secret and don’t write it on any sticky notes Figure 7-41: Login Screen

7

You will see the following screen if your credentials are correct

Figure 7-42: Successful login screen

Sales and Inventory Management System |Users Guide

179

Tasks After logging successfully to the system, click “Tasks” menu and choose a task to perform. The application provide the following tasks  Search Customers  Search Products  Search Orders  Search Shipments  Add Order  Add Shipment  Add Customer 

Report Damaged Product

Figure 7-43: Tasks Menu

Credentials Error This notification shows up if the provided credentials is not correct, or the user is not allowed to access the system in the time of login. To solve this problem, contact the system administrator.

Figure 7-44: Credentials Error Message

180

Users Guide | Sales and Inventory Management System

Connection Error This notification shows up if there is a connection error; ensure that the mobile client has a connection to the inventory server

Figure 7-45: Connection Error Message

Search 7 The application provide search facility for the user to search one of the following system entities  Customers  Products  Shipments  Orders Search Property: the property to search by For example, to search for a product using it name as the search criteria, choose “Name” item from this list

Figure 7-46: search screen with name

Sales and Inventory Management System |Users Guide

181

Adding Customers The “ Add Customer” screen is divided into two main tabs. The First one prompts the user for the Title, First Name, Lasr Name, and any notes about the customer.

Figure 7-47: Add customer personal Information

The second screen prompts the user for the customer contact details

Figure 7-48: add address and contact details

Search for a product using Barcode image To get a product using its barcode, you can use the integrated camera in the device (if available). To do that, press on the blank text box and hold for a second, a menu item will appear as in the picture. Press “Add from Image…” and take a clear picture for the product barcode then press ok, then press “Search”.

182

Figure 7-49: Search using barcode image

Users Guide | Sales and Inventory Management System

Creating Order To create order, press the “Tasks” menu after login successfully, the next screen will show up. The “Create Order” screen is divided into two parts, “Order Products”, and “Order Information”. To add a product to the order, press “Add Product…” button which will open the product search screen. Choose the product then press choose button. Figure 7-50: Order Products

7

The other part of the screen is “Order Information” which includes the customer details (if found) and the order total due.

Figure 7-51: Order Details

Sales and Inventory Management System |Users Guide

183