oil refining.frm

This project is to design the automated system for the inventory of Al-Ahmad Cotton and Oil Mills si

reset
Exit Sub
Else
Set rs2 = New ADODB.Recordset

If rs2.State = adStateOpen Then rs2.Close

rs2.Open "select * from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenStatic, adLockReadOnly
If rs2.RecordCount = 0 Then
MsgBox "Castic Soda is not available for Production"
reset
Exit Sub
'//////////////////////////
End If
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenDynamic, adLockOptimistic
qty = rs1.Fields(0) - rs1.Fields(1)
If qty < Val(casticsodamncon.Text) Then
MsgBox "Required Soda is not available,Remaining Soda(Mn)= " & qty
reset
Exit Sub
End If
'//////////////////////////
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id=" & Val(co.Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(co.Text)
rs2.Fields("Tras_Date") = refno(0).Text
rs2.Fields("Qty_In") = 0
rs2.Fields("Qty_Out") = Val(crudeoilmnused.Text)
rs2.Update

'////////////////////////////////////////////////////
'Stock Maintenance of Castic Soda used

'////////////////////
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id=" & Val(id.Text), cnn, adOpenDynamic, adLockOptimistic
rs2.AddNew
rs2.Fields("Prod_Id") = Val(id.Text)
rs2.Fields("Tras_Date") = refno(0).Text
rs2.Fields("Qty_In") = 0
rs2.Fields("Qty_Out") = Val(casticsodamncon.Text)
rs2.Update
'//////////////////////////////////////////////
'Stock Maintanance of Refine Oil Produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = ro.Text
rs3.Fields("Tras_Date") = refno(0).Text
rs3.Fields("Qty_In") = refoilmnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.Update
'//////////////////////////////////////////////
'Stock Maintanance of Dirt Oil Produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock1", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = dirto.Text
rs3.Fields("Tras_Date") = refno(0).Text
rs3.Fields("Qty_In") = oildirtmnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.Update
'////////////////////////////////////////////////////
'****************************
'Stock Maintenance in Stock2 Table

'Stock Maintanance of Refine Oil Produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = ro.Text
rs3.Fields("Tras_Date") = refno(0).Text
rs3.Fields("Qty_In") = refoilmnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.Update
'//////////////////////////////////////////////
'Stock Maintanance of Dirt Oil Produced

If rs3.State = adStateOpen Then rs3.Close
rs3.Open "Stock2", cnn, adOpenDynamic, adLockOptimistic
rs3.AddNew
rs3.Fields("Prod_Id") = dirto.Text
rs3.Fields("Tras_Date") = refno(0).Text
rs3.Fields("Qty_In") = oildirtmnprod.Text
rs3.Fields("Qty_Out") = 0
rs3.Update
'////////////////////////////////////////////////////
'******************************

If rs.State = adStateOpen Then rs.Close

rs.Open "OilRefining", cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew
rs.Fields("RefNo") = refno(1).Text
rs.Fields("RefDate") = refno(0).Text
rs.Fields("CrudeOilMnUsed") = crudeoilmnused.Text
rs.Fields("CasticSodaMnCosm") = casticsodamncon.Text
rs.Fields("RefOilMnProd") = refoilmnprod.Text
rs.Fields("OilDirtMnProd") = oildirtmnprod.Text
rs.Fields("Shortage") = shortage.Text


rs.Update
reset

rs.MoveLast
refno(1).Text = rs.Fields(0) + 1
ckg.SetFocus
End If
End If
End If
End If
End Sub

Private Sub Command5_Click()
Unload Me

End Sub



Private Sub cskg_KeyPress(Index As Integer, KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub cskg_KeyUp(Index As Integer, KeyCode As Integer, Shift As Integer)
b = Val(cskg(0).Text) / 37.324
casticsodamncon.Text = CStr(Round(b, 3))
End Sub

Private Sub delete_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from OilRefining where RefNo='" & Val(refno(1).Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "First Select a Record From Search Button"
Command2.SetFocus
If rs.State = adStateOpen Then rs.Close

rs.Open "OilRefining", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
refno(1).Text = 1
Else
rs.MoveLast
refno(1).Text = rs.Fields(0) + 1
End If
Exit Sub
End If
'///////////////////////////
If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select * from Stock1 where Prod_Id='" & Val(co.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_Out='" & Val(crudeoilmnused.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'///////////////////////////
If rs2.State = adStateOpen Then rs2.Close
rs2.Open "select * from Stock1 where Prod_Id='" & Val(id.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_Out='" & Val(casticsodamncon.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'/////////////////////////////
If rs3.State = adStateOpen Then rs3.Close
rs3.Open "select * from Stock1 where Prod_Id='" & Val(ro.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_In='" & Val(refoilmnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
If rs4.State = adStateOpen Then rs4.Close
rs4.Open "select * from Stock1 where Prod_Id='" & Val(dirto.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_In='" & Val(oildirtmnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
'////////////////////////////////
'********************
'Delete From Stock2 Table
If rs5.State = adStateOpen Then rs5.Close
rs5.Open "select * from Stock2 where Prod_Id='" & Val(ro.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_In='" & Val(refoilmnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic
If rs6.State = adStateOpen Then rs6.Close
rs6.Open "select * from Stock2 where Prod_Id='" & Val(dirto.Text) & "' and Tras_Date='" & refno(0).Text & "' and Qty_In='" & Val(oildirtmnprod.Text) & "'", cnn, adOpenDynamic, adLockOptimistic

'*********************
If MsgBox("Are you sure to delete this record ?", vbconfirmation + vbYesNo, "Shahbaz Oil Mill Pvt(Ltd)") = vbYes Then
rs6.delete
rs5.delete
rs4.delete
rs3.delete
rs2.delete
rs1.delete
rs.delete
'/////////////////////////////////
reset
refno(0).Text = ""
refno(0).Text = Date

If rs.State = adStateOpen Then rs.Close

rs.Open "OilRefining", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
refno(1).Text = 1
Else
rs.MoveLast
refno(1).Text = rs.Fields(0) + 1
End If
End If
reset

End Sub

Private Sub dkg_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub dkg_KeyUp(KeyCode As Integer, Shift As Integer)
d = Val(dkg.Text) / 37.324
oildirtmnprod.Text = CStr(Round(d, 3))


rshortage.Text = Val(ckg.Text) + Val(cskg(0).Text) - Val(rkg.Text) - Val(dkg.Text)
shortage.Text = Val(crudeoilmnused.Text) + Val(casticsodamncon.Text) - Val(refoilmnprod.Text) - Val(oildirtmnprod.Text)
End Sub

Private Sub Form_Load()
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hussain"
rs.Open "OilRefining", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
refno(1).Text = 1
Else
rs.MoveLast
refno(1).Text = rs.Fields(0) + 1
End If
refno(0).Text = Date
'Grid1.Visible = False
'stock1.Visible = False
End Sub

Public Sub reset()
 crudeoilmnused.Text = ""
 casticsodamncon.Text = ""
 refoilmnprod.Text = ""
 oildirtmnprod.Text = ""
 shortage.Text = ""
 ckg.Text = ""
 cskg(0).Text = ""
 rkg.Text = ""
 dkg.Text = ""
 rshortage.Text = ""
 '///////////////////////
refno(0).Text = ""
refno(0).Text = Date
If rs.State = adStateOpen Then rs.Close
rs.Open "OilRefining", cnn, adOpenStatic, adLockReadOnly, adCmdTable
If rs.RecordCount = 0 Then
refno(1).Text = 1
Else
rs.MoveLast
refno(1).Text = rs.Fields(0) + 1
End If
ckg.SetFocus
End Sub

Private Sub rkg_KeyPress(KeyAscii As Integer)
If Not (KeyAscii >= 48 And KeyAscii <= 57 Or KeyAscii = 8) Then
KeyAscii = 0
End If
End Sub

Private Sub rkg_KeyUp(KeyCode As Integer, Shift As Integer)
c = Val(rkg.Text) / 37.324
refoilmnprod.Text = CStr(Round(c, 3))
End Sub

Private Sub stock_Click()
If stock1.Visible = False And Grid1.Visible = False Then

Grid1.Visible = True
stock1.Visible = True
Grid1.ColWidth(0) = 1600
Grid1.ColWidth(1) = 1600
Grid1.ColWidth(2) = 1600
Grid1.ColWidth(3) = 1600
Grid1.ColWidth(4) = 1600
Grid1.TextMatrix(0, 0) = "Product"
Grid1.TextMatrix(0, 1) = "Total Purchase(Mn)"
Grid1.TextMatrix(0, 2) = "Used In Production"
Grid1.TextMatrix(0, 3) = "Remaining Stock(Mn)"
Grid1.TextMatrix(0, 4) = "Remaining Stock(Kg)"

Grid1.TextMatrix(1, 0) = "Crude Oil"
Grid1.TextMatrix(2, 0) = "Castic Soda"
'//////////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "Stock1", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
Exit Sub
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=5", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then
ad
Exit Sub
Else
'///////////////////////////////

If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=5", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(1, 1) = qin
Grid1.TextMatrix(1, 2) = qout
Grid1.TextMatrix(1, 3) = rstock
Grid1.TextMatrix(1, 4) = tkg

ad
End If
End If
Else
stock1.Visible = False
Grid1.Visible = False
End If
End Sub
Public Sub ad()
'/////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from Stock1 where Prod_Id=2", cnn, adOpenStatic, adLockReadOnly
If rs.RecordCount = 0 Then

Exit Sub
End If
'////////////////////////
If rs.State = adStateOpen Then rs.Close
rs.Open "select sum(Qty_In),sum(Qty_Out) from Stock1 where Prod_Id=2", cnn, adOpenDynamic, adLockOptimistic
qin = rs.Fields(0)
qout = rs.Fields(1)
rstock = rs.Fields(0) - rs.Fields(1)
tkg = Val(rstock * 37.324)
Grid1.TextMatrix(2, 1) = qin
Grid1.TextMatrix(2, 2) = qout
Grid1.TextMatrix(2, 3) = rstock
Grid1.TextMatrix(2, 4) = tkg
End Sub

Private Sub update_Click()

End Sub