Create an Invoice application

Introduction

This is a simple billing project which was developed using C# and .Net, SQL Server 2014, Entity Framework 6.0, RDLC Report. You can use it for all type of invoice preparation after small modification.

Look at the following invoice before start and observe what is the information printed in the invoice. Some of the information is common to all invoices and some of the information is specific to the invoice. This information may be varying based on the industry or business type.

The following fields are collected from the invoices

Required invoice fields

The following image is the final output of invoice application.

final output invoice

How to start?

Step 1 - Start from database design

Create the Database

Create the database

Step 2 - Create the Product Table

I am going to create the Master table with necessary fields only. Later we can add more field if we need.

List of required field

Product Master

Complete table structure

Complete table structure

Script to create table and constraints

Script to create table and constraints

Step 3 - Insert the record

Insert into [dbo].[MaHSN](HSNCode,HSNDescription)Values('1006','RICE')

Insert into [dbo].[MaTax](TaxName,CGSTCaption,CGSTTaxRate,SGSTCaption,SGSTTaxRate)
Values('CGST 9% AND SGST 9%','CGST',9,'SGST',9)

Insert into [dbo].[MaUnit](UnitDesc)
Values('No(s).')

Select * from [dbo].[MaTax]
Select * from [dbo].[MaUnit]
Select * from [dbo].[MaHSN]

Insert into [dbo].[PrMaster](PrCode,PrDesc,Pr_HSNId,Pr_UnitId,Pr_TaxID,
    PrOpenBalance,PrPurchaseRate,PrSalesRate)
Values('1','BOILED RICE -5KG BAG',1,1,1,100,250,275)                           

Select * from [dbo].[PrMaster]
        
Insert script for product master table

Step 4 - Join the table

Select * from [dbo].[PrMaster] pr 
   inner join [dbo].[MaUnit] unit on pr.Pr_UnitId = unit.UnitId
   inner join [dbo].[MaHSN] hsn on pr.Pr_HSNId = hsn.HSNId
   inner join [dbo].[MaTax] tax on pr.Pr_TaxID = tax.TaxId
                
Join the table to read data from multiple tables

Insert one more record in Product table

 Insert into [dbo].[PrMaster](PrCode,PrDesc,Pr_HSNId,Pr_UnitId,Pr_TaxID,
    PrOpenBalance,PrPurchaseRate,PrSalesRate)
 Values('2','BOILED RICE -10KG BAG',1,1,1,100,450,480)
 
 Select * from [dbo].[PrMaster] pr 
    inner join [dbo].[MaUnit] unit on pr.Pr_UnitId = unit.UnitId
    inner join [dbo].[MaHSN] hsn on pr.Pr_HSNId = hsn.HSNId
    inner join [dbo].[MaTax] tax on pr.Pr_TaxID = tax.TaxId
 
Records from multiple table using join

Step 5 - Relationship or Association between tables

The HSNId from [dbo].[MaHSN] table which is 1, mapped or associated with two product which are "BOILED RICE -5KG BAG,BOILED RICE -10KG BAG"

The UnitId from [dbo].[MaUnit] table which is 1, mapped or associated with two product which are "BOILED RICE -5KG BAG,BOILED RICE -10KG BAG"

The TaxId from [dbo].[MaTax] table which is 1, mapped or associated with two product which are "BOILED RICE -5KG BAG,BOILED RICE -10KG BAG"

This relationship between the tables is called one-to-many relationship.

Step 6 Insert some more records

 Insert into [dbo].[MaHSN](HSNCode,HSNDescription)Values('230630','Cooking Oil')

 Insert into [dbo].[MaTax](TaxName,CGSTCaption,CGSTTaxRate,SGSTCaption,SGSTTaxRate)
 Values('CGST 5% AND SGST 5%','CGST',5,'SGST',5)

 Insert into [dbo].[MaUnit](UnitDesc)
 Values('Litre(s).')

 Insert into [dbo].[PrMaster](PrCode,PrDesc,Pr_HSNId,Pr_UnitId,Pr_TaxID,
     PrOpenBalance,PrPurchaseRate,PrSalesRate)
 Values('25','Sunflower Oil',2,2,2,10,120,130)

 Insert into [dbo].[MaHSN](HSNCode,HSNDescription)Values('3401','Bath Soap')

 Insert into [dbo].[MaTax](TaxName,CGSTCaption,CGSTTaxRate,SGSTCaption,SGSTTaxRate)
 Values('CGST 18% AND SGST 18%','CGST',18,'SGST',18)

 Insert into [dbo].[PrMaster](PrCode,PrDesc,Pr_HSNId,Pr_UnitId,Pr_TaxID,
     PrOpenBalance,PrPurchaseRate,PrSalesRate)
 Values('15','Cinthol',3,1,3,12,30,34)

 Select * from [dbo].[PrMaster] pr 
	inner join [dbo].[MaUnit] unit on pr.Pr_UnitId = unit.UnitId
	inner join [dbo].[MaHSN] hsn on pr.Pr_HSNId = hsn.HSNId
	inner join [dbo].[MaTax] tax on pr.Pr_TaxID = tax.TaxId
                
Required fields to create the invoice

Step 7 Invoice Tables

Sample invoice

Invoice Tables structure

Invoice table structure
CREATE TABLE [dbo].[MaParty](
	[PaID] [int] IDENTITY(1,1) NOT NULL,
	[PaName] [varchar](50) NULL,
	[PaAddress1] [varchar](200) NULL,
	[PaAddress2] [varchar](200) NULL,
	[PaAddress3] [varchar](200) NULL,
	[PaPINCode] [varchar](10) NULL,
	[PaGSTN] [varchar](50) NULL,
	[PaPAN] [varchar](25) NULL,
	[PaMailId] [varchar](100) NULL,
	[PaStateID] [int] NULL,
	[PaTypeID] [int] NULL,
	[PaMobileNo] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[PaID] ASC
))
go
CREATE TABLE [dbo].[InvoiceMain](
	[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceNo] [int] NOT NULL,
	[InvoiceDate] [datetime] NOT NULL,
	[InvoicePaID] [int] NOT NULL,
	[InvoiceGrossAmount] [numeric](18, 2) NOT NULL,
	[InvoiceTaxAmount] [numeric](18, 2) NOT NULL,
	[InvoiceRoundOff] [numeric](18, 2) NOT NULL,
	[InvoiceNetAmount] [numeric](18, 2) NOT NULL,
	[InvoiceInsertData] [datetime] NULL,
	[InvoiceStatus] [int] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[InvoiceId] ASC
	)
)
GO
ALTER TABLE [dbo].[InvoiceMain] ADD  DEFAULT ((1)) FOR [InvoiceStatus]
GO

ALTER TABLE [dbo].[InvoiceMain]  WITH CHECK ADD FOREIGN KEY([InvoicePaID])
REFERENCES [dbo].[MaParty] ([PaID])
CREATE TABLE [dbo].[InvoiceSub](
	[InvoiceSubId] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceId] [int] NOT NULL,
	[InvoicePrId] [int] NOT NULL,
	[InvoicePrQty] [numeric](18, 2) NOT NULL,
	[InvoicePrRate] [numeric](18, 2) NOT NULL,
	[InvoiceTaxId] [int] NOT NULL,
	[InvoiceCGSTAmount] [numeric](18, 2) NULL,
	[InvoiceSGSTAmount] [numeric](18, 2) NULL,
	[InvoiceIGSTAmount] [numeric](18, 2) NULL,
	[InvoiceUGSTAmount] [numeric](18, 2) NULL,
PRIMARY KEY CLUSTERED 
	(
		[InvoiceSubId] ASC
	)
)

ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD FOREIGN KEY([InvoiceId])
REFERENCES [dbo].[InvoiceMain] ([InvoiceId])
GO

ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD FOREIGN KEY([InvoicePrId])
REFERENCES [dbo].[PrMaster] ([PrId])
GO

ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD FOREIGN KEY([InvoiceTaxId])
REFERENCES [dbo].[MaTax] ([TaxId])

Step 7 Front-end development using C#

.Net Application selection Collecting .Net Application name

Project Structure

.Net project structure

Step 8 Front-end Design for Master and Transaction Module

Step 9 Report Design

Add the report

Add invoice report

Create new dataset

Create the dataset

Open the data source tool box

Open the data source tool box

Step 10 Right click on the tool box to open data source option. Select the "Add New Data Source..."

Add new data source
Add dataset to report Step 1
Add dataset to report Step 2

Step 11 Enter the Database details

Add dataset to report Step 3
Add dataset to report Step 4
Add dataset to report Step 5
Add dataset to report Step 6

Step 12 Select the data source

Select the data source

Step 13 Map the dataset with report

Map the dataset with report Step 1
Map the dataset with report Step 2

Mapped field structure

Map the dataset with report Step 3

Step 14 Report designing area

Complete report designing area

Step 15 Page size & Margin setup

Right click on report area and select "Report Properties"

Report page size and margin settings

Step 16 Enable page header & page footer

Report page header and footer settings

Step 17 Report designing area with page header & page footer

Report page header and footer settings

Report page height and width calculation

Paper Size is A4

Height = 29.7cm

Width = 21cm

Printable Area Height = Total Paper Height - (Top Margin + Bottom Margin)

29.7cm - (2cm+2cm) = 25.7cm(Including Header and footer)

Printable Area Width = Total Paper Width - (Left Margin + Right Margin)

21cm - (2cm+2cm) = 17cm

Header Section height = 3CM

footer Section height = 3CM

Details section height = Printable Area Height - (Header Section height + footer Section height)

Details section height = 25.7cm - (3cm+3cm)

Details section height = 19.7cm. If the height exceed than 25.7cm, the content moved to next page. You can identity this issue by export the report in PDF format and open the report using Acrobat reader. Follow the same procedure to calculate the page width.

Step 18 Designing the report.

Drag and drop the required filed such as text box, image and table in the respective report section

The Table control will repeat the row based on the number of record passed to the dataset.

Invoice designing

Step 19 Completely designed report

Completely designed invoice

Step 20 Report Viewer

Integrate the report viewer to open the report

Step 21 Final Report

Final invoice report