Create an Invoice application

Introduction

This is a simple billing project which was developed using using C#.Net, SQL Server 2014, Entity Fremework 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 are the information printed in the invoice. Some of the informations are common to all invoices and some of the information are specific to the invoice. These information may be vary 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 table 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, 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