26623608-stock-maintenance-system
TRANSCRIPT
Stock maintenance system
Stock maintenance system
ABSTRACT The main objective of this project is to overcome the work load and time consumption
which makes the maintenance of the stock in an organization as a tedious process. This
project provides complete information about the details of the stock to the users. This
project identifies the amount of stock available,the product that is purchased faults in the
product,products that are replaced,products that have been sold,and the date at which the
products are bought and sold in a particular concern.Separate modules have been created
for purchasing, viewing stock details,selling, details and delivery details.As a
whole ,marketing process can be improved if stock has been maintained properly.
This project is developed with help of visual basic 6.0 as a front end and oracle 9i
as back end. The application software Rational Rose is also used.
I
Stock maintenance system
CONTENTS
S.No Title Page No
Abstracts
List of figures
List of tables
I
II
III
1.
Introduction
1.1 problem
1.2 problem definition
2.
System tools
2.1 MS-VISUAL basic
2.2 ORACLE9i
3.
Methodology
3.1 concept
3.2 database connectivity
4.
System requirement
4.1 hardware requirement
4.2 software requirement
5. System modules
5.1 structural diagram
5.1.1 use case diagram
5.1.2 class diagram
5.1.3 state diagram
5.1.4 activity diagram
5.1.5 sequence diagram
5.1.6 collaboration diagram
5.2 module diagram
5.2.1 stock module
5.2.2 sales module
Stock maintenance system
5.2.3 delivery module
5.2.4 replacing the fault module
6. Implementation and result
7. Testing
8.
Conclusion and future enhancement
8.1 conclution
8.2 future enhancement
9.
Reference
9.1 reference book
9.2 reference web sites
LIST OF FIGURES
Stock maintenance system
FIG NO TITLE PAGE NO
OVER ALL DIAGRAM
1. USE CASE DIAGRAM
2. CLASS DIAGRAM
3. STATE DIAGRAM
4. ACTIVITY DIAGRAM
5. SEQUENCE DIAGRAM
6. COLLABORATION DIAGRAM
STOCK MODULE
1. USE CASE DIAGRAM
2. CLASS DIAGRAM
3. STATE DIAGRAM
4. ACTIVITY DIAGRAM
5. SEQUENCE DIAGRAM
6. COLLABORATION DIAGRAM
SALES MODULE
1. USE CASE DIAGRAM
2. CLASS DIAGRAM
3. STATE DIAGRAM
4. ACTIVITY DIAGRAM
5. SEQUENCE DIAGRAM
6. COLLABORATION DIAGRAM
DELIVERY MODULE
1. USE CASE DIAGRAM
2. CLASS DIAGRAM
3. STATE DIAGRAM
4. ACTIVITY DIAGRAM
5. SEQUENCE DIAGRAM
6. COLLABORATION DIAGRAM
REPLASEING THE FAULT MODULE
Stock maintenance system
1. USE CASE DIAGRAM
2. CLASS DIAGRAM
3. STATE DIAGRAM
4. ACTIVITY DIAGRAM
5. SEQUENCE DIAGRAM
6. COLLABORATION DIAGRAM
II
Stock maintenance system
LIST OF TABLESTABLE NO TITLE PAGE NO
1. STOCK MODULE
2. SALES MODULE
3. DELIVERY MODULE
4. REPLACING THE FAULTS MODULE
III
Stock maintenance system
1. INTRODUCTION
1.1 PROBLEMStock Maintenance gives an idea about how products are maintained in the
particular concern. The products that are to be purchased, the products that are to be sold
are maintained here.
This project also gives idea about the faults in the purchased product and the products
that are to be replaced also been given. Further some additional details of the current
stock that is available in the store is also given.
Stock maintenance in this project is done in an authorized way. The password and user id
has been set here. As a whole the marketing process can be improved if the stock is
maintained properly.
1.2PROBLEM DEFINITION: The stock has to be maintained properly since the whole marketing process can be
improved. Stock maintenance in this project gives the idea about how products are
maintained in a particular concern.
The stock details which includes the amount of stock available, the stock is to be
purchased, the date or the time it is being bought or delivered, the amount that is already
available are maintained in this project.
The stock maintenance in this project is understood by goin through the modules that is
being involved. The whole economic status is being improved properly if stock is
maintained.
Stock maintenance system
2. SOFTWARE TOOLS
2.1 MS-VISUAL BASIC:
Visual Basic 6.0 is fastest and easiest way to create single user and client/server
applications for Microsoft Windows. Visual Basic 6.0 provides complete set of tools to
simplify rapid application development both for the experienced professional and new
windows programmers.The “Visual” part refers to the method used to create Graphical
User Interface.The “Basic” part refers to the BASIC language used by more
programmers than any other language in the history of computing. It is an ideal
programming language for developing sophisticated professional applications for
Microsoft.
2.2 ORACLE 9i:Oracle 9i makes most necessary conversion to the code automatically when we
convert our data base. However the conversion process makes some changes to code
that need to aware of, and there are some additional changes that must made in order to
run the application successfully in oracle.
Stock maintenance system
3. METHODOLOGY
3.1 CONCEPTStoring of information about the stock values and updating the stock values for
each organization which is using this system, keeps track of all the information about the
stock exchange that are made by the customers, having registration feature of adding up
new customers to the organization are provided in this system.
3.2 DATABASE CONNECTIVITY:The front end used for implementation of this project is Visual Basic 6.0 and the
back end that is used for this project is Oracle9i.The database connectivity between
Visual Basic 6..0 and oracle9i is provided by using Microsoft ADO Data control
6.0(OLEDB). Thus the project makes use of the internal feature of ADODC for
communicating with the database and this is responsible for transfer of data to and from
the table from and to the front end.
A term used to a database or database server used as a source of data. ODBC data
source are referred to by their Data Source Name (DSN). Data sources can be created by
using the windows control panel or the register database method. A standard protocol that
permits application to connect to a variety of external database servers or files. ODBC
drivers used by the Microsoft database engine permit access to Microsoft SQL server
and several other databases.
To add ADO data control to the toolbox go to the components and select
Microsoft ado Data control 6.0(OLEDB). There are three different options insetting
connection string. They are as follows
1. Use data link file : any data link file already stored can be specified.
2. Use ODBC Data source name : any ODBC DSN can be specified.
3. Use connection string : choose build to create a new connection String
specifying the OLEDB provider.
Stock maintenance system
4.SYSTEM REQUIREMENT
4.1 Hardware Requirements:
Processor : Intel Pentium III or later.
CPU Speed : 1.0 GHZ
Strorage Capacity : 20 GB
RAM Capacity : 512 MB
4.2 Software Requirements:Front End : VISUAL BASIC 6.0
Back End : Oracle 9i
Operating System : Windows Family
Application Software : Rational Rose
Stock maintenance system
5.SYSTEM MODULES
5.1 STRUCTURE DIAGRAM
OVERALL DIAGRAM
5.1.1 USE CASE DIAGRAM:Use case diagram is a diagram that shows the interaction between the user and the
system to captures the user’s goal.
Stock maintenance system
5.1.2 CLASS DIAGRAM:
Class diagram or object modeling is a collection of static elements such as classes,
their relationships connected as a graph to each other.
COMPANY MANAGERidaddress
supply items()
USERnameaddress
request()purchase()
DB MANAGER
update()display()
ORDER LINEquantityprice
ORDERpriceamount
dispatch()close()
STORE KEEPERaddressphone no
DEALERnameaddress
sends price details()request replacing defected items()
Fig 1.2
Stock maintenance system
5.1.3 STATE DIAGRAM:State diagram shows that the sequence of states that an object goes through during
its life in response to outside stimuli and messages.
Stock maintenance system
5.1.4 ACTIVITY DIAGRAMActivity diagram is a variation or special case of a state machine in which
the states are activities representing the performance of operations and the transitions are
triggered by the completion of the operations.
create requirement from customer
check availablity
receive payroll
supply products
request and receive item
check for damage
yes
no
replace damaged items
Fig 1.4
Stock maintenance system
5.1.5 SEQUENCE DIAGRAM:Sequence diagram is an easy and intuitive way of describing the behaviour of a
system by viewing the interaction between the system and its environment.
USER ORDER SHOP KEEPER
DEALER COMPANY DB MANAGER
request for item
check for availablity
update prodect detailsorder items
ask for quotation
send price details
capture deal
dispatch product
request for delivery
request for customer details
send customer details
update delivery detail
complain about defect
detail of defect
request for replacing refected items
replace defect
replace deliver item
close order
Fig 1.5
Stock maintenance system
5.1.5 COLLABORATION DEIAGRAM:Colloboration diagram represents a collaboration, which is a set of objects
related in a particular context and interaction,which is a set of exchanged amongt the
objects with in the collaboration to achieve a desired outcome.
USER
ORDER
SHOP KEEPER
DEALER COMPANY
DB MANAGER
1: request for item
2: check for availablity
3: update prodect details4: order items 5: ask for quotation
6: send price details
7: capture deal8: dispatch product
9: request for delivery
10: request for customer details
11: send customer details
12: update delivery detail
13: complain about defect
14: detail of defect
15: request for replacing refected items
16: replace defect
17: replace deliver item
18: close order
Fig 1.6
Stock maintenance system
5.2 MODULE DIAGRAM Class diagram or object modeling is a collection of static elements such as
classes, their relationships connected as a graph to each other.
5.2.1 STOCK MODULE:
USE CASE DIAGRAM
Stock maintenance system
CLASS DIAGRAM:
USERnameaddress
complain()purchase()
STORE KEEPERuser idpassword
deliver()send defects details()
ORDERpriceamount
dispatch()close()
DB MANAGER
update details()display details()
COMPANYnameaddress
supply items()set limit()
DEALERnameaddress
replace()request return()
Fig 2.2
STATE DIAGRAM:
Stock maintenance system
ACTIVITY DIAGRAM:
SEQUENCE DIAGRAM:
Stock maintenance system
COLLABORATION DIAGRAM:
Stock maintenance system
5.2.2 SALES MODULE:
USE CASE DIAGRAM:
COMPANY MANAGER
set limit
DEALER
DB MANAGER
price deal
verify price
capture deal
STORE KEEPER
update product details
Fig 2.7
Stock maintenance system
CLASS DIAGRAM:
COMPANYnameaddress
supply items()set limit()
DEALERnameaddress
replace()request return()
ORDERpriceamount
dispatch()close()
DB MANAGER
update details()display details()
STORE KEEPERuser idpassword
deliver()send defects details()
Fig 2.8
STATE DIAGRAM:
Stock maintenance system
ACTIVITY DIAGRAM:
SEQUENCE DIAGRAM:
Stock maintenance system
COLLABORATION DIAGRAM:
Stock maintenance system
5.2.3 DELIVERY MODULE:
USE CASE DIAGRAM:
USER
DB MANAGER
asks for product
sends the product
receive payment
STORE KEEPER
update product's delivery details
Fig 2.13
CLASS DIAGRAM:
USERnameaddress
complain()purchase()
STORE KEEPERuser idpassword
deliver()send defects details()
DB MANAGER
update details()display details()
Fig 2.14
Stock maintenance system
STATE DIAGRAM:
ACTIVITY DIAGRAM:
Stock maintenance system
COLLABORATION DIAGRAM:
Stock maintenance system
5.2.4 REPLACING THE FAULT MODULE:
USE CASE DIAGRAM:
DB MANAGER
USER
update details
complaints about defect STORE KEEPER
COMPANY MANAGER
details about defect
replaces the defect requests for return
DEALER
return another product
fig 2.18
Stock maintenance system
CLASS DIAGRAM:
COMPANY MANAGER
replace product()
USERnameaddress
complain()purchase()
DEALERnameaddress
replace()request return()
DB MANAGER
update details()display details()
STORE KEEPERuser idpassword
deliver()send defects details()
Fig 2.19
STATE DIAGRAM:
Stock maintenance system
ACTIVITY DIAGRAM:
COLLABORATION DIAGRAM:
Stock maintenance system
6.IMPLEMENTATION AND RESULT:
LOGIN FORM:
Fig 6.1
OUTPUT:
Fig 6.2
CODING:
Private Sub cmdCancel_Click()
LoginSucceeded = False
Unload Me
End Sub
Private Sub cmdOK_Click()
If (txtUserName.Text = "") Then
MsgBox "Enter a valid Username", vbCritical + vbOKOnly, "Error"
ElseIf (txtPassword.Text = "") Then
Stock maintenance system
MsgBox "Enter a valid Password", vbCritical + vbOKOnly, "Error"
End If
rs.Close
rs.Open "Select Passwd from Login where uname = '" & txtUserName.Text & "'", db, 1,
2
If rs.EOF = True Then 'If Search is found
MsgBox "User not found", vbCritical + vbOKOnly, "Error"
Else
If (rs.Fields("Passwd") <> txtPassword.Text) Then
MsgBox "Invalid Password", vbCritical + vbOKOnly, "Error"
txtPassword.Text = ""
txtPassword.SetFocus
Else
user = txtUserName.Text
If (txtUserName.Text = "admin") Then
userAdmin = True
Text1.Text = True
frmLogin.Hide
frmmsg.Label1.Caption = "welcome to Stock Maintenance System"
frmmsg.Show
End If
End If
End Sub
Stock maintenance system
MAIN FROM:
Fig :6.3
Stock maintenance system
STOCK MAINTENANCE:
Fig 6.4
Stock maintenance system
UPDATE:
Fig :6.5
NEW ENTRY:
Fig 6.6
Stock maintenance system
SHOW ALL:
Fig :6.7
CODING:
Dim date1 As Date
Dim temp, q, p As Integer
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmddelete_Click()
Dim str As String
If (MsgBox("Are you sure you want to Delete it", vbYesNo + vbQuestion, "Stock") =
vbYes) Then
Stock maintenance system
str = "delete from Stock where itemcode='" & rs!itemcode & "'"
db.Execute str
db.Close
connectme
MsgBox "Deleted Successfully", vbOKOnly
cmbitype.Visible = True
Else
frmStockDetails.SetFocus
End If
End If
End Sub
Private Sub Command2_Click()
If (MsgBox("Are you sure you want to update the stock", vbYesNo + vbQuestion,
"Stock") = vbYes) Then
With rs
.Update "itemtype", txtitype.Text
.Update "itemcode", txticode.Text
.Update "itemname", txtiname.Text
.Update "netweight", txtnetwt.Text
.Update "date_of_entry", txtdate.Text
.Update "quantity", txtquantity.Text
.Update "price", txtprice.Text
End With
MsgBox "Stock Detail modified successfully", vbInformation + vbOKOnly, "Update"
Unload Me
frmStockDetails.Show
End If
Else
frmStockDetails.SetFocus
End Sub
Stock maintenance system
Private Sub Command1_Click()
frmpurchase.Show
End Sub
Private Sub Command3_Click()
txtitype.Visible = False
cmbitype.Visible = True
Call Reset
End Sub
Private Sub Form_Load()
connectme
MSHFlexGrid1.Visible = False
rs.Close
rs.Open "select * from stock ", db, adOpenKeyset, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
End Sub
Private Sub connectme()
Dim i As Integer, j As Integer
db.Open "arun", "scott", "tiger"
Call Resetall
rs.Open "select itemtype from stock group By itemtype ", db, adOpenKeyset,
adLockOptimistic
While rs.EOF <> True
cmbitype.AddItem rs!itemtype
rs.MoveNext
Wend
txtitype.Visible = False
End Sub
Stock maintenance system
Private Sub Command4_Click()
If MSHFlexGrid1.Visible = True Then
MSHFlexGrid1.Visible = False
txtitype.Visible = True
txticode.Visible = True
txtiname.Visible = True
txtamount.Visible = True
txtnetwt.Visible = True
txtquantity.Visible = True
txtprice.Visible = True
txtdate.Visible = True
Command2.Enabled = True
cmddelete.Enabled = True
Else
Unload Me
End If
End Sub
Private Sub cmbitype_Click()
Call Reset
rs.Close
rs.Open "select * from stock where itemtype=" & "'" & cmbitype.Text & "'", db,
adOpenKeyset, adLockOptimistic
rs.MoveFirst
cmbitype.Visible = False
txtitype.Visible = True
With rs
txtitype = !itemtype
txticode = !itemcode
txtiname.Text = !itemname
Stock maintenance system
txtnetwt.Text = !netweight
txtdate.Text = !date_of_entry
txtquantity.Text = !quantity
txtprice.Text = !price
p = txtprice.Text
q = txtquantity.Text
txtamount.Text = p * q
End With
End Sub
Public Sub Resetall()
cmbitype.Clear
txticode.Text = ""
txtiname.Text = ""
txtnetwt.Text = ""
txtquantity.Text = ""
txtdate.Text = ""
txtprice.Text = ""
End Sub
Private Sub cmdfirst_Click()
txticode.Visible = True
On Error GoTo Err
cmdNext.Enabled = True
cmdLast.Enabled = True
rs.MoveFirst
Display
cmdPrev.Enabled = False
cmdFIrst.Enabled = False
Err:
If Err.Number = 3021 Then
Stock maintenance system
MsgBox "NO CURRENT RECORD"
cmdNext.Enabled = False
cmdLast.Enabled = False
cmdPrev.Enabled = False
cmdFIrst.Enabled = False
End If
End Sub
Private Sub cmdlast_Click()
txticode.Visible = True
On Error GoTo Err
cmdFIrst.Enabled = True
cmdPrev.Enabled = True
rs.MoveLast
Display
cmdNext.Enabled = False
cmdLast.Enabled = False
Err:
If Err.Number = 3021 Then
MsgBox "NO CURRENT RECORD"
cmdNext.Enabled = False
cmdLast.Enabled = False
cmdPrev.Enabled = False
cmdFIrst.Enabled = False
End If
End Sub
Private Sub cmdnext_Click()
txticode.Visible = True
On Error GoTo Err
If rs.BOF <> True And rs.EOF <> True Then
Stock maintenance system
cmdFIrst.Enabled = True
cmdPrev.Enabled = True
rs.MoveNext
Display
Else
rs.MoveLast
Display
cmdNext.Enabled = False
cmdLast.Enabled = False
End If
If Err.Number = 3021 Then
MsgBox "NO CURRENT RECORD"
cmdNext.Enabled = False
cmdLast.Enabled = False
cmdPrev.Enabled = False
cmdFIrst.Enabled = False
End If
End Sub
Private Sub cmdprev_Click()
txticode.Visible = True
On Error GoTo Err
cmdNext.Enabled = True
cmdLast.Enabled = True
If rs.BOF <> True And rs.EOF <> True Then
rs.MovePrevious
Display
Else
rs.MoveFirst
Display
cmdPrev.Enabled = False
Stock maintenance system
cmdFIrst.Enabled = False
End If
Err:
If Err.Number = 3021 Then
MsgBox "NO CURRENT RECORD"
cmdNext.Enabled = False
cmdLast.Enabled = False
cmdPrev.Enabled = False
cmdFIrst.Enabled = False
End If
End Sub
Stock maintenance system
SALES MASTER:
Fig 6.8
Stock maintenance system
OUTPUT:
Fig :6.9
Stock maintenance system
Fig :6.10
Fig :6.11
Stock maintenance system
CODING:Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim p, q As Double
Private Sub cmbitype_Click()
Command6.Enabled = True
Command1.Enabled = True
Label17.Visible = False
txtcname.Visible = False
temp = cmbitype.Text
Call Reset
cmbicode.Clear
rs.Open "select itemcode from stock where itemtype=" & "'" & temp & "'", db,
adOpenDynamic, adLockPessimistic
While rs.EOF <> True
cmbicode.AddItem rs!itemcode
rs.MoveNext
Wend
rs.Close
rs.Open "select * from stock where itemtype= " & "'" & temp & "'", db, adOpenKeyset,
adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
End Sub
Private Sub Command3_Click()
Command6.Enabled = True
Stock maintenance system
Command1.Enabled = True
Label17.Visible = False
txtcname.Visible = False
Call save
connectme
End Sub
Private Sub Command6_Click()
rs1.Open "select * from itemorder ", db, adOpenKeyset, adLockOptimistic
rs1.AddNew
rs1!billnumber = txtbillno.Text
rs1!itemcode = cmbicode.Text
rs1!customername = txtcname.Text
rs1!order_date = txtdate.Text
rs1!orderedquantity = txtquantity.Text
rs1!amount = txtamount.Text
rs1.Update
rs1.Close
MsgBox "Item Detail Save Successfully ", , "Order Master"
MsgBox "your item will be Delivered as Soon as Possible", , "Order Master"
Call save
Call saleanother
Label17.Visible = False
txtcname.Visible = False
Command1.Enabled = True
End Sub
Private Sub open1()
rs2.Open "select * from stock where itemcode=" & "'" & cmbicode.Text & "'" & "and
itemtype=" & "'" & cmbitype.Text & "'" & "and itemname=" & "'" & txtiname.Text &
"'", db, adOpenKeyset, adLockOptimistic
Stock maintenance system
If rs2.EOF <> True And rs2.BOF <> True Then
rs2!quantity = rs2!quantity - Val(txtquantity.Text)
If rs2!quantity < 0 Then
MsgBox "Sorry Not Enough Stock and you want order it "
Exit Sub
End If
rs2.Update
rs2.Close
MsgBox "item quantity updated"
Else
MsgBox "THAT ITEM IS NOT AVIALABLE"
End If
rs1.Open "select * from sales ", db, adOpenKeyset, adLockOptimistic
rs1.AddNew
rs1!billnumber = txtbillno.Text
rs1!itemtype = cmbitype.Text
rs1!itemcode = cmbicode.Text
rs1!itemname = txtiname.Text
rs1!netweight = txtnetwt.Text
rs1!sale_date = txtdate.Text
rs1!quantity = txtquantity.Text
rs1!price = txtprice.Text
rs1!amount = txtamount.Text
rs1.Update
rs1.Close
MsgBox "Item Detail Save Successfully", vbOKOnly, "Sales Master"
If (MsgBox("sale another Item", vbYesNo + vbQuestion, "Sales Master ") = vbYes) Then
Call save
Call saleanother
Command6.Enabled = True
Else
Stock maintenance system
Unload Me
frmSaleentry.Show
frmSaleentry.SetFocus
End If
End Sub
Private Sub saleanother()
rs.Open "select itemtype from stock group By itemtype ", db, adOpenKeyset,
adLockOptimistic
While rs.EOF <> True
cmbitype.AddItem rs!itemtype
rs.MoveNext
Wend
rs.Close
rs.Open "select itemtype from stock group By itemtype ", db, adOpenKeyset,
adLockOptimistic
rs.Close
End Sub
Private Sub save()
txtcname.Text = ""
cmbitype.Clear
cmbicode.Clear
txtiname.Text = ""
txtnetwt.Text = ""
txtdate.Text = ""
txtdate.Text = Format$(Date, "dd/mm/yyyy")
txtquantity.Text = ""
txtprice.Text = ""
txtamount.Text = ""
Stock maintenance system
End Sub
Private Sub Command2_Click()
Dim z, x As Double
cmdRefresh_Click
connectme
cmbitype.SetFocus
Command1.Enabled = True
Label17.Visible = False
txtcname.Visible = False
Command6.Enabled = True
End Sub
Private Sub increment()
rs1.Open "select * from sales order by BILLNUMBER", db, adOpenKeyset,
adLockOptimistic
If rs1.BOF <> True And rs1.EOF <> True Then
rs1.MoveLast
z = Val(rs1!billnumber)
z = z + 1
txtbillno.Text = z
rs1.Close
rs1.Open "select * from itemorder order by BILLNUMBER", db, adOpenKeyset,
adLockOptimistic
If rs1.BOF <> True And rs1.EOF <> True Then
rs1.MoveLast
txtbillno.Text = ""
x = Val(rs1!billnumber)
rs1.Close
If x > z Then
i = Val(x)
Stock maintenance system
i = i + 1
txtbillno.Text = i
Else
i = Val(z)
i = i + 1
txtbillno.Text = i
End If
Else
rs1.Close
End If
Else
txtbillno.Text = 7000
rs1.Close
End If
End Sub
Public Sub Reset()
txtcname.Text = ""
txtiname.Text = ""
txtnetwt.Text = ""
txtquantity.Text = ""
txtprice.Text = ""
txtamount.Text = ""
End Sub
Private Sub cmbicode_Click()
Call Reset
rs.Open "select * from stock where itemtype=" & "'" & cmbitype.Text & "'" & " and
itemcode=" & "'" & cmbicode.Text & "'", db, adOpenKeyset, adLockOptimistic
With rs
txtiname.Text = !itemname
Stock maintenance system
txtnetwt.Text = !netweight
txtquantity.Text = ""
txtprice.Text = !price
End With
rs.Close
txtquantity.SetFocus
End Sub
Private Sub Form_Load()
db.Open "arun", "scott", "tiger"
connectme
End Sub
Private Sub connectme()
Call Resetall
increment
rs.Open "select itemtype from stock group By itemtype ", db, adOpenKeyset,
adLockOptimistic
While rs.EOF <> True
cmbitype.AddItem rs!itemtype
rs.MoveNext
Wend
rs.Close
rs.Open "select itemtype from stock group By itemtype ", db, adOpenKeyset,
adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
End Sub
Public Sub Resetall()
txtbillno.Text = ""
cmbitype.Clear
Stock maintenance system
cmbicode.Clear
txtiname.Text = ""
txtnetwt.Text = ""
txtquantity.Text = ""
txtdate.Text = ""
txtprice.Text = ""
txtamount.Text = ""
End Sub
Stock maintenance system
DELIVERY MODULE:
Fig :6.12
Stock maintenance system
OUTPUT:
Fig :6.13
Stock maintenance system
Fig :6.14
CODING:
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmbbillno_click()
temp = cmbbillno.Text
cmbicode.Clear
rs.Open "select * from itemorder where billnumber=" & "'" & temp & "'and deliver is
NULL", db, adOpenDynamic, adLockPessimistic
While rs.EOF <> True
cmbicode.AddItem rs!itemcode
rs.MoveNext
Wend
rs.Close
rs.Open "select billnumber,itemcode,deliver from itemorder where billnumber=" & "'"
& temp & "' and deliver is NULL", db, adOpenDynamic, adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
Stock maintenance system
rs.Close
End Sub
End Sub
Private Sub cmbicode_Click()
rs.Open "select * from stock where itemcode=" & "'" & cmbicode.Text & "'", db,
adOpenDynamic, adLockPessimistic
With rs
txtitype = !itemtype
txtiname = !itemname
txtnetwt = !netweight
txtprice = !price
End With
rs.Close
rs.Open "select * from itemorder where itemcode=" & "'" & cmbicode.Text & "'", db,
adOpenDynamic, adLockPessimistic
With rs
txtcname = !customername
txtdate = !order_date
txtoquantity = !orderedquantity
txtamount = !amount
End With
rs.Close
End Sub
Private Sub Command1_Click()
Dim x, y As Double
rs.Open "select * from stock where itemcode=" & "'" & cmbicode & "'", db,
adOpenKeyset, adLockOptimistic
x = Val(rs!quantity)
If rs.EOF <> True And rs.BOF <> True Then
Stock maintenance system
y = x - Val(txtoquantity.Text)
If y < 0 Then
MsgBox "Sorry Not Enough Stock.So Delivery is not Possible "
rs.Close
Exit Sub
End If
rs!quantity = y
rs.Update
MsgBox "item is Delivered"
End If
rs.Close
rs.Open "select * from itemorder where itemcode=" & "'" & cmbicode.Text & "'" & "
and billnumber=" & "'" & cmbbillno.Text & "'", db, adOpenDynamic, adLockPessimistic
With rs
.Update "DELIVERY_DATE", txtddate.Text
.Update "deliver", txtdeliver.Text
End With
rs.Close
rs.Open "select billnumber,itemcode,deliver from itemorder where billnumber=" & "'"
& cmbbillno.Text & "'" & " and billnumber=" & "'" & cmbbillno.Text & "'", db,
adOpenDynamic, adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
End Sub
Private Sub Command2_Click()
Command1.Enabled = False
MSHFlexGrid2.Visible = True
rs.Open "select * from itemorder where deliver is not NULL", db, adOpenDynamic,
adLockPessimistic
Set MSHFlexGrid2.DataSource = rs
rs.Close
End Sub
Stock maintenance system
Private Sub Command3_Click()
Command1.Enabled = True
If MSHFlexGrid2.Visible = True Then
MSHFlexGrid2.Visible = False
Else
Unload Me
End If
End Sub
Private Sub Command4_Click()
Command1.Enabled = False
MSHFlexGrid2.Visible = True
rs.Open "select * from itemorder ", db, adOpenDynamic, adLockPessimistic
Set MSHFlexGrid2.DataSource = rs
rs.Close
End Sub
Private Sub Form_Load()
txtddate.Text = Format$(Date, "dd/mm/yyyy")
db.Open "arun", "scott", "tiger"
rs.Open "select billnumber from(Select * from itemorder group By billnumber)where
deliver is NULL", db, adOpenKeyset, adLockOptimistic
While rs.EOF <> True
cmbbillno.AddItem rs!billnumber
rs.MoveNext
Wend
Set MSHFlexGrid1.DataSource = rs
MSHFlexGrid2.Visible = False
rs.Close
End Sub
Stock maintenance system
REPLACING FAULT MODULE:
Fig :6.15
OUTPUT:
Fig :6.16
Stock maintenance system
CODING:
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmbbillno_Change()
temp = cmbbillno.Text
Call Resetall
cmbicode.Clear
rs.Open "select * from itemorder where billnumber=" & "'" & temp & "'and deliver is
not NULL", db, adOpenDynamic, adLockPessimistic
While rs.EOF <> True
cmbicode.AddItem rs!itemcode
rs.MoveNext
Wend
rs.Close
rs.Open "select billnumber,Customername,itemcode,amount from itemorder where
billnumber=" & "'" & temp & "' and deliver is not NULL", db, adOpenDynamic,
adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
End Sub
Private Sub cmbbillno_click()
temp = cmbbillno.Text
Call Resetall
cmbicode.Clear
rs.Open "select * from itemorder where billnumber=" & "'" & temp & "'and deliver is
not NULL", db, adOpenDynamic, adLockPessimistic
While rs.EOF <> True
cmbicode.AddItem rs!itemcode
Stock maintenance system
rs.MoveNext
Wend
rs.Close
rs.Open "select billnumber,Customername,itemcode,amount from itemorder where
billnumber=" & "'" & temp & "' and deliver is not NULL", db, adOpenDynamic,
adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
End Sub
Public Sub Resetall()
cmbicode.Clear
txtcname.Text = ""
txtnoreplace.Text = ""
txtoquantity.Text = ""
End Sub
Private Sub cmbicode_Click()
rs.Open "select * from itemorder where itemcode=" & "'" & cmbicode & "'" & " and
billnumber=" & "'" & cmbbillno.Text & "'", db, adOpenDynamic, adLockPessimistic
With rs
txtcname = !customername
txtoquantity = !orderedquantity
End With
rs.Close
If (MsgBox("If you want to Replace the item press Yes ,otherwise press No", vbYesNo +
vbQuestion, "Replace/Reject") = vbYes) Then
Stock maintenance system
Command2.Enabled = False
Else
Command1.Enabled = False
Label1.Visible = False
txtnoreplace.Visible = False
End If
End Sub
Private Sub Command1_Click()
If (MsgBox("If you press Yes Replacement will done", vbYesNo + vbQuestion,
"Replace") = vbYes) Then
rs.Open "select * from replace where itemcode=" & "'" & cmbicode.Text & "'" & " and
billnumber=" & "'" & cmbbillno.Text & "'", db, adOpenDynamic, adLockPessimistic
rs.AddNew
rs!billnumber = cmbbillno.Text
rs!itemcode = cmbicode.Text
rs!customername = txtcname.Text
rs!replace_quantity = txtnoreplace.Text
rs!replace_date = txtdate.Text
rs!Replace = Text1.Text
rs.Update
MsgBox "Replacement successfully done", , "Replace"
rs.Close
rs.Open "select billnumber,itemcode,replace_quantity ,replace from replace where
billnumber=" & "'" & cmbbillno.Text & "'" & " and itemcode=" & "'" & cmbicode.Text
& "'", db, adOpenDynamic, adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
Command2.Enabled = True
End If
End Sub
Stock maintenance system
Private Sub Command2_Click()
If (MsgBox("Are you sure want to Reject", vbYesNo + vbQuestion, "Reject") = vbYes)
Then
rs.Open "select * from replace where itemcode=" & "'" & cmbicode.Text & "'" & " and
billnumber=" & "'" & cmbbillno.Text & "'", db, adOpenDynamic, adLockPessimistic
rs.AddNew
rs!billnumber = cmbbillno.Text
rs!itemcode = cmbicode.Text
rs!customername = txtcname.Text
rs!replace_quantity = 0
rs!replace_date = txtdate.Text
rs!Replace = Text2.Text
rs.Update
MsgBox "Replacement not done", , "Reject"
rs.Close
rs.Open "select billnumber,itemcode,replace_quantity ,replace from replace where
billnumber=" & "'" & cmbbillno.Text & "'" & " and itemcode=" & "'" & cmbicode.Text
& "'", db, adOpenDynamic, adLockPessimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
Command1.Enabled = True
Label1.Visible = True
txtnoreplace.Visible = True
End If
End Sub
Private Sub Command4_Click()
cmbbillno.Clear
Label1.Visible = True
txtnoreplace.Visible = True
Stock maintenance system
Command1.Enabled = True
Command2.Enabled = True
Call Resetall
db.Close
Call Form_Load
End Sub
Private Sub Form_Load()
db.Open "arun", "scott", "tiger"
rs.Open "select billnumber from(select * from(replace full outer join itemorder using
( billnumber))where replace is null and deliver is not null )group By billnumber", db,
adOpenKeyset, adLockOptimistic
While rs.EOF <> True
cmbbillno.AddItem rs!billnumber
rs.MoveNext
Wend
rs.Close
rs.Open "select billnumber,itemcode,customername from (replace full outer join
itemorder using (billnumber,customername,itemcode))where replace is null and deliver is
not null ", db, adOpenKeyset, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs
rs.Close
txtdate.Text = Format$(Date, "dd/mm/yyyy")
End Sub
Stock maintenance system
TABLES DESIGN
STOCK MODULE: NAME NULL? TYPE
ITEMTYPE VARCHAR2(60)
ITEMCODE NUMBER(35)
ITEMNAME VARCHAR2(100)
NETWEIGHT VARCHAR2(70)
DATE_OF_ENTRY DATE
QUANTITY NUMBER(35)
PRICE NUMBER(10,2)
SALES MODULE:NAME NULL? TYPE
BILLNUMBER NUMBER(35)
ITEMTYPE VARCHAR2(60)
ITEMCODE NUMBER(35)
ITEMNAME VARCHAR2(60)
NETWEIGHT VARCHAR2(50)
SALE_DATE DATE
QUANTITY NUMBER(35)
PRICE NUMBER(35)
AMOUNT NUMBER(10,2)
Stock maintenance system
DELIVERY MODULE:NAME NULL? TYPE
BILLNUMBER NUMBER(35)
ITEMCODE NUMBER(35)
CUSTOMERNAME VARCHAR2(60)
ORDER_DATE DATE
DELIVERY_DATE DATE
ORDERED
QUANTITY
VARCHAR2(60)
AMOUNT NUMBER(10,2)
DELIVER VARCHAR2(50)
REPLACE MODULE:
NAME NULL? TYPE
BILLNUMBER NUMBER(35)
ITEMCODE NUMBER(35)
CUSTOMERNAME VARCHAR2(60)
REPLACE_DATE DATE
REPLACE_QUANTIT
Y
VARCHAR2(60)
REPLACE VARCHAR2(68)
Stock maintenance system
8. CONCLUSION AND FUTURE ENHANCEMENT
8.1 CONCLUSIONThus the project gives the detailed explanation about how stock is maintained in
an organization.it also gives idea about how stocks are to be purchased and how to be
maintained in a company.
8.2 FUTURE ENHANCEMENTThere is always a room for the improvement in any software package, however
good and efficient it may be. The important thing is that the software should be flexible
enough for further modifications.
In future we will enhance this project by making our system to store the details of
the customer such as the house address to make the door delivery which is an easy and
quick way.
Stock maintenance system
9. REFERENCE
9.1 REFERENCE BOOKS
“Programming in Microsoft Visual Basic 6.0” by Francesco Balena.
“Oracle The complete Reference” by George Koch and Kevin Loney.
“Visual Studio .NET :The .NET Framework Black Book”
by Temple man and Vitter
9.2 REFERENCE WEBSITES www.epay.com
www.v3schools .com