Invoice Application using .Net

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

The following image is the final output of invoice application.

How to start?

Step 1 - Start from database design

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

Complete table structure

Script to create table and constraints

 CREATE TABLE [dbo].[MaHSN](
    [HSNId] [int] IDENTITY(1,1) NOT NULL,
    [HSNCode] [varchar](50) NOT NULL,
    [HSNDescription] [varchar](100) NULL,
    PRIMARY KEY CLUSTERED 
        (
            [HSNId] ASC
        ) 
    )

 CREATE TABLE [dbo].[MaTax](
    [TaxId] [int] IDENTITY(1,1) NOT NULL,
    [TaxName] [varchar](100) NOT NULL,
    [CGSTCaption] [varchar](50) NULL,
    [CGSTTaxRate] [numeric](18, 2) NULL,
    [SGSTCaption] [varchar](50) NULL,
    [SGSTTaxRate] [numeric](18, 2) NULL,
    [IGSTCaption] [varchar](50) NULL,
    [IGSTTaxRate] [numeric](18, 2) NULL,
    [UGSTCaption] [varchar](50) NULL,
    [UGSTTaxRate] [numeric](18, 2) NULL,
 PRIMARY KEY CLUSTERED 
     (
        [TaxId] ASC
     ) 
 ) ON [PRIMARY]

CREATE TABLE [dbo].[MaUnit](
[UnitId] [int] IDENTITY(1,1) NOT NULL,
[UnitDesc] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
    (
        [UnitId] ASC
    ) 
) ON [PRIMARY]

CREATE TABLE [dbo].[PrMaster](
   [PrId] [int] IDENTITY(1,1) NOT NULL,
   [PrCode] [varchar](50) NULL,
   [PrDesc] [varchar](500) NULL,
   [PrUnit] [varchar](50) NULL,
   [Pr_HSNId] [int] NULL,
   [Pr_UnitId] [int] NULL,
   [Pr_TaxID] [int] NULL,
   [PrOpenBalance] [numeric](18, 2) NULL,
   [PrPurchaseRate] [numeric](18, 2) NULL,
   [PrSalesRate] [numeric](18, 2) NULL,
    CONSTRAINT [PK_PrMaster] PRIMARY KEY CLUSTERED
   [PrId] ASC
 ON [PRIMARY]

ALTER TABLE [dbo].[PrMaster] WITH CHECK ADD FOREIGN KEY([Pr_HSNId]) REFERENCES [dbo].[MaHSN] ([HSNId])	This script create the relationship between the tables [dbo].[PrMaster] and [dbo].[MaHSN] using the key field HSNId and Pr_HSNId. Once the relationship is created, we can insert the value for Pr_HSNId in [dbo].[PrMaster] table and that value should be exist in the field HSNId in [dbo].[MaHSN] table. If you try insert the value, which is not mapped(exist) with the field HSNId in table [dbo].[MaHSN] the error will occur. So we cannot insert the record in the table [dbo].[PrMaster].
ALTER TABLE [dbo].[PrMaster] WITH CHECK ADD FOREIGN KEY([Pr_TaxID]) REFERENCES [dbo].[MaTax] ([TaxId])	This script create the relationship between the tables [dbo].[PrMaster] and [dbo].[MaTax] using the key field TaxId and Pr_TaxId. Once the relationship is created, we can insert the value for Pr_TaxId in [dbo].[PrMaster] table and that value should be exist in the field TaxId in [dbo].[MaTax] table. If you try insert the value, which is not mapped(exist) with the field TaxId in table [dbo].[MaTax] the error will occur. So we cannot insert the record in the table [dbo].[PrMaster].
ALTER TABLE [dbo].[PrMaster] WITH CHECK ADD FOREIGN KEY([Pr_UnitId]) REFERENCES [dbo].[MaUnit] ([UnitId])	This script create the relationship between the tables [dbo].[PrMaster] and [dbo].[MaUnit] using the key field UnitId and Pr_UnitId. Once the relationship is created, we can insert the value for Pr_UnitId in [dbo].[PrMaster] table and that value should be exist in the field UnitId in [dbo].[MaUnit] table. If you try insert the value, which is not mapped(exist) with the field UnitId in table [dbo].[MaUnit] the error will occur. So we cannot insert the record in the table [dbo].[PrMaster].
ALTER TABLE [dbo].[PrMaster] ADD DEFAULT ((0)) FOR [PrOpenBalance]	This is create the default constraint. If you fail to pass the value for the field PrOpenBalance, the defaul value "0" will be assigned.

What is Primary key?
What is Identity attribute?
How many Identity column can have a table?
How to reset the Identity value?
How to change the Identity sequence?
What happen when you execute the "Delete command"?
What happen when you execute the "Truncate table"?
How to insert the value for Identity column?
What happen if you miss the any value in the Identity sequence?
Can we assign Identity attribute to non-numeric/decimal column?
How to get last Identity value of a table?
@Identity vs Scope_Identity
What is Index?
What are the types of Index?
What is difference between varchar() and char()?

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]

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

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

Step 7 Invoice Tables

Invoice Tables 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#

Project Structure

Step 8 Front-end Design for Master and Transaction Module

Under Development

Step 9 Report Design

Add the report

Create new dataset

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..."

Step 11 Enter the Database details

Step 12 Select the data source

Step 13 Map the dataset with report

Mapped field structure

Step 14 Report designing area

Step 15 Page size & Margin setup

Right click on report area and select "Report Properties"

Step 16 Enable page header & page footer

Step 17 Report designing area with page header & page footer

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.

Step 19 Completely designed report

Step 20 Report Viewer

Integrate the report viewer to open the report

Step 21 Final Report