Tutoril and sample code for Billing project
The most common type of business application using Visual Basic is Retail Billing / Invoicing Software. A Billing software are used in almost all retail business. Some examples are Grocery, medical shop, Electrical & Electronics appliance, Garments & Textiles showrooms, petrol pumps, Fruit stall, Hotels & Restaurants, etc.,
Visual Basic is the excellent platform to develop these kind of projects. Due to RAD (Rapid Application Development) capabilities and short learning curve everyone with basic programming knowledge can learn Visual Basic from the scratch and develop a software in short time. In this tutorial i will teach to design & develop a Windows application that can be used in a POS (Point of Sale) Terminal.
You need Visual Studio 2005 and Ms-Access 2003 or later version. Crystal reports is bundled with Visual Studio.
Though there are many forms required for sales bill we will discuss the main form, the sales bill entry. Below i have given the screenshot for a typical sales bill
Design the above form, color and design is not necessary at this step. At the top left there is a combo box. I call it archive selection. The listbox just below the archive box will show the saved bills as per selection in the combo box. If you select 1-100 in the combo box the list box will show only bills from 1 to 100. if you select 100-200 the list box will show only those bills.
On the right had side there is bill master details. Master details are those have only one record per bill. The bill number, customer name, bill date, are saved only one time. Below is the details grid that will capture bill details like product name, quantity, rate and amount.
To add transport charges, tax there is another grid below. Below that another grid to less discounts etc., At the bottom of the form there is text box for grand total. There are four command buttons named Clear, Save, Delete and Close. The function of these buttons are obvious and self explanatory.
Now the Coding. The complete source code for the form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
namespace EasyInventory
{
public partial class frmSales : Form
{
OleDbConnection con = new OleDbConnection();
DataTable tblGRN = new DataTable();
DataTable tblAdd = new DataTable();
DataTable tblLess = new DataTable();
DataTable tblArchive = new DataTable();
int interval = 100;
string table_main = "Sales_mas";
int zone = -1;
public frmSales(int Zone)
{
zone = Zone;
InitializeComponent();
grid.CellValueChanged += new DataGridViewCellEventHandler(grid_CellValueChanged);
grid.CellLeave += new DataGridViewCellEventHandler(grid_CellLeave);
gridAdd.CellValueChanged += new DataGridViewCellEventHandler(gridExp_CellValueChanged);
gridLESS.CellValueChanged += new DataGridViewCellEventHandler(gridLESS_CellValueChanged);
Environment.CurrentDirectory = Application.StartupPath;
con = Globals.GetConnection();
if (con == null)
{
this.Close();
}
}
void grid_CellLeave(object sender, DataGridViewCellEventArgs e)
{
}
void gridLESS_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 1)
SetTotal();
}
void gridExp_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 1)
SetTotal();
}
void grid_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 0)
{
string matid = grid.Rows[e.RowIndex].Cells[0].Value.ToString();
string sql = "SELECT UOMID FROM Material WHERE ID =" + matid + " AND ZoneID=" + this.zone;
OleDbDataAdapter adp = new OleDbDataAdapter(sql, con);
DataTable table = new DataTable();
adp.Fill(table);
if (table.Rows.Count > 0)
{
int bal = (int)table.Rows[0]["UomID"];
if (bal > 0)
grid.Rows[e.RowIndex].Cells[1].Value = bal;
}
sql = "SELECT B.Rate FROM Material A INNER JOIN OpeningStock B ON A.ID=B.MaterialID AND A.ZoneID=B.ZoneID " +
" WHERE A.ID =" + matid + " AND A.ZoneID=" + this.zone;
adp.SelectCommand.CommandText = sql;
table = new DataTable();
adp.Fill(table);
if (table.Rows.Count > 0)
{
Decimal bal = (Decimal)table.Rows[0]["Rate"];
grid.Rows[e.RowIndex].Cells[3].Value = bal;
}
else
grid.Rows[e.RowIndex].Cells[3].Value = 0;
}
if (e.ColumnIndex == 2 | e.ColumnIndex == 3)
{
try
{
Decimal qty = Decimal.Parse(grid.Rows[e.RowIndex].Cells[2].Value.ToString());
Decimal rate = Decimal.Parse(grid.Rows[e.RowIndex].Cells[3].Value.ToString());
grid.Rows[e.RowIndex].Cells[4].Value = qty * rate;
SetTotal();
}
catch
{
}
}
}
private void SetTotal()
{
Decimal total = 0;
for (int i = 0; i < grid.Rows.Count - 1; i++)
{
total += (Decimal)grid.Rows[i].Cells[4].Value;
}
txtTotalAmount.Text = total.ToString("0.00");
for (int i = 0; i < gridAdd.Rows.Count - 1; i++)
{
total += (Decimal)gridAdd.Rows[i].Cells[1].Value;
}
for (int i = 0; i < gridLESS.Rows.Count - 1; i++)
{
total -= (Decimal)gridLESS.Rows[i].Cells[1].Value;
}
txtGrandTotal.Text = total.ToString("0.00");
}
private void ClearForm()
{
lstList.SelectedIndex = -1;
txtNo.Text = GetNextID().ToString();
dTPDate.Value = DateTime.Now;
txtRemarks.Text = "";
optCredit.Checked = true;
this.FillLookup();
this.cboCustomer.SelectedIndex = -1;
this.cboZone.SelectedIndex = -1;
cboPurchaseAc.SelectedIndex = -1;
InitGrid();
InitGridAdd();
InitGridLess();
tblGRN.Clear();
tblAdd.Clear();
tblLess.Clear();
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT MaterialID,UomID," +
"Quantity,Rate,Amount,Remarks FROM sales_det WHERE ID=" + txtNo.Text + " AND ZoneID="+zone.ToString(),con);
adp.Fill(tblGRN);
grid.DataSource = tblGRN;
adp.SelectCommand.CommandText ="SELECT LedgerID,Amount FROM sales_ADD WHERE SalesID=" + txtNo.Text + " AND ZoneID="+zone.ToString();
adp.Fill(tblAdd);
gridAdd.DataSource = tblAdd;
adp.SelectCommand.CommandText = "SELECT LedgerID,Amount FROM sales_Less WHERE SalesID=" + txtNo.Text + " AND ZoneID=" + zone.ToString();
adp.Fill(tblLess);
gridLESS.DataSource = tblLess;
}
private void FillLookup()
{
string sql = "SELECT ID,Description FROM acc_ledgers WHERE under=27";
if (optCash.Checked)
sql = "SELECT ID,Description FROM acc_ledgers WHERE under=27";
else if (optCredit.Checked)
sql = "SELECT ID,Description FROM acc_ledgers WHERE under=26";
OleDbDataAdapter adp = new OleDbDataAdapter(sql, con);
DataTable table = new DataTable();
adp.Fill(table);
cboCustomer.DataSource = table;
cboCustomer.DisplayMember = "Description";
cboCustomer.ValueMember = "ID";
adp.SelectCommand.CommandText = "SELECT ID,ZoneName FROM ZoneBlock WHERE ID=" + zone.ToString();
DataTable table2 = new DataTable();
adp.Fill(table2);
cboZone.DataSource = table2;
cboZone.DisplayMember = "ZoneName";
cboZone.ValueMember = "ID";
sql="SELECT ID,Description FROM acc_ledgers WHERE under=10";
adp.SelectCommand.CommandText = sql;
DataTable table3 = new DataTable();
adp.Fill(table3);
cboPurchaseAc.DataSource = table3;
cboPurchaseAc.DisplayMember = "Description";
cboPurchaseAc.ValueMember = "ID";
}
private void SaveRecord()
{
if (IsValidInput())
{
int zoneid = -1;
if (cboZone.SelectedIndex != -1)
zoneid = (int)cboZone.SelectedValue;
tblGRN.AcceptChanges();
tblAdd.AcceptChanges();
OleDbTransaction transaction = null;
try
{
if (lstList.SelectedIndex == -1)
{
int id = GetNextID();
string vid = GetNextIDVoucher().ToString();
OleDbCommand CMD = new OleDbCommand("SELECT tid FROM transIDByDate WHERE zoneid=" + zoneid.ToString() + " AND DateDiff('d',RefDate,'" + dTPDate.Text + "')=0", con);
//OleDbCommand CMD = new OleDbCommand("SELECT tid FROM transID WHERE zoneid=" + zoneid.ToString() , con);
object tid = CMD.ExecuteScalar();
int TID=1;
if (tid != DBNull.Value)
TID = Convert.ToInt32(tid) + 1;
transaction = con.BeginTransaction();
string strSql = "INSERT INTO sales_mas VALUES (" + id.ToString() + ",'" + dTPDate.Value.ToShortDateString() +
"'," + cboZone.SelectedValue.ToString() + "," + cboCustomer.SelectedValue.ToString() +
",'" + txtNo.Text + "'," + optCash.Checked + "," + cboPurchaseAc.SelectedValue.ToString() + "," + this.txtGrandTotal.Text + "," + TID.ToString() + ",'" + txtRemarks.Text + "'," + vid + ")";
OleDbCommand cmd = new OleDbCommand(strSql, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
for (int i = 0; i < tblGRN.Rows.Count; i++)
{
strSql = "INSERT INTO sales_det VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblGRN.Rows[i]["MaterialID"].ToString() + "," + tblGRN.Rows[i]["UOMID"].ToString() + "," + tblGRN.Rows[i]["Quantity"].ToString() + "," + tblGRN.Rows[i]["Rate"].ToString() + "," + tblGRN.Rows[i]["Amount"].ToString() + ",'" + tblGRN.Rows[i]["Remarks"].ToString() + "')";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
for (int i = 0; i < tblAdd.Rows.Count; i++)
{
strSql = "INSERT INTO sales_ADD VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblAdd.Rows[i]["LedgerID"].ToString() + "," + tblAdd.Rows[i]["Amount"].ToString() + ")";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
for (int i = 0; i < tblLess.Rows.Count; i++)
{
strSql = "INSERT INTO sales_Less VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblLess.Rows[i]["LedgerID"].ToString() + "," + tblLess.Rows[i]["Amount"].ToString() + ")";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
SaveVoucher(transaction,cmd,vid);
transaction.Commit();
FillArchive();
lstList.SelectedValue = id;
}
else
{
int id = Convert.ToInt32(lstList.SelectedValue);
string vid = GetVoucherID(id).ToString();
transaction = con.BeginTransaction();
string strSql = "UPDATE sales_mas SET SalesAc=" + cboPurchaseAc.SelectedValue.ToString() +
", IsCash=" + optCash.Checked + ", SalesDate='" + dTPDate.Value.ToShortDateString() +
"',CustomerID=" + cboCustomer.SelectedValue.ToString() + ",ZoneID=" + cboZone.SelectedValue.ToString() +
",Remarks='" + txtRemarks.Text + "',TotAmount=" + txtGrandTotal.Text + " WHERE ID=" + id.ToString() + " AND ZoneID=" + zone.ToString();
OleDbCommand cmd = new OleDbCommand(strSql, con);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE * FROM sales_det WHERE ID=" + id.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
for (int i = 0; i < tblGRN.Rows.Count; i++)
{
strSql = "INSERT INTO sales_det VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblGRN.Rows[i]["MaterialID"].ToString() + "," + tblGRN.Rows[i]["UOMID"].ToString() + "," + tblGRN.Rows[i]["Quantity"].ToString() + "," + tblGRN.Rows[i]["Rate"].ToString() + "," + tblGRN.Rows[i]["Amount"].ToString() + ",'" + tblGRN.Rows[i]["Remarks"].ToString() + "')";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
cmd.CommandText = "DELETE * FROM sales_ADD WHERE SalesID=" + id.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
for (int i = 0; i < tblAdd.Rows.Count; i++)
{
strSql = "INSERT INTO sales_ADD VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblAdd.Rows[i]["LedgerID"].ToString() + "," + tblAdd.Rows[i]["Amount"].ToString() + ")";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
cmd.CommandText = "DELETE * FROM sales_LESS WHERE SalesID=" + id.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
for (int i = 0; i < tblLess.Rows.Count; i++)
{
strSql = "INSERT INTO sales_Less VALUES(" + id.ToString() + "," + cboZone.SelectedValue.ToString() + "," + tblLess.Rows[i]["LedgerID"].ToString() + "," + tblLess.Rows[i]["Amount"].ToString() + ")";
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
}
SaveVoucher(transaction, cmd, vid);
transaction.Commit();
//FillList();
lstList.SelectedValue = id;
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
try
{
transaction.Rollback();
}
catch
{
}
}
}
}
private int GetVoucherID(int vid)
{
string sql ="SELECT VoucherNo FROM sales_mas WHERE id=" + vid;
OleDbCommand cmd = new OleDbCommand(sql, con);
object id = cmd.ExecuteScalar();
if (id != System.DBNull.Value)
{
int ID = Convert.ToInt32(id);
return ID;
}
else
{
return 1;
}
}
private void SaveVoucher(OleDbTransaction transaction,OleDbCommand cmd, string id)
{
string sql = "DELETE FROM acc_vouchers_mas WHERE id=" + id + " AND VoucherID=5";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "DELETE FROM acc_vouchers_det WHERE id=" + id + " AND VoucherID=5";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "INSERT INTO acc_vouchers_mas VALUES(" + id + ",5,'" + dTPDate.Text + "','" + txtNo.Text + "'," + optCash.Checked + ",'',NULL,'" + txtRemarks.Text + "')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
int sorder = 0;
sql = "INSERT INTO acc_vouchers_det VALUES(" + id + "," + cboPurchaseAc.SelectedValue.ToString() + ",5,1,0," + this.txtGrandTotal.Text + "," + sorder.ToString() + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sorder++;
sql = "INSERT INTO acc_vouchers_det VALUES(" + id + "," + cboCustomer.SelectedValue.ToString() + ",5,0," + txtGrandTotal.Text + ",0," + sorder.ToString() + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
for (int i = 0; i < tblAdd.Rows.Count; i++)
{
sorder++;
sql = "INSERT INTO acc_vouchers_det VALUES(" + id.ToString() + "," + tblAdd.Rows[i]["LedgerID"].ToString() + ",5,0," + tblAdd.Rows[i]["Amount"].ToString() + ",0," + sorder.ToString()+")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
for (int i = 0; i < tblLess.Rows.Count; i++)
{
sorder++;
sql = "INSERT INTO acc_vouchers_det VALUES(" + id.ToString() + "," + tblLess.Rows[i]["LedgerID"].ToString() + ",5,1,0," + tblLess.Rows[i]["Amount"].ToString() + "," + sorder.ToString() + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
private int GetNextIDVoucher()
{
OleDbCommand cmd = new OleDbCommand("SELECT MAX(ID) FROM acc_vouchers_mas WHERE VoucherID=5", con);
object id = cmd.ExecuteScalar();
if (id != System.DBNull.Value)
{
int ID = Convert.ToInt32(id);
ID++;
return ID;
}
else
{
return 1;
}
}
private bool IsValidInput()
{
if (cboZone.SelectedIndex == -1)
{
MessageBox.Show("Please Select a Zone");
cboZone.Focus();
return false;
}
try
{
Decimal tot = Decimal.Parse(txtTotalAmount.Text);
}
catch
{
MessageBox.Show("Enter valid total");
txtTotalAmount.Focus();
return false;
}
return true;
}
private void DeleteRecord()
{
if (lstList.SelectedIndex != -1)
{
if (MessageBox.Show("Are you sure to Delete this record?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string vid = GetVoucherID((int)lstList.SelectedValue).ToString();
OleDbTransaction transaction = con.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("DELETE * FROM sales_mas WHERE ID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString(), con);
cmd.Transaction = transaction;
int res = cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM sales_det WHERE ID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM sales_ADD WHERE SalesID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM sales_LESS WHERE SalesID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
cmd.ExecuteNonQuery();
string sql = "DELETE FROM acc_vouchers_mas WHERE id=" + vid + " AND VoucherID=5";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "DELETE FROM acc_vouchers_det WHERE id=" + vid + " AND VoucherID=5";
transaction.Commit();
if (res > 0)
MessageBox.Show("Deleted!");
FillArchive();
}
}
}
private void FillForm()
{
try
{
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM sales_mas WHERE ID=" + lstList.SelectedValue.ToString() + " AND ZoneID="+zone.ToString(), con);
DataTable table = new DataTable();
adp.Fill(table);
txtNo.Text = table.Rows[0]["ID"].ToString();
dTPDate.Value = (DateTime)table.Rows[0]["SalesDate"];
cboZone.SelectedValue = (int)table.Rows[0]["Zoneid"];
optCash.Checked = (bool)table.Rows[0]["IsCash"];
optCredit.Checked = !(bool)table.Rows[0]["IsCash"];
FillLookup();
Application.DoEvents();
cboZone.SelectedValue = (int)table.Rows[0]["Zoneid"];
cboCustomer.SelectedValue = (int)table.Rows[0]["CustomerID"];
cboPurchaseAc.SelectedValue = (int)table.Rows[0]["SalesAc"];
txtRemarks.Text = table.Rows[0]["Remarks"].ToString();
txtTotalAmount.Text = table.Rows[0]["TotAmount"].ToString();
tblGRN.Clear();
adp.SelectCommand.CommandText = "SELECT MaterialID,UOMID,Quantity,Rate,Amount,Remarks FROM sales_det WHERE ID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
adp.Fill(tblGRN);
grid.DataSource = tblGRN;
tblAdd.Clear();
adp.SelectCommand.CommandText = "SELECT LedgerID,Amount FROM sales_ADD WHERE SalesID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
adp.Fill(tblAdd);
this.gridAdd.DataSource = tblAdd;
tblLess.Clear();
adp.SelectCommand.CommandText = "SELECT LedgerID,Amount FROM sales_Less WHERE SalesID=" + lstList.SelectedValue.ToString() + " AND ZoneID=" + zone.ToString();
adp.Fill(tblLess);
this.gridLESS.DataSource = tblLess;
SetTotal();
}
catch (Exception er)
{
//MessageBox.Show(er.Message);
}
}
private void FillList()
{
try
{
if (cboArchive.SelectedIndex != -1)
{
int archiveIndex = (int)cboArchive.SelectedValue;
DataRow foundRow = tblArchive.Rows.Find(archiveIndex);
int from = (int)foundRow["From"];
int to = 0;
string sql="";
if (foundRow["to"] == DBNull.Value)
sql = "SELECT ID FROM " + table_main + " WHERE ZoneID=" + zone.ToString() + " AND ID>=" + from.ToString() + " ORDER BY ID";
else
sql = "SELECT ID FROM " + table_main + " WHERE ZoneID=" + zone.ToString() + " AND ID>=" + from.ToString() + " AND ID<=" + foundRow["to"].ToString() + " ORDER BY ID";
OleDbDataAdapter adp = new OleDbDataAdapter(sql, con);
DataTable table = new DataTable();
adp.Fill(table);
lstList.DataSource = table;
lstList.ValueMember = "ID";
lstList.DisplayMember = "ID";
}
}
catch
{
}
}
private int GetNextID()
{
OleDbCommand cmd = new OleDbCommand("SELECT MAX(ID) FROM sales_mas WHERE ZONEID=" + zone.ToString(), con);
object id = cmd.ExecuteScalar();
if (id != System.DBNull.Value)
{
int ID = Convert.ToInt32(id);
ID++;
return ID;
}
else
{
return 1;
}
}
private void InitGrid()
{
grid.Columns.Clear();
DataGridViewComboBoxColumn cboItem = new DataGridViewComboBoxColumn();
DataGridViewComboBoxColumn cboUOM = new DataGridViewComboBoxColumn();
DataGridViewTextBoxColumn txtQuantity = new DataGridViewTextBoxColumn();
DataGridViewTextBoxColumn txtRate = new DataGridViewTextBoxColumn();
DataGridViewTextBoxColumn txtAmount = new DataGridViewTextBoxColumn();
DataGridViewTextBoxColumn txtRemarks = new DataGridViewTextBoxColumn();
cboItem.DataPropertyName = "MaterialID";
cboItem.HeaderText = "ITEM NAME";
cboUOM.DataPropertyName = "UomID";
cboUOM.HeaderText = "UOM";
txtQuantity.DataPropertyName = "Quantity";
txtQuantity.HeaderText = "Quantity";
txtRate.DataPropertyName = "Rate";
txtRate.HeaderText = "Rate";
txtAmount.DataPropertyName = "Amount";
txtAmount.HeaderText = "Amount";
txtRemarks.DataPropertyName = "Remarks";
txtRemarks.HeaderText = "Remarks";
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT ID,Material FROM Material WHERE Zoneid="+zone.ToString()+" ORDER BY Material", con);
DataTable table1 = new DataTable();
adp.Fill(table1);
adp.SelectCommand.CommandText = "SELECT ID,Symbol FROM UOM WHERE Zoneid="+zone.ToString()+" ORDER BY Symbol";
DataTable table2 = new DataTable();
adp.Fill(table2);
cboItem.DataSource = table1;
cboItem.DisplayMember = "Material";
cboItem.ValueMember = "ID";
cboUOM.DataSource = table2;
cboUOM.DisplayMember = "Symbol";
cboUOM.ValueMember = "ID";
cboItem.DefaultCellStyle.Font = new Font("TscArial", 12);
grid.Columns.Add(cboItem);
grid.Columns.Add(cboUOM);
grid.Columns.Add(txtQuantity);
grid.Columns.Add(txtRate);
grid.Columns.Add(txtAmount);
grid.Columns.Add(txtRemarks);
txtQuantity.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
txtRate.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
txtAmount.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
txtQuantity.DefaultCellStyle.Format = "0.000";
txtRate.DefaultCellStyle.Format = "0.00";
txtAmount.DefaultCellStyle.Format = "0.00";
cboItem.Width = 350;
cboUOM.Width = 60;
txtQuantity.Width = 60;
txtRate.Width = 50;
txtAmount.Width = 90;
txtRemarks.Width = 200;
}
private void InitGridAdd()
{
gridAdd.Columns.Clear();
DataGridViewComboBoxColumn cboLedger = new DataGridViewComboBoxColumn();
DataGridViewTextBoxColumn txtAmount = new DataGridViewTextBoxColumn();
cboLedger.DataPropertyName = "LedgerID";
cboLedger.HeaderText = "Ledger";
txtAmount.DataPropertyName = "Amount";
txtAmount.HeaderText = "Amount";
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT ID, Description FROM acc_ledgers WHERE under IN (13,15,20) ORDER BY Description", con);
DataTable table1 = new DataTable();
adp.Fill(table1);
cboLedger.DataSource = table1;
cboLedger.DisplayMember = "Description";
cboLedger.ValueMember = "ID";
//cboItem.DefaultCellStyle.Font = new Font("TscArial", 12);
gridAdd.Columns.Add(cboLedger);
gridAdd.Columns.Add(txtAmount);
txtAmount.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
txtAmount.DefaultCellStyle.Format = "0.00";
cboLedger.Width = 430;
txtAmount.Width = 90;
}
private void InitGridLess()
{
gridLESS.Columns.Clear();
DataGridViewComboBoxColumn cboLedger = new DataGridViewComboBoxColumn();
DataGridViewTextBoxColumn txtAmount = new DataGridViewTextBoxColumn();
cboLedger.DataPropertyName = "LedgerID";
cboLedger.HeaderText = "Ledger";
txtAmount.DataPropertyName = "Amount";
txtAmount.HeaderText = "Amount";
OleDbDataAdapter adp = new OleDbDataAdapter("SELECT ID, Description FROM acc_ledgers WHERE under IN (15) ORDER BY Description", con);
DataTable table1 = new DataTable();
adp.Fill(table1);
cboLedger.DataSource = table1;
cboLedger.DisplayMember = "Description";
cboLedger.ValueMember = "ID";
//cboItem.DefaultCellStyle.Font = new Font("TscArial", 12);
gridLESS.Columns.Add(cboLedger);
gridLESS.Columns.Add(txtAmount);
txtAmount.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
txtAmount.DefaultCellStyle.Format = "0.00";
cboLedger.Width = 430;
txtAmount.Width = 90;
}
private void btnClear_Click(object sender, EventArgs e)
{
ClearForm();
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveRecord();
}
private void btnDelete_Click(object sender, EventArgs e)
{
DeleteRecord();
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
private void frmSales_Load(object sender, EventArgs e)
{
FillArchive();
ClearForm();
this.Top = 0;
this.Left = 0;
}
private void FillArchive()
{
tblArchive = new DataTable();
DataColumn ID = new DataColumn("ID", System.Type.GetType("System.Int32"));
DataColumn From = new DataColumn("From", System.Type.GetType("System.Int32"));
DataColumn To = new DataColumn("To", System.Type.GetType("System.Int32"));
DataColumn FromTo = new DataColumn("FromTo", System.Type.GetType("System.String"));
DataColumn[] keys = new DataColumn[1];
keys[0] = ID;
tblArchive.Columns.Add(ID);
tblArchive.Columns.Add(From);
tblArchive.Columns.Add(To);
tblArchive.Columns.Add(FromTo);
tblArchive.PrimaryKey = keys;
OleDbCommand cmd=new OleDbCommand("SELECT MAX(ID) FROM " + table_main +" WHERE Zoneid="+zone.ToString(),con);
object Max = cmd.ExecuteScalar();
int max = 1;
if (Max != System.DBNull.Value)
max = Convert.ToInt32(Max);
max = max - (max % interval);
int cnt = 1;
for (int i = 1; i <= max; i+=interval)
{
DataRow row = tblArchive.NewRow();
row["ID"] = cnt;
row["From"] = i;
row["To"] = i + interval-1;
row["FromTo"] = row["From"].ToString()+ "-" + row["To"].ToString();
tblArchive.Rows.Add(row);
cnt++;
}
DataRow row1 = tblArchive.NewRow();
row1["ID"] = cnt;
row1["From"] = (interval*(cnt-1))+1;
row1["To"] = DBNull.Value;
row1["FromTo"] = row1["From"].ToString() + "-";
tblArchive.Rows.Add(row1);
cboArchive.DataSource = tblArchive;
cboArchive.DisplayMember = "FromTo";
cboArchive.ValueMember = "ID";
cboArchive.SelectedIndex = - 1;
cboArchive.SelectedIndex = cnt-1;
}
private void lstList_SelectedIndexChanged(object sender, EventArgs e)
{
FillForm();
}
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
ReportDocument doc = new ReportDocument();
string strRepName = "";
strRepName = Application.StartupPath + @"\Reports\SalesBill.rpt";
doc.FileName = strRepName;
string strFilter = " WHERE A.ZoneID=" + this.zone;
string sql = " SELECT RefNo, SalesDate AS BillDate, C.Material AS ITEM, " +
" D.Symbol AS UOM, B.Quantity, B.Rate,B.Amount,TotAmount FROM (((Sales_mas A " +
" INNER JOIN sales_det B ON A.ID=B.ID AND A.ZoneID=B.ZoneID) " +
" INNER JOIN Material C ON B.MaterialID=C.ID AND B.ZoneID=C.ZoneID) " +
" INNER JOIN UOM D ON B.UOMID=D.ID AND B.ZoneID=D.ZoneID) " +
" WHERE A.ZoneID=" + this.zone + " AND A.ID=" + lstList.SelectedValue.ToString();
OleDbDataAdapter adp = new OleDbDataAdapter(sql, con);
DataTable table = new DataTable();
adp.Fill(table);
doc.Load(strRepName);
doc.SetDataSource(table);
DataTable tblAdd = new DataTable();
adp.SelectCommand.CommandText = "SELECT Description AS Ledger, Amount FROM sales_add A " +
" INNER JOIN acc_ledgers B ON A.LEdgerID=B.ID WHERE A.SalesID=" + lstList.SelectedValue.ToString() +
" AND A.ZoneID=" + this.zone.ToString();
adp.Fill(tblAdd);
DataTable tblLess = new DataTable();
adp.SelectCommand.CommandText = "SELECT Description AS Ledger, Amount FROM sales_Less A " +
" INNER JOIN acc_ledgers B ON A.LEdgerID=B.ID WHERE A.SalesID=" + lstList.SelectedValue.ToString() +
" AND A.ZoneID=" + this.zone.ToString();
adp.Fill(tblLess);
//doc.OpenSubreport("Add");
//doc.OpenSubreport("Less");
//OpenSubreport(doc, "Add", tblAdd);
doc.Subreports[0].Database.Tables[0].SetDataSource(tblAdd);
//doc.Subreports[0].SetDataSource(tblAdd);
doc.Subreports[1].SetDataSource(tblLess);
DataTable tblAddress = new DataTable();
sql = "SELECT * FROM Company";
adp.SelectCommand.CommandText = sql;
adp.Fill(tblAddress);
doc.SetParameterValue("CompName", "");
doc.SetParameterValue("CompAddress", "");
doc.SetParameterValue("AmountInWords", "");
sql= "SELECT IsCash FROM Sales_mas WHERE ZoneID=" + this.zone + " AND ID=" + lstList.SelectedValue.ToString();
OleDbCommand cmd = new OleDbCommand(sql, con);
bool iscash = (bool)cmd.ExecuteScalar();
string customer = "";
if (iscash)
customer = "CASH BILL";
else
{
sql = "SELECT B.Description,Address1, Address2,City,State,PIN,phone,TIN,CST" +
" FROM (sales_mas A INNER JOIN acc_ledgers B ON A.CustomerID=B.ID) " +
" INNER JOIN acc_ledgerdetails C ON B.ID=C.LedgerID WHERE A.ZoneID=" + this.zone + " AND A.ID=" + lstList.SelectedValue.ToString();
DataTable tbl = new DataTable();
adp.SelectCommand.CommandText = sql;
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
customer = tbl.Rows[0]["Description"].ToString() + '\n' + tbl.Rows[0]["Address1"].ToString() +
'\n' + tbl.Rows[0]["Address2"].ToString() + '\n' + tbl.Rows[0]["City"].ToString() +
'\n' + tbl.Rows[0]["State"].ToString() + '\n' + tbl.Rows[0]["PIN"].ToString();
customer = customer.Trim();
}
}
doc.SetParameterValue("Customer", customer);
try
{
doc.SetParameterValue("CompName", tblAddress.Rows[0]["CompName"]);
doc.SetParameterValue("CompAddress", tblAddress.Rows[0]["Address"]);
Double TotAmount = Convert.ToDouble(table.Rows[0]["TotAmount"]);
MPowerPrinting.DraftPrint draftprint = new MPowerPrinting.DraftPrint();
doc.SetParameterValue("AmountInWords", "Rupees " + draftprint.ConvToText(ref TotAmount) + " only");
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
frmReportViewer rep = new frmReportViewer(doc);
rep.MdiParent = this.MdiParent;
rep.Show();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
public void OpenSubreport(ReportDocument Report, string reportObjectName, DataTable table)
{
string subreportName;
SubreportObject subreportObject;
ReportDocument subreport = new ReportDocument();
// Get the ReportObject by name and cast it as a
// SubreportObject.
subreportObject = Report.ReportDefinition.ReportObjects
[reportObjectName] as SubreportObject;
if (subreportObject != null)
{
// Get the subreport name.
subreportName = subreportObject.SubreportName;
// Open the subreport as a ReportDocument.
subreport = Report.OpenSubreport(subreportName);
subreport.Database.Tables[0].SetDataSource(table);
}
}
private void cboArchive_SelectedIndexChanged(object sender, EventArgs e)
{
FillList();
}
private void grid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void optCash_CheckedChanged(object sender, EventArgs e)
{
FillLookup();
}
private void optCredit_CheckedChanged(object sender, EventArgs e)
{
FillLookup();
}
}
}
//end of source code
When the form loads FillArchive() and ClearForm() are called. This will fill the combo box for archive. You can understand the logic. Clear form will clear all fields and make the form ready for input. Clear form also calls IntiGrid() which will Initialize the grid and set datasource. It will add combo box for selecting products. It will fetch products from Products table and UOM from UOM table. Also customer combo box data is from customer table. I have used ledgers here. So a complete accounting package is ready.
The above screen shot was taken during bill entry, just before saving. After saving the bill number would appear on the list box. Selecting the bill no will display the bill. You need four tables for sales alone. One for master another for details another for ADD another for LESS. I have given the MS-Access table design screenshots below.
Another tables involved are material master, UOM master and Ledger for customer.
Material reference to UOM->ID and other tables have similar references. In sales only custoemer id, material id, will stored not the actual names. In reports will use sql join query to fetch names.
I think you have some understanding about the process. If you have any queries write to me: sundaracm@gmail.com
