يا جماعة انا عندي مشكلة وياريت حد يتكرم ويساعدني ويبقي جزاه الله عني كل خير
الموضوع : ان في مشروع معمول بvb6 وsql2000 والمشروع كبير جدا وفي نفس الوقت بياخد وقت في تنفيذ التقارير والاستعلامات فانا قلت ان انا لما احوله الى vb.net هيكون اسرع
اولا: ما مدى صحة هذا الكلام
ثانيا :ازاي احول المشروع الى vb.net بمعنى ماهو نظام الاتصال المستخدم
ثالثا :المشروع ملئ باستعلامات كما سيأتي المرفقات فما هى الاكواد المقابلة لها بvb.net
Dim n As Integer
Dim timefrom As String
Dim rsreworksub As Long
Dim timeto As String
Dim sql As String
Dim timetotal As String
Dim rsdowntime As New ADODB.Recordset
Dim rsdowntimew As New ADODB.Recordset
Dim rsmindowntime As New ADODB.Recordset
Dim rsmindowntimew As New ADODB.Recordset
Dim rsscada As New ADODB.Recordset
Dim machentry As New ADODB.Recordset
Dim acceptboxvalue As Long
Dim Rejectboxvalue As Long
Dim Producedvalue As Long
Dim Holidays As Long
Dim goodproduced As Double
Dim OffSpec As Double
Dim totaltonnage As Double
Dim prodorder As Long
Dim modvalue As Long
Dim maintenance As Long
Dim testing As Long
Dim others As Long
Dim shutdownlosses As Long
Dim loadingtime As Long
Dim line_id As Long
Dim team_id As Long
Dim equipmenttime As String
Dim minortime As String
Dim aviliblity As Double
Dim downtime As Double
Dim downtimew As Double
Dim noofstopw As Double
Dim accfirst As Long
Dim rjtfrist As Long
Dim acclast As Long
Dim reworkacum As Long
Dim rjtcum As Long
Dim productionacum As Double
Dim rjtlast As Long
Dim rsrestfalg As New ADODB.Recordset
Dim rssumaccepted As New ADODB.Recordset
Dim rssumrejected As New ADODB.Recordset
Dim acccum As Long
Dim rsddesignspeed As New ADODB.Recordset
Dim design As Double
Dim eqbreak As Double
Dim Change As Double
Dim cutting As Double
Dim startup As Double
Dim management As Double
Dim operation As Double
Dim eqother As Double
Dim totaldowntime As Double
Dim operatingtime As Double
Dim oeeperformance As Double
Dim oeequlty As Double
Dim oee As Double
Dim rsbreackdown As New ADODB.Recordset
Dim rschangeover As New ADODB.Recordset
Dim rscutting As New ADODB.Recordset
Dim rsstartup As New ADODB.Recordset
Dim rsmanagement As New ADODB.Recordset
Dim rsoperation As New ADODB.Recordset
Dim rsminorstops As New ADODB.Recordset
Dim rsothers As New ADODB.Recordset
Dim X As Integer
Dim rsetectrical As New ADODB.Recordset
Dim electricaltime As Double
Dim electericalnostops As Integer
Dim mechtime As Double
Dim specproduct As Double
Dim scrap As Double
Dim scrapkilo As Double
Dim operatingtimeratio As Double
Dim Minornostop As Double
Dim Minorratio As Double
Dim eqbreaknostop As Double
Dim mechtimeratio As Double
Dim noofstop As Double
Dim mttr As Double
Dim mtbf As Double
Dim TB_toa As String
Dim TB_frommonth As String
Dim timediffhourmonth As String
Dim timediffmonth As String
Dim rsminorstopsmtd As New ADODB.Recordset
Dim Minormtd As Double
Dim Minornostopmtd As Double
Dim rsbreackdownmtd As New ADODB.Recordset
Dim eqbreakmtd As Double
Dim eqbreaknostopmtd As Double
Dim rsetectricalmonth As New ADODB.Recordset
Dim rschangeovermonth As New ADODB.Recordset
Dim rscuttingmonth As New ADODB.Recordset
Dim rsstartupmonth As New ADODB.Recordset
Dim rsmanagementmonth As New ADODB.Recordset
Dim rsoperationmonth As New ADODB.Recordset
Dim rsothersmonth As New ADODB.Recordset
Dim cuttingmonth As Double
Dim electricaltimemonth As Double
Dim mechtimemonth As Double
Dim Changemonth As Double
Dim startupmonth As Double
Dim managementmonth As Double
Dim operationmonth As Double
Dim eqothermonth As Double
Dim rsscadab As New ADODB.Recordset
Dim rsreworksubb As Double
Dim Rejectboxvalueb As Double
Dim acceptboxvalueb As Double
Dim rssumacceptedb As New ADODB.Recordset
Dim acccumb As Double
Dim accfirstb As Double
Dim acclastb As Double
Dim machentrya As New ADODB.Recordset
Dim machentryb As New ADODB.Recordset
Dim goodproduceda As Double
Dim goodproducedb As Double
Dim OffSpecmtd As Double
Dim machentrymtd As New ADODB.Recordset
Dim goodproducedmtd As Double
Dim downtimemtd As Double
Dim noofstopmtd As Double
Dim mttrmtd As Double
Dim mtbfmtd As Double
Dim aviliblitymtd As Double
Dim oeeperformancemtd As Double
Dim oeequltymtd As Double
Dim oeemtd As Double
Dim fromdate As String
Dim fromtime As String
Dim J As Long
Dim fromonth As String
Dim fromyear As String
Dim tdatenow As Date
Dim datenow As String
Dim timenow As String
Dim TB_from As String
Dim TB_to As String
Dim TB_ff As String
Dim rsscadamtd As New ADODB.Recordset
Dim rsspeed As New ADODB.Recordset
Dim rsorganization As New ADODB.Recordset
Dim rslogistics As New ADODB.Recordset
Dim rsdefect As New ADODB.Recordset
Dim rsmeasurement As New ADODB.Recordset
Dim Y As Integer
Dim TB_fromf As String
Private Sub CmdExit_Click()
Main.Timer1.Enabled = True
Main.Timer2.Enabled = True
Main.Visible = True
Unload Me
End Sub
Private Sub Command1_Click()
sql = " delete from TB_Detil"
conn.Execute sql
'timediff = Int(DateDiff("d", TB_from, TB_to)) + 1
'timetotal = IIf(IsNull(timediff), "0", timediff * 24)
'If Me.Option1 = True Or Me.Option2 = True Then
' totaltime = 10 * (IIf(IsNull(timediff), "0", timediff * 24) - timediffhour)
' Else
'
'End If
'=========Date and time adjustements
Dim X As String
fromonth = Combo1.Text
fromyear = Combo2.Text
X = Combo3.Text
If Not IsNumeric(fromonth) Or fromonth = "" Or Not IsNumeric(fromyear) Or fromyear = "" Or Not IsNumeric(X) Or X = "" Then
MsgBox ("ÃÏÎá ÇáÞíã ÇáÕÍíÍÉ")
Exit Sub
End If
J = 0
Dim Y As Integer
If fromonth = 4 Or fromonth = 6 Or fromonth = 9 Or fromonth = 11 Then
Y = 30
Else
If fromonth = 2 Then
Y = 28
Else
Y = 31
End If
End If
For J = 1 To Y
TB_from = ""
fromdate = ""
fromtime = ""
TB_to = ""
X = J
If X = 1 Then
fromdate = fromyear & "/" & fromonth & "/" & X
fromtime = "07:30:00 am"
TB_ff = Format(fromdate, "yyyy/mm/dd") & " " & Format(fromtime, "HH:mm:ss")
End If
fromdate = fromyear & "/" & fromonth & "/" & X
fromtime = "07:30:00 am"
TB_from = Format(fromdate, "yyyy/mm/dd") & " " & Format(fromtime, "HH:mm:ss")
TB_fromf = TB_from
J = J + 1
X = J
If J > Y Then
fromonth = fromonth + 1
X = 1
If fromonth > 12 Then
fromonth = 1
fromyear = fromyear + 1
X = 1
End If
End If
'End If
fromdate = fromyear & "/" & fromonth & "/" & X
fromtime = "07:30:00 am"
'TB_to = Format(fromdate, "YYYY/MM/DD") & " " & Format(fromtime, "HH:mm:ss")
TB_to = Format(fromdate, "yyyy/mm/dd") & " " & Format(fromtime, "HH:mm:ss")
J = J - 1
X = J
totaltimemonth = 24
n = val(Combo3.Text)
Machine_Id = n
sql = " SELECT Designspeed From dbo.designspeedvalue WHERE (Machine_Id =" & n & ")"
rsddesignspeed.Open (sql), conn, adOpenStatic, adLockReadOnly
design = IIf(IsNull(rsddesignspeed!Designspeed) Or (rsddesignspeed.EOF = True), "0", rsddesignspeed!Designspeed)
rsddesignspeed.Close
'===============================================================
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '940') AND (Active = 1)AND (total_time < 10)AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID= " & n & ")"
Debug.Print sql
rsminorstops.Open (sql), conn, adOpenStatic, adLockReadOnly
Debug.Print sql
If Not rsminorstops.EOF Then
Minor = Format(IIf(IsNull(rsminorstops!downtime), "0", rsminorstops!downtime / 60), "0.0#")
Minornostop = Format(IIf(IsNull(rsminorstops!Noofstops), "0", rsminorstops!Noofstops), "0.0#")
Else
Minor = "0"
End If
rsminorstops.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '930') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & " )"
rsbreackdown.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsbreackdown.EOF Then
eqbreak = Format(Format(IIf(IsNull(rsbreackdown!downtime), "0", rsbreackdown!downtime / 60), "0.0#"), "0.0#")
Else
eqbreak = "0"
End If
rsbreackdown.Close
electricaltime = "0"
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '931') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
rschangeover.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rschangeover.EOF Then
Change = Format(IIf(IsNull(rschangeover!downtime), "0", rschangeover!downtime / 60), "0.0#")
Else
Change = "0"
End If
rschangeover.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '932') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID= " & n & ")"
Debug.Print sql
rscutting.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rscutting.EOF Then
cutting = Format(IIf(IsNull(rscutting!downtime), "0", rscutting!downtime / 60), "0.0#")
Else
cutting = "0"
End If
rscutting.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '933') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
'rsstartup.Close
rsstartup.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsstartup.EOF Then
startup = Format(IIf(IsNull(rsstartup!downtime), "0", rsstartup!downtime / 60), "0.0#")
Else
startup = "0"
End If
rsstartup.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '934') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
rsmanagement.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsmanagement.EOF Then
management = Format(IIf(IsNull(rsmanagement!downtime), "0", rsmanagement!downtime / 60), "0.0#")
Else
management = "0"
End If
rsmanagement.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '935') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID= " & n & ")"
rsoperation.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsoperation.EOF Then
operation = Format(IIf(IsNull(rsoperation!downtime), "0", rsoperation!downtime / 60), "0.0#")
Else
operation = "0"
End If
rsoperation.Close
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '936') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
rsothers.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsothers.EOF Then
eqother = Format(IIf(IsNull(rsothers!downtime), "0", rsothers!downtime / 60), "0.0#")
Else
eqother = "0"
End If
rsothers.Close
'
'
'
'============================================================================
======================
sql = "SELECT TOP 100 PERCENT SUM(Val) AS sum"
sql = sql + " From dbo.FloatTagRT_BagsNo"
sql = sql + " WHERE (Marker = 's') and (Status ='') AND (TagIndex = " & val(n - 1) & ")AND (DateAndTime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND DateAndTime <= CONVERT(DATETIME,'" & TB_to & " ', 102))"
sql = sql + " and(datepart(hour,DateAndTime) = '" & resethour & "')and (datepart(minute,DateAndTime) = '59')"
Debug.Print sql
rssumaccepted.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rssumaccepted.EOF Then
acccum = IIf(IsNull(rssumaccepted!Sum), "0", rssumaccepted!Sum)
Else
acccum = "0"
End If
sql = " SELECT DateAndTime, TagIndex, Val, Status, Marker"
sql = sql + " From dbo.FloatTagRT_BagsNo"
sql = sql + " WHERE (Marker = 's')and (Status ='') AND (TagIndex = " & val(n - 1) & ")AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) and dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))"
rsscada.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsscada.EOF Then
If Hour(TB_from) = resethour + 1 And Minute(TB_to) < 59 Then
accfirst = "0"
Else
rsscada.MoveFirst
accfirst = IIf(IsNull(rsscada!val), "0", rsscada!val)
End If
rsscada.MoveLast
acclast = IIf(IsNull(rsscada!val), "0", rsscada!val)
If Hour(TB_to) > resethour + 1 Then
acceptboxvalue = val(acccum) + val(acclast) - val(accfirst)
Else
If Hour(TB_to) = resethour + 1 And Minute(TB_to) < 59 Then
acceptboxvalue = val(acccum)
Else
acceptboxvalue = val(acclast) - val(accfirst)
End If
End If
Else
acceptboxvalue = "0"
End If
rsscada.Close
rssumaccepted.Close
'============================================================================
====
'=====New Modification Add Reworkmanual from productionmanual form and (goodproduct-rework)
sql = "SELECT TOP 100 PERCENT Machine_ID, dateandtime, rej_box From dbo.production_entry"
sql = sql + " Where (rej_box <> 0) And (Machine_Id = " & n & ") AND (DateAndTime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND DateAndTime <= CONVERT(DATETIME,'" & TB_to & " ', 102)) ORDER BY Machine_ID, dateandtime"
rsscada.Open (sql), conn, adOpenStatic, adLockReadOnly
If rsscada.EOF = True And rsscada.BOF = True Then
Rejectboxvalue = "0"
Else
Rejectboxvalue = IIf(IsNull(rsscada!rej_box), "0", rsscada!rej_box)
End If
rsscada.Close
'============================================================================
=======
'
'
'
'
'
sql = " SELECT AVG(Designspeed) AS Designspeed, SUM(Goodproduct) AS Goodproduct, SUM(OffSpec) AS OffSpec, SUM(Holidays) AS Holidays, SUM(Noproduction)AS Noproduction"
sql = sql + " , SUM(Planned_Mod) AS Planned_Mod, SUM(Planned_Mainten) AS Planned_Mainten, SUM(trialsandtest) AS trialsandtest,SUM(others) As others From dbo.machinedataentry"
sql = sql + " WHERE (Machine_Id =" & n & ")AND (dateandtime >= CONVERT(DATETIME,'" & TB_from & " ', 102) and dateandtime <= CONVERT(DATETIME,'" & TB_to & " ', 102))"
machentry.Open (sql), conn, adOpenStatic, adLockReadOnly
'
'
'goodproduced = Val(acceptboxvaluea) + Val(acceptboxvalueb) + IIf(IsNull(machentry!Goodproduct) Or (machentry.EOF = True), 0, machentry!Goodproduct) - IIf(IsNull(rsreworksuba), "0", rsreworksuba) - IIf(IsNull(rsreworksubb), "0", rsreworksubb) - Val(Rejectboxvaluea) - Val(Rejectboxvalueb)
StringRT = "dbo.StringRT" & n
FloatRT = "dbo.FloatRT" & n
TagRT = "dbo.TagRT" & n
FloatRT = "dbo.FloatRT" & n
sql = " SELECT DateAndTime, TagIndex, Val From " & FloatRT & "_Fault"
sql = sql + " WHERE (Val <> 0)and (Marker ='')and (Status ='') AND (TagIndex = 5) AND (DateAndTime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND DateAndTime <= CONVERT(DATETIME, '" & TB_to & " ', 102))"
Debug.Print sql
rsscada.Open (sql), conn, adOpenStatic, adLockReadOnly
If Not rsscada.EOF Then
rsreworksub = "0"
rsscada.MoveFirst
Do Until rsscada.EOF
rsreworksub = rsreworksub + Mid(rsscada!val, 3, 6)
rsscada.MoveNext
Loop
Else
rsreworksub = "0"
End If
rsscada.Close
goodproduced = val(acceptboxvalue) + IIf(IsNull(machentry!Goodproduct) Or (machentry.EOF = True), 0, machentry!Goodproduct) - IIf(IsNull(rsreworksub), "0", rsreworksub) - val(Rejectboxvalue)
If val(goodproduced) <= 0 Then
goodproduced = "0"
Else
goodproduced = goodproduced / 100
End If
OffSpec = val(Rejectboxvalue) + IIf(IsNull(machentry!OffSpec) Or (machentry.EOF = True), "0", machentry!OffSpec) + rsreworksub
If val(OffSpec) <= 0 Then
OffSpec = "0"
Else
OffSpec = OffSpec / 100
End If
'
If goodproduced = 0 Then
specproduct = 0
scrap = 0
scrapkilo = 0
totaltonnage = 0
Else
specproduct = Format((val(goodproduced) * 7.2 / 36000), "0.00#")
scrap = (OffSpec / goodproduced) * 100
scrapkilo = (OffSpec * 2 / 1000)
totaltonnage = (200 * val(goodproduced + OffSpec)) / 1000000
End If
''''''''''xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Holidays = IIf(IsNull(machentry!Holidays) Or machentry.EOF = True, "0", machentry!Holidays)
prodorder = IIf(IsNull(machentry!Noproduction) Or machentry.EOF = True, "0", machentry!Noproduction)
modvalue = IIf(IsNull(machentry!Planned_Mod) Or machentry.EOF = True, "0", machentry!Planned_Mod)
maintenance = IIf(IsNull(machentry!Planned_Mainten) Or machentry.EOF = True, "0", machentry!Planned_Mainten)
testing = IIf(IsNull(machentry!trialsandtest) Or machentry.EOF = True, "0", machentry!trialsandtest)
others = IIf(IsNull(machentry!others) Or machentry.EOF = True, "0", machentry!others)
machentry.Close
shutdownlosses = val(Holidays) + val(prodorder) + val(modvalue) + val(maintenance) + val(testing) + val(others)
loadingtime = Abs(val(totaltimemonth) - val(shutdownlosses))
totaldowntime = val(eqbreak) + val(Change) + val(cutting) + val(startup) + val(management) + val(operation) + val(eqother)
operatingtime = val(loadingtime) - val(totaldowntime)
If loadingtime = "0" Then
aviliblity = "0"
Else
aviliblity = Format(((val(loadingtime) - val(totaldowntime)) / val(loadingtime)) * 100, "0.0#")
operatingtimeratio = (operatingtime / loadingtime) * 100
Minorratio = (Minor / loadingtime) * 100
mechtimeratio = (mechtime / loadingtime) * 100
electricalratio = (electricaltime / loadingtime) * 100
downtime = Minor + eqbreak
noofstop = Minornostop + eqbreaknostop
'calculate mttr \\\\\\\\\\\\\\\waly edit 25/6/2008\\\\\\\\\\\\\\\\\\\
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '930') AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
rsdowntimew.Open (sql), conn, adOpenStatic, adLockReadOnly
Debug.Print sql
sql = "SELECT SUM(total_time) AS Downtime , COUNT(Fault_Id) AS Noofstops From dbo.Losses"
sql = sql + " WHERE (SUBSTRING(Fault_Id, 1, 3) = '940') AND (total_time >= 10) AND (Active = 1) AND (dateandtime >= CONVERT(DATETIME, '" & TB_from & " ', 102) AND"
sql = sql + " dateandtime <= CONVERT(DATETIME, '" & TB_to & " ', 102))AND (Machine_ID = " & n & ")"
rsmindowntimew.Open (sql), conn, adOpenStatic, adLockReadOnly
downtimew = IIf(IsNull(rsdowntimew!downtime), "0", rsdowntimew!downtime) + IIf(IsNull(rsmindowntimew!downtime), "0", rsmindowntimew!downtime)
noofstopw = IIf(IsNull(rsdowntimew!Noofstops), "0", rsdowntimew!Noofstops) + IIf(IsNull(rsmindowntimew!Noofstops), "0", rsmindowntimew!Noofstops)
rsdowntimew.Close
rsmindowntimew.Close
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
If downtimew = 0 Or noofstopw = 0 Then
mttrw = 0
mtbfw = 0
Else
mttrw = val(Format((downtimew / 60) / (noofstopw), "0.0#"))
mtbfw = Format(((loadingtime) - (downtimew / 60)) / (noofstopw), "0.0#")
End If
End If
' aviliblity = Format(((Val(loadingtime) - Val(totaldowntime)) / Val(loadingtime)) * 100, "0.0#")
If design = 0 Then
design = "1.8"
End If
If val(goodproduced) = 0 And val(OffSpec) = 0 Then
oeeperformance = 0
oeequlty = 0
oee = 0
Else
oeeperformance = Format(((val(goodproduced) + val(OffSpec)) / (val(design) * 60 * val(operatingtime))) * 100, "0.0#")
oeequlty = Format(val(goodproduced) / (val(goodproduced) + val(OffSpec)) * 100, "0.0#")
oee = Format((val(aviliblity) * val(oeeperformance) * val(oeequlty)) / 10000, "0.0#")
End If
'============================================================================
============================================================
'
If n >= 1 And n <= 10 Then
line_id = "1"
team_id = "1"
End If
If n >= 11 And n <= 12 Then
line_id = "2"
team_id = "2"
End If
If n >= 13 And n <= 20 Then
line_id = "2"
team_id = "3"
End If
If n >= 21 And n <= 30 Then
line_id = "3"
team_id = "4"
End If
If n >= 31 And n <= 40 Then
line_id = "4"
team_id = "5"
End If
'TB_from = Format(fromdate, "yyyy/dd/mm")
equipmenttime = "0"
TB_from = Format(TB_from, "YYYY/MM/DD")
sql = " INSERT TB_Detil (dayandtime,mac_id,load_time,prod_total,oee,mttr,mtbf) "
sql = sql + " VALUES ( '" & TB_from & "' ," & n & "," & operatingtime & "," & goodproduced & "," & oee & "," & mttrw & "," & mtbfw & ")"
Debug.Print sql
conn.Execute sql
'rsdowntime.Close
'rsmindowntime.Close
Next
With Main.CrystalReport1
.Reset
.ReportFileName = App.Path & "\OeeReport.rpt"
.Connect = conn.ConnectionString
' .DiscardSavedData = False
.WindowTitle = " ÊÞÑíÑ ÃÏÇÁ T.B Çáíæãí "
' .RetrieveDataFiles
.ReportSource = 0
.WindowShowPrintBtn = True
.WindowShowPrintSetupBtn = True
'.SQLQuery = "Select * from DataBaseProject order by EmployeeName"
' .ReportTitle = "????? ????? ????????"
.WindowShowPrintBtn = True
.WindowShowPrintSetupBtn = True
.Destination = crptToWindow
.PrintFileType = crptCrystal
.WindowState = crptMaximized
.WindowMaxButton = True
.WindowMinButton = True
.Formulas(1) = "currentdatefrom = " & "'" & TB_ff & " '"
.Formulas(2) = "currentdateto = " & "'" & TB_from & " '"
' .Formulas(3) = "currentmachine = "" ???????? """
' .SelectionFormula = strQryString
.Action = 1
End With
End Sub
Private Sub Form_Load()
'TB_from.Value = Format(Now, "yyyy/mm/dd 07:00:00 ")
'TB_to.Value = Format(Now, "yyyy/mm/dd 07:00:00")
Dim i As Integer
Dim val As String
For i = 1 To 6
Combo3.AddItem (i)
Next
For i = 1 To 12
If i < 9 Then
val = 0 & i
Else
val = i
End If
Combo1.AddItem (val)
Next
For i = 0 To 20
If i < 9 Then
val = "200" & i
Else
val = "20" & i
End If
Combo2.AddItem (val)
Next
End Sub
Private Sub Form_Unload(Cancel As Integer)
Main.Show
Unload Me
End Sub
