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.
Create the Database
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
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]
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
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
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"
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
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])
Right click on report area and select "Report Properties"
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.
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.