Tuesday, December 24, 2013

Print the Invoice in Computer Stationery using Dot Matrix Printer


This post Explains how to Print the invoice in Pre-Print format(Continuous paper/Multipart forms).

Sample Format
Please go through my previous article before start.
The alignment & Scale process is same for Laser & Dot matrix Printer. Continuous Paper Feeding Method(Refer the Image)
  • Make sure your paper has a clean, straight leading edge. Open the sprocket covers. Fit the first holes of the paper over the sprocket pins and then close the sprocket covers. Slide the right sprocket to remove any slack in the paper and lock it in place.
  • Turn on the printer.
  • Press the "Load/Eject" button.
  • Now the paper will feed automatically and stop at corresponding position. You should not use "Knob/Roller" to adjust the position.
  • Adjust the Paper thickness lever. Because you may need to print multi-part paper.
Paper Type Lever Position
Standard Paper(Single Sheets or continuous) 0
Multipart Forms(Carbon Paper) 2-Sheet 0
3-Sheet 1
4-Sheet 2
5-Sheet 3
  • Set the Default Printer(Your Dot Matrix Printer)
  • Open Invoice Application
  • Open Invoice List Form(frmInvoiceList)
  • Add Command button and change the caption to "Print - Dot Matrix Printer"
  • Add following code in Command Click Event
  • Run the Application
  • Print the Invoice
  • Now the printer, print the invoice using Default printer.
  • Print 2 or 3 times a invoice and change the field position as per your requirement.

Print the Invoice in Pre-print Format using Crystal Report


This post Explains how to Print the invoice in Pre-Print format(The format is given below).



Step 1 : Add new Crystal report.

Step 2 : Specify the margin.
Scale the Margin from your Invoice template(Refer the below image).
Select & Specify the Paper size & Margin(Refer the below image)
(To open Page setup dialog box right on Crystal report->Design->Page Setup)
Step 3 : Drag & Drop the field from Field Explorer to Crystal Report.(Refer below Image)
Step 4 : Measurement Unit Settings(Optional).
Use the Ruler at left & top of report when place the report field. You can change the Measurement unit from Centimeter to Millimeter or vice versa(Refer below Image)
* US - Inches
* Metric - Centimeter

Change your measurement system as per your requirement and close and re-open the report in Visual Studio.
(To change Measurement Option - Control Panel -> Regional Settings - > Change Date, Time & Number formats)
I always prefer Metric unit(Centimeter). Because we can scale exact dimension and location in millimeter unit.
Preview the Report
Step 5 : Incorporate the report
Add a Command button in Invoice List form
Change the caption to "Print(A4 Pre-Print Format)"

Step 6 : Add below mentioned code in Click Event.
Step 7 : Preview the Report
Points to Remember
  • All points are measured in centimeter unit.
  • The alignment may vary at 1 to 2 millimeter level.
  • Before start to design the report,Fixed the Pager Size, Margin,font and Font size.
  • Take 1 or 2 test print and adjust the field position as per your requirement.
  • Ensure that You have installed Proper Printer Driver.

Sunday, April 28, 2013

Invoice,Billing Application(Software)


Introduction

This is a simple billing project which was developed using C# & SQL Server 2005. You can use it for all type of invoice/Bill preparation after small modification.
Download Project
Download SQL Script

Video in Youtube

Create Database
There are two ways to create a database.
         1. SQL Management Studio Query Analyser.
         2. SQL Management Studio Visual Tool.
The following sql script create a database with default option using SQL Management Studio Query Analyser.
CREATE DATABASE [MyBill]
(where MyBill is a name of database)
Open SQL Server Management Studio Express and execute the above mentioned query.
CreateDB
Create a database using SQL Server Management Studio Express
Right Click on Databases.
Select New Database... from the popup

Type your database name(Eg. "MyBill")
Select "OK"

Table Creation
Table Name Purpose
CompanyInfo Store the Company name & Address, it will be used in Invoice
PrMaster Store the Product details such as Product code, description, Rate, Unit etc.
InvoiceMain Store the invoice details such as Invoice No., Date, Customer Name & Address,
InvoiceSub Store the product details for corresponding invoice.
Script for Create the Table.
Copy the following script and paste in SQL Server Management studio Query Analyser. Press "F5" or Click "Run" button in the Tool bar.
        Use MyBill
go
CREATE TABLE [dbo].[InvoiceMain](
 [InvId] [int] IDENTITY(1,1) NOT NULL,
 [InvoiceNo] [int] NULL,
 [InvoiceDate] [datetime] NULL,
 [InvoiceCustomer] [varchar](200) NULL,
 [InvoiceAddress] [varchar](500) NULL,
 [InvoiceCustomerId] [varchar](50) NULL,
 [InvoiceChequeNo] [varchar](25) NULL,
 [InvoiceBank] [varchar](100) NULL,
 [InvChequeDate] [datetime] NULL,
 [InvNetAmount] [numeric](18, 2) NULL,
 [InvTaxNetAmount] [numeric](18, 2) NULL,
 CONSTRAINT [PK_InvoiceMain] PRIMARY KEY CLUSTERED 
(
 [InvId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvoiceSub](
 [RowId] [int] IDENTITY(1,1) NOT NULL,
 [InvId] [int] NULL,
 [PrId] [int] NULL,
 [PrQty] [numeric](18, 2) NULL,
 [PrRate] [numeric](18, 2) NULL,
 [PrTaxPercent] [numeric](18, 2) NULL,
 CONSTRAINT [PK_InvoiceSub] PRIMARY KEY CLUSTERED 
(
 [RowId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyInfo](
 [CompanyId] [int] IDENTITY(1,1) NOT NULL,
 [CompanyName] [varchar](100) NULL,
 [CompanyAddress1] [varchar](100) NULL,
 [CompanyAddress2] [varchar](100) NULL,
 [CompanyAddress3] [varchar](100) NULL,
 [CompanyPINCode] [varchar](50) NULL,
 [CompanyPhone1] [varchar](100) NULL,
 [CompanyTNGST] [varchar](50) NULL,
 [CompanyCST] [varchar](50) NULL,
 [CompanyTIN] [varchar](50) NULL,
 CONSTRAINT [PK_CompanyInfo] PRIMARY KEY CLUSTERED 
(
 [CompanyId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrMaster](
 [PrId] [int] IDENTITY(1,1) NOT NULL,
 [PrCode] [varchar](50) NULL,
 [PrDesc] [varchar](500) NULL,
 [PrUnit] [varchar](50) NULL,
 CONSTRAINT [PK_PrMaster] PRIMARY KEY CLUSTERED 
(
 [PrId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
Database Diagram(E-R Diagram)
Insert data into Companyinfo Table
       INSERT INTO [CompanyInfo]
           ([CompanyName]
           ,[CompanyAddress1]
           ,[CompanyAddress2]
           ,[CompanyAddress3]
           ,[CompanyPINCode]
           ,[CompanyPhone1]
           ,[CompanyTNGST]
           ,[CompanyCST]
           ,[CompanyTIN])
     VALUES
           ('My Company',
   'Address1',
   'Address2',
   'Address3',
   'PIN Code',
   'Phone No',
   'TNGST No',
   'CST No',
   'TIN'
   )
go
Select * from CompanyInfo
         

Insert Data in Product Master

Please refer the screen structure. I have designed this application in VS 2010 with C# Code. Below mentioned screen used to create Product.



Saved data in SQL Server.


Invoice/Bill Preparation

Please refer screen structure for Invoice/Bill.


Invoice List


Add - Create New Invoice.
Delete - Delete the selected Invoice.
Close - Close Invoice List.
Print - Print the selected Invoice.

Report Designing - Crystal Report.

Now I am going to design invoice/Billing application in  below mentioned format using Crystal Report.
Download Crystal report for Visual studio 2010


Add Crystal Report

Select Project Menu->Add New Item->Visual C# Items->Crystal Reports
Type the Report Name(MyInvoice.rpt)

Select "As a Blank Report"

Report Sections

Add fields to Report


Connect with Data source

Select View Menu->Server Explorer(Refer below screen)

Follow the images for data source connections.


Add table schema to Dataset

Drag & drop the "PrintInvoice" stored Procedure with "Invoicedataset.xsd"(Refer below screen)
PrintInvoice is a Stored Procedure which returns the necessary records for print the Invoice.

Place the field in Crystal Report.

Swith to MyInvoice.rpt Design mode.
Open Field Explorer.(If Field Explorer not visible Go to View Menu View->Other Windows->Document Outline)


Paper size & Margin Settings

Refer the below screen for Paper size & Margin settings.



Report Design


Place the fields in Report Area

Place the fields in Report(Refer the Image)


Preview the Report

Add new form and Place Crystal report viewer and name it CRV.

Change the DOCK Property of Crystal report viewer to Fill(Refer screen)

Switch to design view of frmInvoiceList.cs. Double click and write the following code in click Event


Refer the screen for Print the invoice.

  
private void btnPrint_Click(object sender, EventArgs e) {
            if (lstMain.SelectedItems.Count == 0)
            {
                MessageBox.Show("Please select the invoice...");
                return;
            }
            DataTable dt = new DataTable();
            string InvId;
            InvId = this.lstMain.SelectedItems[0].SubItems[6].Text;
            dt = Invoice.InvoicePrint(InvId);
            frmReportShow f = new frmReportShow();
            rptinvoice rpt = new rptinvoice();
            rpt.SetDataSource(dt);
            f.CRV.ReportSource = rpt;           
            f.ShowDialog();
        }

Print the Invoice(Refer Screen)


Invoice Output


Points to Remember
Just look at below screen.
Script of PrintInvoice stored Procedure
ALTER Proc [dbo].[PrintInvoice](@InvId int)
as
SELECT  dbo.InvoiceMain.InvId,
dbo.InvoiceMain.InvoiceNo,dbo.InvoiceMain.InvoiceDate, dbo.InvoiceMain.InvoiceCustomer,
dbo.InvoiceMain.InvoiceAddress, dbo.InvoiceMain.InvoiceCustomerId,
dbo.InvoiceMain.InvoiceChequeNo,dbo.InvoiceMain.InvoiceBank,
dbo.InvoiceMain.InvChequeDate, dbo.InvoiceMain.InvNetAmount,
dbo.InvoiceMain.InvTaxNetAmount,dbo.InvoiceSub.RowId,
dbo.InvoiceSub.PrId,dbo.InvoiceSub.PrQty,dbo.InvoiceSub.PrRate, 
dbo.InvoiceSub.PrTaxPercent,dbo.PrMaster.PrCode,
dbo.PrMaster.PrDesc,dbo.PrMaster.PrUnit,
dbo.CompanyInfo.CompanyName, dbo.CompanyInfo.CompanyAddress1,
dbo.CompanyInfo.CompanyAddress2,dbo.CompanyInfo.CompanyAddress3,
dbo.CompanyInfo.CompanyPINCode, dbo.CompanyInfo.CompanyPhone1,
dbo.CompanyInfo.CompanyTNGST, dbo.CompanyInfo.CompanyCST,
dbo.CompanyInfo.CompanyTIN,
dbo.AmountToWords(InvNetAmount)+' only.' as Amountinwords
FROM dbo.InvoiceMain 
INNER JOIN dbo.InvoiceSub ON dbo.InvoiceMain.InvId =
 dbo.InvoiceSub.InvId INNER JOIN
dbo.PrMaster ON dbo.InvoiceSub.PrId = 
dbo.PrMaster.PrId CROSS JOIN
dbo.CompanyInfo
where dbo.InvoiceMain.InvId=@InvId
order by rowId
The field InvId(Identity Column) is a primary key of InvoiceMain Table.
The field InvId is a Foreign key of InvoiceSub Table which used to create ONE TO MANY relationship between the tables.
The PrintInvoice stored Procedure used to print the invoice. When you execute this procedure it should return some records. If it is return Zero record, the blank report will open.
In invoice no 1, there are two items are used.
In invoice no 2, there are three items are used.

ER Diagram for Invoice Print The above ER Diagram show the relationship between the table. The CompanyInfo table cross join with other table.
The CompanyInfo table must have only one record. If it have more than one record, the duplicate records will be return byPrintInvoice stored procedure.
The CompanyInfo table is optional. I have stored the Company details in that table. You can directly type(static text) company details in Crystal Report using Text box control. After deployment of the application, you may need to change the company details. If you use the CompanyInfo table, just update the details in Corresponding field in CompanyInfo. It will reflect immediately in the report.
if you use the static text method, you have to again deploy the entire project.

If you want the source code, Please send the mail to mailtobabum@gmail.com

Troubleshoot

So many user post their comments. But they have written, the application returned blank reports. Please run the following query in your Sql Server management studio(Refer Image). All the query should return at least one row. Otherwise you will get the blank report. If any of the query return no records, kindly check whether the table has record or not.

select * from [dbo].[CompanyInfo]

select * from [dbo].[PrMaster]

select * from [dbo].[InvoiceMain] order by invid 

select * from [dbo].[InvoiceSub] order by invid 


SELECT  dbo.AmountToWords(InvNetAmount)+' only.' as Amountinwords,   dbo.InvoiceMain.InvId,
 dbo.InvoiceMain.InvoiceNo,
 dbo.InvoiceMain.InvoiceDate, dbo.InvoiceMain.InvoiceCustomer, dbo.InvoiceMain.InvoiceAddress, 
dbo.InvoiceMain.InvoiceCustomerId, dbo.InvoiceMain.InvoiceChequeNo, dbo.InvoiceMain.InvoiceBank,
 dbo.InvoiceMain.InvChequeDate, 
dbo.InvoiceMain.InvNetAmount, dbo.InvoiceMain.InvTaxNetAmount, dbo.InvoiceSub.RowId,
 dbo.InvoiceSub.PrId, dbo.InvoiceSub.PrQty, dbo.InvoiceSub.PrRate, 
dbo.InvoiceSub.PrTaxPercent, dbo.PrMaster.PrCode, dbo.PrMaster.PrDesc, dbo.PrMaster.PrUnit,
 dbo.CompanyInfo.CompanyName, 
dbo.CompanyInfo.CompanyAddress1, dbo.CompanyInfo.CompanyAddress2, dbo.CompanyInfo.CompanyAddress3,
 dbo.CompanyInfo.CompanyPINCode, 
dbo.CompanyInfo.CompanyPhone1, dbo.CompanyInfo.CompanyTNGST, dbo.CompanyInfo.CompanyCST,
 dbo.CompanyInfo.CompanyTIN
FROM dbo.InvoiceMain INNER JOIN
dbo.InvoiceSub ON dbo.InvoiceMain.InvId = dbo.InvoiceSub.InvId INNER JOIN
dbo.PrMaster ON dbo.InvoiceSub.PrId = dbo.PrMaster.PrId CROSS JOIN
dbo.CompanyInfo