Title: Develop Web ERP Software Using VS 2015
Table of Contents
The objective of this book is to teach the secrets of developing a Web
ERP software application using ASP.Net and C#.
Chapter : 1. Introduction to Visual Studio 2015
- Understanding .NET 2015 components
- .Net framework 4.6
- .Net Core 1.0
- Core CLR
- .Net Native
- ASP.NET Core 1.0
- Universal Windows apps
- ASP.Net 4.6
- Introduction to ASP.NET Web Forms
- Deciding when to create a Web Forms Application
- Advantages of a Web Forms-Based Web Application
- Working of ASP.Net Applications
- Advantages of an MVC-Based Web Application
- ASP.NET 4.5 features
- New features in ASP.NET Web Forms in .NET 4.6
- Creating ASP.Net 4.6 application in VS 2015
- Code behind page model
- ASP.NET Page Syntax
- Overview of ASP.Net server controls
- Using Labels and TextBox controls
- Using Button control, LinkButton and ImageButton
- How to wire multiple controls in a page to the same event handler
- Using ListBoxes and DropDown Lists
- Creating Multiple-Selection List boxes
- Get the selected Item in the single-selection ListBox control
- Get the selected Items in the multiple-selection ListBox control
- SelectedIndexChanged event of ListBox control
- Creating ListItem objects and adding them to a Control using the Add() method
- Removing items from a multiple-selection ListBox control
- Using CheckBoxes and RadioButtons
- Get the Selection in a RadioButton Control using Radio button click event
- Check Box control
- RadioButtonList and CheckBoxList controls
- Get selected items from the CheckBoxList control
- Validation Controls
- RequiredField Validator
- CompareValidator Control
- RangeValidator Control
- Regular Expression Validator Control
- CustomValidator Control
- ValidationSummary Control
- Using Validation Groups
Chapter : 2. Data Access in ASP.Net Application
- The SqlDataSource control
- How to Connect to a SQL Server Database Using the SqlDataSource Control
- Creating a SqlDataSource control declaratively
- Storing Connection String in Web.config file
- Executing Data Commands with the SqlDataSource Control
- Binding a ListBox Control to the SqlDataSource control
- Setting DataSouceMode property
- SqlDataSource control Events
- Using parameters with SqlDataSource control
- Properties of ASP.Net parameter objects
- SqlDataSource control Collections
- How to set SqlDataSource's parameters before the select command is executed
- Using the ASP.Net ControlParameter Object
- Overview of GridView control
- Properties of GridView control
- GridView Events
- GridView Methods
- Display, edit and delete data in the GridView control using SqlDataSource control
- Programmatic DataBinding in a GridView Control
- Formatting GridView control
- Formatting GridView control with CSS
- Using fields with the GridView control
- Using BoundFields
- How to Format date and currency column using DataFormatString property
- Using Command Fields
- Using Template Fields
- How to use Eval() and Bind() method
- Display a DropDownList when Editing in the GridView using EditItemTemplate
- How to insert new data into the database with GridView control using FooterTemplate
- How to restrict an item from being selected using GridView SelectedIndexChanging Event
- DetailsView and FormView control
- Using Fields in a DetailsView control
- Using style elements in a DetailsView control
- Creating a DetailsView control
- Data Binding with the DetailsView Control
- Using DataSourceID property
- Using DataSource property
- DetailsView Properties
- DetailsView Events
- Using Command buttons in DetailsView
- Using Templates in a DetailsView control
- How to create Master - Detail page by using GridView and DetailsView control
- FormView control
- FormView vs. DetailsView control
- How to use FormView control to update and insert data
Chapter : 3. ERP Domain
- Technologies for ERP software
- Platform suited for creating ERP software
- ERP software overview
- ERP software implementation issues
- Resources required for implementing ERP software
- Benefits of ERP software
- SAAS and ERP
- Role of softwares in an ERP system
- ERP software and standardization of business processes
- Working of the Finance department
- Working of the Sale department
- Working of the Purchase department
- Working of the Manufacturing department
- Working of the Inventory department
Chapter : 4. ERP Application Architecture
- Benefits of Multi-tier Architecture
- Purpose of Presentation Layer
- Purpose of Business Layer
- Purpose of Data Access Layer
- Purpose of Data Layer
- Creating Web ERP Application Framework using Visual Studio
- Creating a Class Library for the Data Access Layer
- Creating a Class Library for the Business Layer
- Creating a Web Application for the Presentation Layer
- Developing Data Access Layer in C#
- Creating DataServer Class
- Constructor of the DataServer class
- DataServer Class Methods
- Introduction to Master pages
- Creating a Master page for the ERP Web Application
- How to place a Menu control on a Master page
Chapter : 5. Developing Finance Module
- Business processes in Finance Module
- Presentation Layer of Finance Module
- Creation of Accounts
- Designing and Programming the AccountsDialog.aspx
- Managing ASP.Net Navigation using Server.Transfer method
- Send a parameter to another page using Server.Transfer method
- Designing and Programming the AccountsForm.aspx
- Bind the DropDownList control to a ObjectDataSource control
- Using SelectMethod property of ObjectDataSource
- TypeName property of ObjectDataSource
- OnSelecting property of ObjectDataSource
- Import the namespace of Business Layer
- Retrieve Query String Values in Page_Load() event using Request.QueryString
- Set ObjectDataSource's parameters using Selecting event - ObjectDataSource1_Selecting
- Save the Account Details
- Creating Business Object - Account.cs
- Retrieve Groups
- Steps to interact with the DataAccessLayer
- Save the Account details
- Connecting AccountForm to the menu
- Creating Customer, Supplier, Bank and General Account using the AccountsForm.aspx
- Chart of Accounts
- Designing and Programming Chart of Accounts.aspx
- Placing a TreeView control on a ChartofAccounts.aspx
- Populate the root level nodes in Page_Load() event
- Populate the child nodes of a given node
- Program the TreeNodePopulate event
- Creating Business Object - Group.cs
- Return main groups
- Return only sub-groups under a particular group
- Designing the JournalForm.aspx for the Journal Voucher Transaction
- Steps to design the JournalForm.aspx
- Create and set important properties and methods of the GridView control
- Create Template columns in the GridView control using asp:TemplateField element
- Footer Template
- Create a Template column for the AccountName column in the JV
- Create a Template column for the Debit column in the JV
- Create a Template column for the Credit column in the JV
- Create a Template column for 'Insert' Button
- Create a 'Delete' Command button and 'Edit' Command button
- Create EmptyDataTemplate to accept first row of data when datasource is empty
- How to place the ObjectDatasource control, Transaction number, Date and totals of Credit and Debit column amounts on the form
- How to insert multiple accounts in the GridView control
- Steps to program the JournalForm.cs
- Building the DataTable Structure for the GridView
- Program the Page_Load event to bind the DataTable to the GridView control
- Program the Add New Record button to add a new row of data to the GridView using EmptyDataTemplate
- Access the controls placed in the EmptyDataTemplate
- Program the Insert button to add a new row of data to the GridView using FooterTemplate
- Retrieve the values of controls (DropDownList and TextBox controls) which are in the Footer Template
- Retrieve existing accounts from the Session object and assign to DataTable
- Save the DataTable into a session
- To get the value from a particular cell, which is a TemplateField
- Program the Event handler for the EditCommand event
- protected void grdAccounts_RowEditing(object sender, GridViewEditEventArgs e)
- Program the Cancel button
- protected void grdAccounts_RowCancelingEdit(Object sender, GridViewCancelEditEventArgs e)
- Program the Event handler for the DeleteCommand event
- protected void grdAccounts_RowDeleting(object sender, GridViewDeleteEventArgs e)
- Delete a row from the DataTable
- Commit all the changes made to this DataTable
- Assign DataTable as DataSource to the GridView and bind the GridView
- To clear the selection of a row, we set the SelectedIndex property programmatically to -1
- Program the Update button
- protected void grdAccounts_RowUpdating(object sender,GridViewUpdateEventArgs e)
- Program the function which keeps a running total of the debit amounts of the accounts
- Program the procedure for checking Double Entry rule
- Program the Save button
- Designing and Programming Voucher/ Receipts Transactions
- Cash Voucher
- Cash Receipt
- Check Voucher
- Check Receipt
- Creating Business Object - Transaction.cs
- Return Cash and Bank Accounts
- Return Accounts based on menu selection
- Save transaction details and return boolean value if transaction updation is successful
Chapter : 6. Developing Sales and Purchase Module
- Business processes in Sales Module
- Design and Program the OrderForm.aspx for creating Sales orders and purchase orders
- Place the DropDownList control and bind it to the ObjectDataSource control to display customer names
- How to insert multiple items in the orderForm.aspx
- Perform Validations in order Form
- Save Sales order /Purchase orders
- Update Order details
- Program the function which calculates the order total
- Program the function for calculating order net amount
- Designing and Programming ordersGrid.aspx
- Pick and Pack Sales orders
- Pick Shipping orders
- Back orders
- Pending Purchase orders
- Designing and Programming InvForm.aspx
- Program the function which calculates the invoice total
- Program the procedure for checking Double Entry rule
- Program the Save button
- Validate the TextBox control placed in the Footer using TextChanged event
- Validate the TextBox control placed in the EmptyDataTemplate using TextChanged event
- Programming Purchase Invoice
- Programming Sale Invoice
- Programming the Cash Sales
- Programming the Cash Purchase
- Programming Purchase Returns
- Programming Sale Returns
- Business Object - Trade
- Retrieve Customer/Vendor Accounts
- Retrieve Trade Accounts from the database
- Retrieve Discount, Freight and Tax Accounts
- Saving the Transaction details
Chapter : 7. Developing Inventory Module
- Business processes in Inventory Module
- Designing and programming the ItemForm.aspx for creating different types of items - Raw Material, Consumable, Sub Assembly or a Finished Product
- Designing and programming the MaterialIssueForm.aspx to issue components to manufacture products
- Business Layer of Inventory Module
- Business Object - Item.cs
- Retrieve Stock Groups from database
- Retrieve Item types
- Save the Item details
- Business Object - Issue
- Return information about single Work order
- Saving Issue details
Chapter : 8. Developing the Manufacturing Module
- Business processes in Manufacturing Module
- Designing and Programming BOMForm.aspx for Creating Bill of Materials
- Designing and Programming WorkOrderForm.aspx for creating work orders
- Business Layer of Manufacturing Module
- Business Objects - BOM
- Return information about all Items
- Return list of Poducts
- Return Components for a parent Item
- Save Product Structure
- Create product structures using BOMForm.aspx
- Display product structures using BOMDisplay.aspx
- Business Objects - WorkOrder
- Return names of Products
- Return Information about Components and their Quantities which are required to
manufacture one parent item (product)
- Save Work Order details
Let us discuss a few learning objectives of this book
- Hierarchical classification of Accounts and Groups
- ERP Domain
- Role of ASP.Net GridView control in the Web forms to record business processes in a Web ERP software
- Validations in ERP software
- Essential skills to develop the ERP application using this book
- ERP modules
- Layered Design
- Reuse of software components
Hierarchical classification of General Ledger Accounts and Groups in the ERP system
Essentially, the Chart of accounts is a list of all the Accounts with their Accounting groups in an ordered form.
In the shown Chart of Accounts template, main groups and their subgroups are listed in a hierarchical order.
At the top level, groups are classified into two main sections. Groups of Balance Sheet and other is Groups of P/L statement.
The Chart of accounts given in this book is a comprehensive template and can be easily customized to any type of organization including adding ofadditional groups at any level.Note that it is an Accounting requirement that any new Account which is entered into the system should belong to a group or a subgroup under the given groups in the COA.
Software such as SAP, Microsoft Dynamics, Sage and Oracle Financials or any open source ERP havetheir own implementation of Chart of Accounts (COA). However, all of them adhere to a generally accepted principles. The flexibility to design a customized COA ensures that a company and its management can meet the requirements of the stakeholders.
In fact, a flexible, sophisticated Chart of Accounts is a key factor for the success of an ERP software.
What is the need to create hierarchicalclassification of General Ledger Accounts and Groups in an ERP system?
Classification of General Ledger Accounts under proper Group heads is essential for multiple reasons.
The most important reason is to extract detailed and meaningful Financial Statements which are seen by the various stakeholders such as the top management, investors, lending institutions and regulatory authorities.
The groups and subgroups are used to logically group different accounts. Additionally, the group category determines the format in which the accounts and their amounts should appear. It also determines whether a particular account should appear or not in the transactions and reports.
The next important reason for a proper COA is that correctly classified and stored data is vital as it is used by all other modules of the system.
A good classification system should be customizable for individual Country’s Accounting system needs.
In the book, you will learn, how to program creation of Customer accounts and Vendor accounts and other accounts listed in the Table of Contents. When a user creates Vendor account, the software will automatically create and place this account under Accounts Payable group. Similarly, the Customer accounts are created under Accounts Receivable group.
Need for enhancement
In every company’s existence, the need for enhancing the Chart of Accounts arises frequently as stakeholders demand particular information from time to time.
The Finance department may require a modified and enhanced classification of accounts to generate reports for a new project or increased lending.
An example of modification of COA is to group Customers data region-wise, district-wise and city-wise from a groupingwhich only is region-wise.
Another example is changing the way the stock is classified from a simpler classification to a detailed classification which can be raw material stock, finished goods stock and machinery spares stock etc.
Enhancing of Chart of Accounts is done by creating groups under the groups present in the COA template.
Extracting a detailed Financial Statements is possible only with a flexible and sophisticated COA classification.
A well designed Chart of accounts classification lays a good foundation for programmers to build sophisticated ERP systems. A poorly designed COAresults in a Financial module which can become a bottleneck for smooth functioning of the organization.
Most software projects involve a financial module as its core and pricing for such projects multiplies depending on the size of the project.
ERP projects with multiple modules require seamless integration with the Financial module.
Learning to build a Financial systemfor a project from the scratch is time consuming and a laborious task often requiring months of work. With proper guidance given in the book, building the Financial module becomes easy and integration with SCM, CRM, Purchase and Sales modules becomes seamless.
The Chart of Accounts givenin the book can be used for commercial projects.
Programming the COA can be seen from in three perspectives
- Database Schema
- Web Forms implementation
- Display Chart of Accounts
Building the COA begins with Database schema
Two tables, Groups table and Accounts table play a pivotal role in the implementation of Chart of accounts.
Setting up relationships between these two tables using keys is an important step to ensure that integrity of the Accounting transactions.
When a new group or an account is created, it should be automatically and correctly placed in the system.
You will learn this entire process in the Chapter 4 of this book.
Web Forms Implementation
While the COA can be sophisticated, the Web form which is used to record the creation of general ledger account or a group has to be simple and intuitive, so that end users find it easy to use the form.
This is made possible by the use of advanced features given ASP.Net, such as ASP.Net Server controls and Validation controls.
We use a web form to record creationof Customer Account.Typically Customer accounts have name, address, credit limit etc. We also need to specify to which group the account belongs to.
By default, all the Customer accounts belongto Accounts Receivable Group.
We also need to facilitate the system to place the Customer accounts to any of the Accounts Receivable sub groups.
The validations incorporated in this web form takes care of displaying proper groups in dropdown control of the Accounts Web Form.
A step by step explanation is given in the book to implement all of the above including creation of Account class, Constructor, Class methods to retrieve correct groups from the database, using methods in the Data access Layer and display them in the dropdown list control of Web form.
Display Chart of Accounts
To understand how accounts are classified under group heads in the ERP system, a tabular display of COA is not useful.
Accounts such as Customers, Vendors, Stock and Asset accounts and other Liability accounts have to be displayed in a hierarchical fashion so that relationship between each one of the above is clearly seen.
Using the TreeView control, you will learn how to display Chart of Accounts with groups and accounts in a hierarchical fashion clearly showing their parent groups.
As shown in the picture, when we expand the BALANCE SHEET node, the main groups under this group are displayed. Similarly, when we expand P/L Statement node, the main groups under this group are displayed.
The steps to program the COA is given in the Financial module of the book.
Hundreds of transactions occur in a company and an ERP system should be able to record all the various types of transactions.
A good ERP system can be created with the thorough knowledge of:
- Working of each department and its business processes
- Detailed analysis of each of the business process
- The data to be recorded in each and every business process
- What is the input given to the business process and the output expected
- Data entry forms to be designed to record each business process
- Sequence of business processes occurring in each department
- Reports generated.
ERP domain covers all of the above and is fully dealt with in the Chapter : 3.
The ERP schema is the logical organization of ERP data including the tables for the Financial Accounting module, Sales module, Purchase module and Stock module including relationships and Stored Procedures.
An ERP schema scriptis given to you with the books and when you run it, tables with data required to initialize the ERP schema system is created.
The created Schema will include default fixed groups and accounts and their relationships established to maintain a successful ERP system.
Module wise schema explanation given in the Chapter 4 gives a broad perspective so that you can relate business processes and the table structures in the database schema.
You can also customize the given ERP database Schemato add additional fields in the tables. For example, in the customer and sales invoice table, you may want to add additional fields to record varied data and extract in-depth reports.
Role of ASP.Net GridView control in the Web forms to record business processes in a Web ERP software
We use Web forms with GridView control extensively to implement transactions in the ERP application.
Data is recorded using simple fields such as DropDownList, Textbox controls, Date control and Radiobutton controls etc.
Along with these, GridView is used to record transactions which involve data with one-to-many and many-to-many relationships.
GridView control is used extensively in the application to design the Vouchers, Receipts, Sales invoice, Purchase Invoice and Orders.
A Sales Invoice in the ERP system involves data such as Invoice No, Date on which sales invoice raised, Customer and the multiple Stock Items sold.
In real time usage, multiple items are added using the Sales Invoice. This data can edited or deleted before and after saving the Sales Invoice transaction.
The need for recording multiple items arises as all transactions in an ERP system have to accept multiple rows of data items and their Quantities and Values. This can be managed easily with the GridView control.
The GridView control’sFooterTemplate, ItemTemplate, EditItemTemplate and InsertTemplate enables creation of sophisticated data entry formsand customizing them.All these are covered in chapter2:
Validations in ERP software
Why are validations are critical in software systems?
- To maintain the integrity of the system
- To ensure that data entry errors are minimized
- To speed up the process of the data entry
- To reduce the possibility of system failures
Validations can be enforced in the back-end, front-end or both
A good ERP implementation uses a combination of back-end and front-endvalidations.
Implementing validations for maintaining Double entry in the Financial module of the ERP system is a critical task.
An expert ERP programmer would know which validations have to be implemented at the front end and those which have to be implemented at the back end.
This topic is dealt with in detail in the book with clear programming examples.
One common method developers use to implement validations is by using Primary Key and Foreign Key constraints.
Foreign Key constraints ensure Referential integrity by checking any change in the Primary Key value which corresponds to the value in the Foreign Key constraint in another table.
Another way of implementing validations is by using Stored Procedures. Validations are essentially ERP business rules which are executed before insert, delete and update operations.
You will learn how to program and implement many ERP Stored procedures in the application.
You will also learn how to call SPs from the Data Access Layer.
One common method programmers use to perform validations in the front end is by using Web Forms. Validations in the web form are executed before data reaches the backend.
ASP.Net provides server control and validation controls to implement validations.
For example, DropDownList control is used to restrict data to be chosen and also restrict unwanted data display. This is a simple but very useful technique when designing the ERP user interface.
In a Sale Invoice form, Customer accounts existing in the Accounts Receivable group should only be displayed in the dropdown list. This ensures proper selection of Customer account in the Sale Invoice transaction by the user.
Implementing Double Entry requires extra validations as finances are involved.
In Financial Accounting transactions such as Vouchers, Receipts, many validations are implemented, one such validation is a minimum of two accounts should exists in the transaction, and another is both debit side totals and credit side totals should be equal and amounts should not be negative.
You will learn how to manage these business rules under the hood so that the user will not enter incorrect transactions.
Implementation of the above is clearly explained in the book.
Essential skills to develop the ERP application using this book are:
- Basics of C# and OOPs
- Relational database SQL server
- ASP.Net programming
- ERP domain
Before you start developing an ERP application, basics of C# and OOPs are essential.
Understanding of Relational databases like SQL server, creating database and Tables and writing stored procedures is also essential.
You will get thorough understanding all of the above in the book. Every ERP system has a solid backend like SQL server or Oracle.
Familiarity of using ASP.Net Data Access controls to access the data from the SQL server will be useful when developing the application.
ASP.Net Programming knowledge is also essential and can be learnt from this book itself in the initial chapters such as working with ASP.Net Server Controls and Validation Controls and Data Access controls.
All these are explained with examples to make it easier for you to develop the ERP application.
ERP modules cover implementationof User Interface, Validations, Business Functionality, Data Retrieval and Data Saving and Integration with other modules.
The five ERP modules are Finance, Purchase, Sales, Inventory and Manufacturing module.
While implementation of each module is important, integration of one module with the other is also equally important and is discussed in the respective chapters.
Of all these modules, implementing Financial module is crucial. The reason being all other modules interact with Financial module in real time.
Software Developers often take some timeto understand how to implementaFinancial system. This is because of lack of a background of Accounting.
Companies emphasize perfect Financial systemsto ensure monies are safe. Tracking expenses in all departments and income from sales department is vital.
A combination of accounting knowledge and technical skills to implement accounting rules is the foundation for creating a Financial module. Taking note of this, the books cover Financial module implementation in extensive detail.
Web based ERP software consists of three layers –browser based user interface implemented with ASP.Net 4.6, Business Layer and Data access layers are implemented in C#.
Implementing ERP system based on Layered architecture makes the system more scalable and flexible to change with business.
These layers are available as reusable components and implementing with other components makes easier to implement.
Business Layer implements the business processesthat occur in the ERP business. This includes many classes responsible for automating the execution of business processes.
Reuse of software components
The reuse benefits of components can be realized when you read the chapter on Web ERP Application Architecture.
This chapter gives the complete code and design of the Data access layer. The other two layers Business Layer and Presentation Layer are explained in the subsequent chapters.
Just as the other two layers, the Data access layeris builtfrom the scratch and covers Data access functionality.
The Data Access layer essentially containsC# classes and methods and handles communication with the underlying database for accessing and saving the data. The interface of this layer is designed so that it can be reused in other projects and is also easy to use.
Components communicates with each other seamlessly as the interface methods are well defined.
As shown in the picture, this component (layer) can be reused by other projects for implementing data access functionality.