Stock Management System

          STOCK  MANAGEMENT  SYSTEM

PROBLEM DESCRIPTION:

  It is too hard to maintain the record about the daily intake of the raw material and view in the detail as a whole. The improvement in the production cannot be viewed easily when done manually. A record about the production when once created is too hard to delete.

  This is mainly designed for the manufacturing companies. It improves its business by facility of maintaining the record. Its password setting of the administrator helps to improve security. It helps the user to view the details of the stock of various categories and the sales of their requirement.

INTERFACES:

This project consists of six modules for the development of the project

·         Stock sales     

·         Stock upload

·         Turnover

·         Rate fixing

·         Individual turnover

·         Storing in database

1. STOCK SALES MODULE

   In the sales module the sales o each day is recorded. Whenever a sales is done there will be decreasing in the stock. The user can be able to check the sales in the every occasional. Every stock is stored in the database.

2. STOCK UPLOAD MODULE

  The stock upload will have separate column for each and every item in the company. The user will be calculated for stock left.

3. TURNOVER MODULE

 The turnover module will be mainly considered about the amount and quantity of  item is sale. The user can be able to see the turnover for they’re all occasional

4. RATE FIXING MODULE

  The price list has been designed for the purchased products and price modification or  updating can also occurred. This process can ony completely accessed by the Administrator.

5. INDIVIDUAL TURNOVER MODULE

  This module completely displays about the given turnover o a particular Bil no or date. This also shows the previous data of the particular Bil no or date. It stores the complete data of the purchased bill along with its date.

6. STORING IN DATABASE MODULE

  It stores the complete data of pricelist of all products, purchased products, purchase date and it has  complete data about the old products and updated products.

FUNCTIONAL CAPABILITIES

  Any registered user can access the data from the site. And for new users who are allowed  a very user friendly method is given to register .Each  user registered  is given a unique user name and password using which the person can login.

PERFORMANCE LEVEL

  The performance level of the system is found to be very high. The performance is found highly reliable because the system uses minimum par t of  memory. The process of the system uses the processor effectively. Customer can finsh the operation in the last time interval.

data structures and elements

  The back end user for the system is visual basic and the back end user is oracle which provides high security for the data used. When database management system access is required, the system establishes a connection to the database management system, once the connection is created; the client program can communicate with database management system.

SAFETY

  Since, we  use oracle as back end it prevents data loss and provides easy retrieval of data.

RELIABILTY

   When the system customer wants to cal the system over a given period o time, the system correctly deliver services and as expected by the user.

SECURITY AND PRIVACY

   Security of the system is very important for the users. This system provides high security and privacy to the customers.

QUALITY

This system is possible to find problems, access the component that failed and make changes to fix its components. This system quality found to be good.

JUSTIFICATION

*The Visual Basic language facilitates a structured and disciplined approach to computer program design.

*Visual Basic is a distinctly different language providing powerful features such as graphical user interfaces, event handing, and access to the win32 API, object-oriented features, error handling and structured programming.

CONSTRAINTS AND LIMITATIONS

If the connectivity of the system is not established properly then there maybe problems for the customer in accessing the system.

USECASE DIAGRAM:

ACTIVITY DIAGRAM:

CLASS DIAGRAM:

SEQUENCE DIAGRAM:

              

FORM 1 (LOGIN FORM):-

Dim Cn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Dim Flag, Flag1 As Integer

Private Sub CmdCancel_Click()

Dim Msg As Integer

Msg = MsgBox(" Are You Sure...... If U Want To Quit Then Press Yes,Else No.", vbCritical + vbYesNo, "Warning!!!")

If Msg = vbYes Then

End

End If

End Sub

Private Sub CmdOk_Click()

If Text1.Text = "" Then

MsgBox "Please Enter The User_Name.", vbInformation, ""

Else

If Text2.Text = "" Then

MsgBox "Please Enter The Password.", vbInformation, ""

Else

Flag = 0

Rs.MoveFirst

While Rs.EOF = False

If Text1.Text = Rs.Fields(0) Then

If Text2.Text = Rs.Fields(1) Then

Flag1 = 1

MsgBox "" & Text1.Text & " Permission Granted!!!", vbInformation, "Login"

Main.Label3.Caption = Rs.Fields(0)

Text1.Text = ""

Text2.Text = ""

Unload Login

Main.Show

End If

End If

Flag = 2

Rs.MoveNext

Wend

If Flag = 2 And Flag1 <> 1 Then

MsgBox "Invalid Password", vbExclamation, "Error"

End If

If Flag = 0 And Flag1 <> 1 Then

MsgBox "Un_Authorized User", vbExclamation, "Error"

End If

End If

End If

End Sub

Private Sub Form_Activate()

Text1.SetFocus

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

Rs.Open "select * fromAccount", Cn, adOpenDynamic, adLockOptimistic

End Sub

FORM 2 ( MAIN MENU):-

Dim Cn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Dim p As Integer

Private Sub Check1_Click()

If Check1.Value = Checked Then

Animation1.Enabled = True

Animation1.Open "C:\Documents and Settings\CSELAB\Desktop\Stock Management\Media\web-app-update.avi"

Animation1.Play

Animation1.Visible = True

End If

If Check1.Value = Unchecked Then

Animation1.Enabled = False

End If

End Sub

Private Sub CmdNewUser_Click()

NewUser.Show vbModal

End Sub

Private Sub Form_Load()

Animation1.Enabled = False

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

Rs.Open "select * from Account", Cn, adOpenDynamic, adLockOptimistic

Label10.Caption = Rs.RecordCount

End Sub

Private Sub Timer1_Timer()

Select Case Y

Case 0

Label8 = "Stock Management System "

End Select

s = Len(Label8)

Label2 = Left(Label8, p)

p = p + 1

Label2.ForeColor = RGB(Rnd() * 255, Rnd() * 255, Rnd() * 255)

If p = s Then

End If

Label3.ForeColor = RGB(Rnd() * 255, Rnd() * 255, Rnd() * 255)

Label4.Caption = Format(Now, "hh:mm:ss AM/PM")

Label7.Caption = Format(Now, "dd.mm.yyyy")

End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)

Select Case Button.Index

    Case 1

         NewItem.Show

    Case 2

         PurchaseItem.Show

    Case 3

         Report.Show

    Case 4

         NewItemList.Show

    Case 5

         Dim Msg As Integer

         Msg = MsgBox(" Are You Sure...... If U Want To Quit Then Press Yes,Else No.", vbCritical + vbYesNo, "Warning!!!")

         If Msg = vbYes Then

         End

         End If

End Select

End Sub

FORM 3(NEW ITEM):-

Dim Cn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Public EditItem As Boolean

Private Sub CmdClose_Click()

Unload Me

End Sub

Private Sub CmdNew_Click()

Rs.Open "select * from AddStock", Cn, adOpenStatic, adLockBatchOptimistic

Rs.AddNew

Rs.Fields(0) = Val(txtItemNo.Text)

Rs.Fields(1) = txtDesc1.Text

Rs.Fields(2) = txtDesc2.Text

Rs.Fields(3) = txtSize.Text

Rs.Fields(4) = txtOnhandQty.Text

Rs.Fields(5) = txtOrderCost.Text

Rs.Fields(6) = txtUnitPrice.Text

Rs.UpdateBatch

MsgBox "New Item Successfully Added...", vbMsgBoxSetForeground, "Shop Management 1.0"

Clear

txtDesc1.SetFocus

itemno

EditItem = False

CmdNew.Enabled = False

LockedTextBox False

End Sub

Public Sub Clear()

txtDesc1.Text = ""

txtDesc2.Text = ""

txtSize.Text = ""

txtOnhandQty.Text = ""

txtOrderCost.Text = ""

txtUnitPrice.Text = ""

End Sub

Private Sub LockedTextBox(Locked As Boolean)

txtDesc1.Locked = Locked

txtDesc2.Locked = Locked

txtSize.Locked = Locked

txtUnitPrice.Locked = Locked

txtOrderCost.Locked = Locked

End Sub

Private Sub Form_Activate()

SendKeys "{home}+{end}"

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

itemno

LockedTextBox False

End Sub

Public Sub itemno()

On Error GoTo SearchError

Rs.Open "select Item_No from AddStock", Cn

If Rs.RecordCount < 1 Then

txtItemNo.Text = 1

Else

Rs.MoveLast

txtItemNo.Text = Rs.Fields(0) + 1

End If

Rs.Close

SearchError:

If Err.Number = 3705 Then

Rs.Close

Resume

End If

End Sub

FORM 4(ITEM SEARCH):-

Dim Cn As New ADODB.Connection

Dim Cs As New ADODB.Recordset

Private Sub CmdCancel_Click()

Unload ItemSearch

PurchaseItem.Show

End Sub

Private Sub CmdSelect_Click()

Dim RecNum As Long

Set Cs = New Recordset

Cs.Open "select * from AddStock", Cn

RecNum = ListView.SelectedItem.Index - 1

Cs.MoveFirst

Cs.Move RecNum

Unload Me

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

Cs.Open "select * from AddStock where Item_No= '" & PurchaseItem.txtSearch.Text & "'", Cn, adOpenStatic, adLockOptimistic

Do While Not Cs.EOF

Set X = ListView.ListItems.Add(, , Cs(0))

    X.SubItems(1) = Cs(1)

    X.SubItems(2) = Cs(2)

    X.SubItems(3) = Cs(3)

    X.SubItems(4) = Cs(4)

    X.SubItems(5) = FormatNumber(Cs(5))

    X.SubItems(6) = FormatNumber(Cs(6))

Cs.MoveNext

Loop

lblcount.Caption = Cs.RecordCount

End Sub

Private Sub ListView_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then CmdSelect_Click

End Sub

FORM 5( NEW ITEM SEARCH):-

Dim Cn As New ADODB.Connection

Dim Rs As New ADODB.Recordset

Private Sub CmdClose_Click()

Unload Me

End Sub

Private Sub CmdDelete_Click()

If DataGrid1 = True Then

Reply = MsgBox("Are You Sure U Want To Delete This Item?", vbCritical + vbYesNo)

If Reply = vbYes Then

Cn.Execute "Delete from AddStock where Item_No = '" & DataGrid1.Text & "'"

MsgBox "The Current Item Is Deleted", vbInformation, ""

Else

MsgBox "No Selected Item...", vbInformation

End If

End If

Rs.Open "select * from AddStock", Cn

Set DataGrid1.DataSource = Rs

End Sub

Private Sub CmdEdit_Click()

Dim Rs As New ADODB.Recordset

Dim Rs1 As New ADODB.Recordset

Rs.Open "select * from AddStock", Cn

Rs1.Open "update AddStock set Item_No='" & DataGrid1.Columns(0) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Prod_Name='" & DataGrid1.Columns(1) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Description='" & DataGrid1.Columns(2) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Size_W='" & DataGrid1.Columns(3) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Qty_Hand='" & DataGrid1.Columns(4) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Order_Price='" & DataGrid1.Columns(5) & "'", Cn, adOpenStatic, adLockOptimistic

Rs1.Open "update AddStock set Unit_Price='" & DataGrid1.Columns(6) & "'", Cn, adOpenStatic, adLockOptimistic

MsgBox "The Record Edited Successfully!", vbInformation, "Record Edit Success"

Set DataGrid1.DataSource = Rs1

End Sub

Private Sub CmdSearch_Click()

Set Rs = New Recordset

Rs.Open "select * from AddStock where Item_No = '" & txtSearch.Text & "' or Prod_Name like '" & txtSearch.Text & "*' ", Cn

Set DataGrid1.DataSource = Rs

End Sub

Private Sub CmdView_Click()

s = "select * from AddStock"

If Rs.State = 1 Then Rs.Close

Rs.CursorLocation = adUseClient

Rs.Open s, Cn, adOpenStatic, adLockBatchOptimistic

Set DataGrid1.DataSource = Rs

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

Rs.Open "select * from AddStock", Cn, adOpenStatic, adLockOptimistic

lblcount.Caption = Rs.RecordCount

End Sub

FORM 6(PURCHASE ITEM):-

Dim Cn As New ADODB.Connection

Dim Ss As New ADODB.Recordset

Dim Ss1 As New ADODB.Recordset

Dim ListviewFocus As Boolean

Public QtyRemain As Integer

Private Sub CmdClose_Click()

Unload Me

End Sub

Private Sub CmdNew_Click()

NewItem.EditItem = False

NewItem.Show vbModal

End Sub

Private Sub CmdPrice_Click()

Dim Ss As New ADODB.Recordset

Ss.Open "select * from AddStock", Cn

Dim i As Integer

i = ListView.ListItems.Count

If i > 0 Then

    If ListviewFocus = True Then

        Price.txtPrice.Text = ListView.SelectedItem.SubItems(5)

        Price.Show vbModal

        ListView.SelectedItem.SubItems(5) = FormatNumber(Price.Price)

        ListView.SelectedItem.SubItems(6) = FormatNumber(CDbl(ListView.SelectedItem.SubItems(5)) * CDbl(Price.Price))

    Else

        Price.txtPrice.Text = ListView.ListItems(i).SubItems(5)

        Price.Show vbModal

        ListView.ListItems(i).SubItems(5) = FormatNumber(rice.Price)

        ListView.ListItems(i).SubItems(6) = FormatNumber(CDbl(ListView.ListItems(i).SubItems(5)) * CDbl(Price.Price))

    End If

Else

MsgBox "No Item in a List..."

End If

ComputeTotal

End Sub

Private Sub CmdQty_Click()

Dim Ss As New ADODB.Recordset

Ss.Open "select * from AddStock", Cn

Dim i As Integer

i = ListView.ListItems.Count

If i > 0 Then

    If ListviewFocus = True Then

        FrmQty.txtqty.Text = ListView.SelectedItem.SubItems(4)

        FrmQty.DET = "Purchase"

        FrmQty.Show vbModal

        ListView.SelectedItem.SubItems(4) = FrmQty.qty

        ListView.SelectedItem.SubItems(6) = FormatNumber(CDbl(ListView.SelectedItem.SubItems(5)) * CDbl(FrmQty.qty))

    Else

        FrmQty.txtqty.Text = ListView.ListItems(i).SubItems(4)

        FrmQty.DET = "Purchase"

        FrmQty.Show vbModal

        ListView.ListItems(i).SubItems(4) = FrmQty.qty

        ListView.ListItems(i).SubItems(6) = FormatNumber(CDbl(ListView.ListItems(i).SubItems(5)) * CDbl(FrmQty.qty))

    End If

Else

MsgBox "No Item In A List..."

End If

ComputeTotal

End Sub

Private Sub CmdRemove_Click()

Dim i As Integer

If ListviewFocus = True Then

i = ListView.SelectedItem.Index

ListView.ListItems.Remove (i)

txtSearch.Text = ""

ListviewFocus = False

Else

MsgBox "No List Selected!", 64

End If

ComputeTotal

End Sub

Private Sub CmdSave_Click()

Dim Ds As New ADODB.Recordset

Dim Ss1 As New ADODB.Recordset

Dim i As Integer

Ds.Open "select * from Purchase ", Cn, adOpenStatic, adLockBatchOptimistic

Ds.AddNew

Ds.Fields(0) = Val(txtPO.Text)

Ds.Fields(1) = txtdate.Text

Ds.Fields(2) = txtSupplier.Text

Ds.Fields(3) = lblcount.Caption

Ds.Fields(4) = txtSubTotal.Text

Ds.Fields(5) = txtDiscount.Text

Ds.Fields(6) = txtTotal.Text

Ds.UpdateBatch

Ss1.Open "select * from AddStock", Cn

If Ss1.Fields(4) <> 0 Then

i = FrmQty.txtqty

Cn.Execute "update AddStock set Qty_Hand='" & (Ss1.Fields(4) - i) & "' where Item_No='" & txtSearch.Text & "'"

MsgBox "Data Successfully Saved...", vbInformation, ""

End If

Unload PurchaseItem

Report.Show

End Sub

Private Sub CmdSearch_Click()

On Error GoTo SearchError

Set Ss = New Recordset

Ss.Open "select * from AddStock ", Cn

If Ss.RecordCount > 1 Then

ItemSearch.Show vbModal

End If

Ss.Open "select * from AddStock where Item_No = '" & txtSearch.Text & "' or Prod_Name like '" & txtSearch.Text & "*' ", Cn

If Ss.RecordCount > 0 Then

QtyRemain = Ss(4)

If QtyRemain > 0 Then

Set x = ListView.ListItems.Add(, , Ss(0))

    x.SubItems(1) = Ss(1)

    x.SubItems(2) = Ss(2)

    x.SubItems(3) = Ss(3)

    x.SubItems(4) = 1

    x.SubItems(5) = FormatNumber(Ss(5))

    x.SubItems(6) = FormatNumber(Ss(6))

    x.EnsureVisible

    x.Selected = True

Else

MsgBox "Out Of Stock...", vbInformation

End If

Else

MsgBox "No Available Item...", vbInformation, ""

SendKeys "{home}+{end}"

End If

Ss.Close

lblcount.Caption = ListView.ListItems.Count

ComputeTotal

SendKeys "{home}+{end}"

SearchError:

If Err.Number = 3705 Then

Ss.Close

Resume

End If

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

Ss.Open "select * from AddStock", Cn, adOpenStatic, adLockOptimistic

lblcount.Caption = Ss.RecordCount

txtdate.Text = Format(Date, "dd-mm-yyyy")

PurchaseItem.Caption = "Purchase : " & Now

PONo

End Sub

Private Sub PONo()

On Error GoTo SearchError

Ss.Open "select Item_No from AddStock", Cn

If Ss.RecordCount < 1 Then

txtPO.Text = 1

Else

Ss.MoveLast

txtPO.Text = Ss.Fields(0) + 1

End If

Ss.Close

SearchError:

If Err.Number = 3705 Then

Ss.Close

Resume

End If

End Sub

Private Sub ComputeTotal()

Dim i As Integer

i = ListView.ListItems.Count

txtSubTotal.Text = 0

Do Until i = 0

txtSubTotal.Text = FormatNumber(CDbl(txtSubTotal.Text) + ListView.ListItems(i).SubItems(6))

i = i - 1

Loop

txtTotal.Text = FormatNumber(CDbl(txtSubTotal.Text) - CDbl(txtDiscount.Text))

End Sub

Private Sub txtDiscount_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then

txtDiscount.Text = FormatNumber(txtDiscount.Text)

End If

End Sub

Private Sub txtSearch_GotFocus()

ListviewFocus = False

End Sub

Private Sub txtSearch_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

    Case 13

        CmdSearch_Click

    Case 116

        CmdQty_Click

End Select

End Sub

Private Sub ListView_DblClick()

CmdQty_Click

End Sub

Private Sub ListView_GotFocus()

ListviewFocus = True

End Sub

Private Sub ListView_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = 116 Then CmdQty_Click

End Sub

Private Sub txtDiscount_Change()

If txtDiscount = "" Then txtDiscount = "0.00": SendKeys "{home}+{end}"

ComputeTotal

End Sub

FORM 7(REPORT):-

Dim Cn As New ADODB.Connection

Dim Qs As New ADODB.Recordset

Dim Qs1 As New ADODB.Recordset

Dim DTBegin As Date, DTEnd As Date

Private Sub cboDateSummary_Click()

Select Case cboDateSummary

    Case "Today"

        DTBegin = Date

        DTEnd = Date

    Case "Yesterday"

        DTBegin = Date - 1

        DTEnd = Date - 1

    Case "This Week"

        DTBegin = DateSerial(Year(Date), Month(Date), Day(Date) - Weekday(Date, vbSunday) + 1)

        DTEnd = Date

    Case "Last Week"

        DTBegin = DateSerial(Year(Date), Month(Date), Day(Date) - Weekday(Date) - 6)

        DTEnd = DateSerial(Year(Date), Month(Date), Day(Date) - Weekday(Date))

    Case "This Month"

        DTBegin = DateSerial(Year(Date), Month(Date), 1)

        DTEnd = Date

    Case "Last Month"

        DTBegin = DateSerial(Year(Date), Month(Date) - 1, 1)

        DTEnd = DateSerial(Year(Date), Month(Date + 1), 0)

    Case "This Year"

        DTBegin = DateSerial(Year(Date), vbFirstJan1, 1)

        DTEnd = Date

    Case "Last Year"

        DTBegin = DateSerial(Year(Date) - 1, vbFirstJan1, 1)

        DTEnd = DateSerial(Year(Date) - 1, Month(Last), 31)

End Select

        DisplayPurchaseList

        LblDate.Caption = "Search Result for " & DTBegin & " to " & DTEnd

End Sub

Public Sub DisplayPurchaseList()

On Error GoTo SearchError

ListView.ListItems.Clear

Qs.Open "select * from Purchase where PDate between '" & Format(DTBegin, "dd-mmm-yyyy") & "' and '" & Format(DTEnd, "dd-mmm-yyyy") & "' ", Cn

Do While Not Qs.EOF

Set x = ListView.ListItems.Add(, , Qs(0))

    x.SubItems(1) = Qs(1)

    x.SubItems(2) = Qs(2)

    x.SubItems(3) = Qs(3)

    x.SubItems(4) = FormatNumber(Qs(4))

    x.SubItems(5) = FormatNumber(Qs(5))

    x.SubItems(6) = FormatNumber(Qs(6))

    Qs.MoveNext

    Loop

    Qs.Close

SearchError:

    If Err.Number = 3705 Then

    Qs.Close

    Resume

    End If

End Sub

Private Sub CmdClose_Click()

Unload Me

End Sub

Private Sub cmdShowAll_Click()

Set Qs = New Recordset

ListView.ListItems.Clear

Qs.Open "select * from Purchase ", Cn

    Set x = ListView.ListItems.Add(, , Qs(0))

    x.SubItems(1) = Qs(1)

    x.SubItems(2) = Qs(2)

    x.SubItems(3) = Qs(3)

    x.SubItems(4) = FormatNumber(Qs(4))

    x.SubItems(5) = FormatNumber(Qs(5))

    x.SubItems(6) = FormatNumber(Qs(6))

    Qs.MoveNext

        Qs.Close

LblDate.Caption = "Search Result For All Purchase..."

End Sub

Private Sub Form_Load()

If Cn.State = 1 Then Cn.Close

Cn.CursorLocation = adUseClient

Cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Persist Security Info=True;"

End Sub

OUTPUT:

CONCLUSION:

    Thus the required project for “Stock Maintenance” using visual basic as front end and oracle as back end was successfully completed.