VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "VacublastSystem"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
'hi chris
'this is going to calculate the cost of Vacublast standard Airblast equipment
'properties to set,...
    'room lenght
    'room width
    'pneumatic or mechanical reclaim floors
    'weather tank and accessories is purchased yes / no
    'weather dust collctor is purchased, yes / no
    
Dim xlSheet As Excel.Application
Dim PathName As String


Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)


Private PATH As String ' path for data base
Private m_roomLenght As String ' either "9ftLong" ,"12ftLong","18ftLong","24ftLong"
Private m_roomWidth As Integer
Private m_floorStyle As String
Private m_DustCollector As String
Private m_tankAndAccessories As String
Private m_floorConfiguration As String ' Full or Sweep, or Cross
Private m_reclaimers As String
Private m_IfFull As String
Private m_afterFilter As String ' yes/no

Private DbsVacublast As Database ' database object variable
Private rstReUse As Recordset 'reusable recordset object variable
Private m_ReclaimUnits(7) As Long
Private m_BlastRoom(7) As Long
Private m_ReclaimFloor(7) As Long
Private m_Collector As Long
Private m_MechCost(7) As Long
Private m_ElectCost(3) As Long
Public Rate As Integer ' engineering labour rate

Public JobNumber As String
Public SerialNumber As String

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

''''''''''''''''''''''''''''<Start setting properties>''''''''''''''''''''''''''''''''''''''''''''''''
Public Property Get ElectCost(i As Integer) ' we need to summarize, FLOOR, ROOM, RECLAIMERS, ELECTRICA,
ElectCost = m_ElectCost(i)
End Property

Public Property Get MechCost(i As Integer) ' we need to summarize, FLOOR, ROOM, RECLAIMERS, ELECTRICA,
m_MechCost(i) = m_ReclaimUnits(i) + m_BlastRoom(i) + m_ReclaimFloor(i)
MechCost = m_MechCost(i)
End Property

Public Property Get GetEngRate(i As Integer) '1=mechenghrs 2=electenghrs 3=rate
Dim rstRate As Recordset ' record set variable

Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object var
Set rstRate = DbsVacublast.OpenRecordset("Rates")
    rstRate.Index = "PrimaryKey"
   
    rstRate.Seek "=", "description"
              GetEngRate = rstRate(i)
            Rate = rstRate(3)
    rstRate.Close
    DbsVacublast.Close
End Property

Public Property Let FloorConfiguration(conf As String)
m_floorConfiguration = conf
End Property

Public Property Let RoomLenght(lght As String)
m_roomLenght = lght
End Property

Public Property Let RoomWidth(wdth As Integer)
m_roomWidth = wdth
End Property

Public Property Let floorStyle(flr As String)
m_floorStyle = flr
End Property

Public Property Let DustCollector(coll As String)
m_DustCollector = coll
End Property

Public Property Let TankAndAccessories(tank As String)
m_tankAndAccessories = tank
End Property
Public Property Let AfterFilter(Aftfilter As String)
m_afterFilter = Aftfilter
End Property
''''''''''''''''''''''''''<end setting properties>''''''''''''''''''''''''''''''''''''''''''''''''

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

''''''''''''''''''''''''''<Start calculating>''''''''''''''''''''''''''''''''''''''''''''''''



Public Sub GetBlastRoomCosts()
Dim rstBlastRoom As Recordset ' record set variable
Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object var
Dim i As Integer

If m_roomWidth = 9 Then
    Set rstBlastRoom = DbsVacublast.OpenRecordset("9ftBlastroom")
        rstBlastRoom.Index = "PrimaryKey"
        rstBlastRoom.Seek "=", m_roomLenght
            On Error Resume Next
        For i = 0 To 6
        m_BlastRoom(i) = rstBlastRoom(i + 2)
        Next i
        
    rstBlastRoom.Close
    DbsVacublast.Close
Else ' automaticaly 12ft.W
    Set rstBlastRoom = DbsVacublast.OpenRecordset("12ftBlastroom")
        rstBlastRoom.Index = "PrimaryKey"
        rstBlastRoom.Seek "=", m_roomLenght
            On Error Resume Next
        For i = 0 To 6
        m_BlastRoom(i) = rstBlastRoom(i + 2)
        Next i
        
    rstBlastRoom.Close
    DbsVacublast.Close
End If

End Sub

Public Sub GetReclaimFloorCosts()
Dim rstReclaimFloor As Recordset ' record set variable
Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object var
Dim i As Integer

Call IfFull

If m_floorStyle = "Pneumatic" Then '

    If m_roomWidth = 9 Then
        Set rstReclaimFloor = DbsVacublast.OpenRecordset("9PneuReclaimFloor")
            rstReclaimFloor.Index = "PrimaryKey"
            rstReclaimFloor.Seek "=", m_IfFull
                    On Error Resume Next
            For i = 0 To 6
            m_ReclaimFloor(i) = rstReclaimFloor(i + 2)
                                                        
            Next i
          
            rstReclaimFloor.Close
            DbsVacublast.Close
    Else ' automaticaly 12ft wide
        Set rstReclaimFloor = DbsVacublast.OpenRecordset("12PneuReclaimFloor")
            rstReclaimFloor.Index = "PrimaryKey"
            rstReclaimFloor.Seek "=", m_IfFull
                    On Error Resume Next
            For i = 0 To 6
            m_ReclaimFloor(i) = rstReclaimFloor(i + 2)
                                                        
            Next i
            
            rstReclaimFloor.Close
            DbsVacublast.Close
    End If
  
Else ' automaticaly Mechanical

    If m_roomWidth = 9 Then
        Set rstReclaimFloor = DbsVacublast.OpenRecordset("9MechReclaimFloor")
            rstReclaimFloor.Index = "PrimaryKey"
            rstReclaimFloor.Seek "=", m_IfFull
                    On Error Resume Next
            For i = 0 To 6
            m_ReclaimFloor(i) = rstReclaimFloor(i + 2)
                                                                                               
            Next i
      
            rstReclaimFloor.Close
            DbsVacublast.Close
    Else ' automaticaly 12ft wide
        Set rstReclaimFloor = DbsVacublast.OpenRecordset("12mechReclaimFloor")
            rstReclaimFloor.Index = "PrimaryKey"
            rstReclaimFloor.Seek "=", m_IfFull
                    On Error Resume Next
            For i = 0 To 6
            m_ReclaimFloor(i) = rstReclaimFloor(i + 2)
                                                                                               
            Next i
            
            rstReclaimFloor.Close
            DbsVacublast.Close
    End If
    
End If
End Sub

Public Sub GetReclaimersCosts()
Dim rstReclaimers As Recordset ' record set variable
Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object variable
Dim i As Integer

If m_floorStyle = "Mechanical" Then
    m_reclaimers = "MechModule"
Else ' automatically Pneumatic floor
    If m_floorConfiguration = "Sweep" Then ' if sweep in floor is selected
        m_reclaimers = "PneuModule(0)"
    ElseIf m_floorConfiguration = "Cross" Then ' if cross screw is selected
        m_reclaimers = "PneuModule(0)"
    ElseIf m_roomWidth = 9 Then               '  if full floor is selected
        If m_roomLenght = "9ftLong" Then
            m_reclaimers = "PneuModule(1)"
        ElseIf m_roomLenght = "12ftLong" Then
            m_reclaimers = "PneuModule(2)"
        ElseIf m_roomLenght = "18ftLong" Then
            m_reclaimers = "PneuModule(3)"
        Else ' automatically then it is 24ftLong
            m_reclaimers = "PneuModule(4)"
        End If
    ElseIf m_roomWidth = 12 Then
        If m_roomLenght = "9ftLong" Then
            m_reclaimers = "PneuModule(1)"
        ElseIf m_roomLenght = "12ftLong" Then
            m_reclaimers = "PneuModule(2)"
        ElseIf m_roomLenght = "18ftLong" Then
            m_reclaimers = "PneuModule(3)"
        Else ' automatically then it is 24ftLong
            m_reclaimers = "PneuModule(4)"
        End If
    End If
End If

Set rstReclaimers = DbsVacublast.OpenRecordset("reclaimers")
    rstReclaimers.Index = "PrimaryKey"
    rstReclaimers.Seek "=", m_reclaimers
                On Error Resume Next
        For i = 0 To 6
        m_ReclaimUnits(i) = rstReclaimers(i + 2)
        Next i
             
If m_tankAndAccessories = "Yes" Then ' adding to the purchases and total cost
rstReclaimers.Seek "=", "TankAcces"
    m_ReclaimUnits(4) = CLng(rstReclaimers(6)) + CLng(m_ReclaimUnits(4))
    m_ReclaimUnits(6) = CLng(rstReclaimers(8)) + CLng(m_ReclaimUnits(6))
Else
End If

Set rstReclaimers = Nothing
Set DbsVacublast = Nothing
End Sub

Public Function GetDustCollectorCosts()
Dim Qualifiers(4) As String ' an easy way to figure out all the combinations of collectors
Qualifiers(0) = (m_floorConfiguration = m_floorConfiguration) And (m_floorStyle = "Mechanical")
Qualifiers(1) = (m_floorConfiguration = "Full") And (m_floorStyle = "Pneumatic")
Qualifiers(2) = (m_floorConfiguration = "Cross") And (m_floorStyle = "Pneumatic")
Qualifiers(3) = (m_floorConfiguration = "Sweep") And (m_floorStyle = "Pneumatic")

Dim rstDustCollector As Recordset ' record set variable
Dim holder As String ' holder var for contatenating
Dim i As Integer
Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object variable

holder = m_roomWidth & "x" & Replace(m_roomLenght, "ftLong", "")

If m_DustCollector = "Yes" Then
    If Qualifiers(0) = True Then
        Set rstDustCollector = DbsVacublast.OpenRecordset("DustcollectorMech")
            rstDustCollector.Index = "PrimaryKey"
            rstDustCollector.Seek "=", holder
                m_Collector = rstDustCollector(2)
            
    ElseIf Qualifiers(1) = True Then 'Full floor pneumatic
        Set rstDustCollector = DbsVacublast.OpenRecordset("DustcollectorPneuFull")
            rstDustCollector.Index = "PrimaryKey"
            rstDustCollector.Seek "=", holder
                m_Collector = rstDustCollector(2)
            
    ElseIf Qualifiers(2) = True Then ' partial floor cross
        Set rstDustCollector = DbsVacublast.OpenRecordset("DustcollectorMech")
            rstDustCollector.Index = "PrimaryKey"
            rstDustCollector.Seek "=", holder
                m_Collector = rstDustCollector(2)
            
    ElseIf Qualifiers(3) = True Then ' sweep in floor
        Set rstDustCollector = DbsVacublast.OpenRecordset("DustcollectorMech")
            rstDustCollector.Index = "PrimaryKey"
            rstDustCollector.Seek "=", holder
                m_Collector = rstDustCollector(2)
            
    Else
        MsgBox "TooBad", , "didn`t work"
    End If
        If m_afterFilter = "yes" Then
            GetDustCollectorCosts = m_Collector + rstDustCollector(3)
        Else
            GetDustCollectorCosts = m_Collector
        End If
Else
    GetDustCollectorCosts = 0
End If

Set DbsVacublast = Nothing
Set rstDustCollector = Nothing
End Function

Public Sub GetElectricsCosts()
Dim rstElect As Recordset ' record set variable
Dim i As Integer
Set DbsVacublast = OpenDatabase(PATH, False, True, ";pwd=cjh") 'setting database object var
Set rstElect = DbsVacublast.OpenRecordset("ElectPanel")
    rstElect.Index = "PrimaryKey"
   
    rstElect.Seek "=", m_floorConfiguration
    
        For i = 0 To 2
                  m_ElectCost(i) = rstElect(i + 1)
        Next i
        
    rstElect.Close
    DbsVacublast.Close
End Sub


Private Sub IfFull()
If m_floorConfiguration = "Full" Then
   m_IfFull = Replace(m_roomLenght, "ong", "full")
Else
    m_IfFull = m_floorConfiguration
End If
End Sub




'**************************<Start Reporting>*************************************************
Public Sub Prints()
Set xlSheet = CreateObject("Excel.Application")
xlSheet.Application.DisplayAlerts = False
xlSheet.Workbooks.Open (PathName)
With xlSheet
   
    .Range("a5").Value = "Job Number : " & JobNumber
    .Range("a7").Value = "Serial Number : " & SerialNumber
    .Range("b10").Value = Me.GetEngRate(1) ' engineering mechanical
    .Range("b11").Value = Me.MechCost(0) ' labour hours
    .Range("b12").Value = Me.MechCost(2) ' steel wieght
    .Range("d12").Value = Me.MechCost(3) ' steel cost
    .Range("d13").Value = Me.MechCost(4) + Me.GetDustCollectorCosts ' Purchased items + dust collector/s
    .Range("d14").Value = Me.MechCost(5) ' Stock Items

    .Range("b15").Value = Round(m_ElectCost(0) / Me.GetEngRate(3), 0) ' eng hours
    .Range("b16").Value = Round(m_ElectCost(1) / Me.GetEngRate(2), 0) ' labour hours
    .Range("d17").Value = m_ElectCost(2) 'Purchased items
     End With
     
VacuBlastMain.MousePointer = vbHourglass
    xlSheet.ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
VacuBlastMain.MousePointer = vbDefault
     
xlSheet.Workbooks("templ.xls").Close savechanges:=False
xlSheet.Quit
Set xlSheet = Nothing
End Sub



Private Sub Class_Initialize()
VacuBlastMain.MousePointer = vbHourglass
'PATH = "C:\Program Files\Microsoft Visual Studio\Vb98\VB Work\Vacublast\Compile & support\vacublastpricing2.mdb"
'PathName = "C:\Program Files\Microsoft Visual Studio\Vb98\VB Work\Vacublast\Compile & support\templ.xls"
PATH = App.PATH & "\" & "vacublastpricing2.mdb"
PathName = App.PATH & "\" & "templ.xls"
VacuBlastMain.MousePointer = vbDefault
End Sub

Private Sub Class_Terminate()
Set xlSheet = Nothing
End Sub