Private mstrJobNumber As String
Private mstrJobName As String
Private mstrSubJobOf As String
Private mstrHeader As String
Private mstrDescription As String
Private mstrContact As String
Private mdblPercentComplete As Double
Private mstrStartDate As String
Private mstrFinishDate As String
Private mstrManager As String
Private mstrLinkedCustomer As String
Private mstrInactiveJob As String
Private mstrTrackReimburseables As String

Private cn As ADODB.Connection
Private mstrSQLDest As String
Private mstrSQLArgs As String

Private Const CLASS_NAME = "clsMYOBJob"

Public Sub CreateJob()
    Dim cmd As ADODB.Command
    Dim strSQL As String
    
    'On Error GoTo Proc_Err
    
    If cn Is Nothing Then
        If MYOBConnection Is Nothing Then
            Set MYOBConnection = New clsMYOBConnection
        End If
        
        If MYOBConnection.cn Is Nothing Then
            Set cn = MYOBConnection.CreateConnection
        Else
            Set cn = MYOBConnection.cn
        End If
    End If
    
    Set cmd = New ADODB.Command
    
    strSQL = "INSERT INTO Import_Jobs "
    DetermineDestinationFields
    ValidateRequiredFields
    
    If mstrSQLDest <> "" And mstrSQLArgs <> "" Then
        cn.BeginTrans
            strSQL = strSQL & "(" & mstrSQLDest & ") VALUES (" & mstrSQLArgs & ")"
            With cmd
                Set .ActiveConnection = cn
                .CommandText = strSQL
                .CommandType = adCmdText
                .Execute , , adExecuteNoRecords
                
                '.CommandText = "END TRANSACTION"
                '.Execute , , adExecuteNoRecords
            End With
            
        cn.CommitTrans
    End If
    
Proc_Exit:
    On Error Resume Next
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
End Sub

Public Function FindJob(Optional strJobName As String, _
                        Optional strJobNumber As String) As Variant
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    
    On Error GoTo Proc_Err
    
    If cn Is Nothing Then
        If MYOBConnection Is Nothing Then
            Set MYOBConnection = New clsMYOBConnection
        End If
        
        If MYOBConnection.cn Is Nothing Then
            Set cn = MYOBConnection.CreateConnection
        Else
            Set cn = MYOBConnection.cn
        End If
    End If
    
    Set rs = New ADODB.Recordset
    
    strSQL = "SELECT JobID FROM MYOB.Jobs WHERE "
    strSQL1 = IIf(strJobName = "", "", "JobName = '" & strJobName & "'")
    strSQL2 = IIf(strJobNumber = "", "", "JobNumber = '" & strJobNumber & "'")
    
    If strJobName <> "" And strJobNumber <> "" Then
        strSQL2 = " AND " & strSQL2
    End If
    
    rs.Open strSQL & strSQL1 & strSQL2, cn, adOpenStatic, adLockReadOnly
    If Not (rs.BOF And rs.EOF) Then
        FindJob = rs!JobID
    Else
        FindJob = Null
    End If
    
Proc_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Exit Function
    
Proc_Err:
    DoCmd.Beep

    
    FindJob = Null
    Resume Proc_Exit
    Resume
End Function

Private Sub DetermineDestinationFields()
    If mstrJobNumber <> "" Then
        mstrSQLDest = mstrSQLDest & "JobNumber,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrJobNumber & "',"
    End If
    
    If mstrJobName <> "" Then
        mstrSQLDest = mstrSQLDest & "JobName,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrJobName & "',"
    End If
    
    If mstrSubJobOf <> "" Then
        mstrSQLDest = mstrSQLDest & "SubJobOf,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrSubJobOf & "',"
    End If
    
    If mstrHeader = "" Then
        mstrHeader = "D"
    Else
        If mstrHeader <> "H" And mstrHeader <> "D" Then mstrHeader = "D"
    End If
    mstrSQLDest = mstrSQLDest & "Header,"
    mstrSQLArgs = mstrSQLArgs & "'" & mstrHeader & "',"
    
    If mstrDescription <> "" Then
        mstrSQLDest = mstrSQLDest & "Description,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrDescription & "',"
    End If
    
    If mstrContact <> "" Then
        mstrSQLDest = mstrSQLDest & "Contact,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrContact & "',"
    End If
    
    If mdblPercentComplete >= 0 Then
        mstrSQLDest = mstrSQLDest & "PercentComplete,"
        mstrSQLArgs = mstrSQLArgs & mdblPercentComplete & ","
    End If
    
    If mstrStartDate <> "" Then
        mstrSQLDest = mstrSQLDest & "StartDate,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrStartDate & "',"
    End If
    
    If mstrFinishDate <> "" Then
        mstrSQLDest = mstrSQLDest & "FinishDate,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrFinishDate & "',"
    End If
    
    If mstrManager <> "" Then
        mstrSQLDest = mstrSQLDest & "Manager,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrManager & "',"
    End If
    
    If mstrLinkedCustomer <> "" Then
        mstrSQLDest = mstrSQLDest & "LinkedCustomer,"
        mstrSQLArgs = mstrSQLArgs & "'" & mstrLinkedCustomer & "',"
    End If
    
    mstrSQLDest = mstrSQLDest & "InactiveJob,"
    If mstrInactiveJob <> "" Then
        mstrSQLArgs = mstrSQLArgs & "'" & mstrInactiveJob & "',"
    Else
        mstrSQLArgs = mstrSQLArgs & "'N',"
    End If
    
    mstrSQLDest = mstrSQLDest & "TrackReimburseables,"
    If mstrTrackReimburseables <> "" Then
        mstrSQLArgs = mstrSQLArgs & "'" & mstrTrackReimburseables & "',"
    Else
        mstrSQLArgs = mstrSQLArgs & "'N',"
    End If
        
    'Remove the trailing comma if it exists
    If mstrSQLDest <> "" Then mstrSQLDest = Left(mstrSQLDest, Len(mstrSQLDest) - 1)
    If mstrSQLArgs <> "" Then mstrSQLArgs = Left(mstrSQLArgs, Len(mstrSQLArgs) - 1)
End Sub

Private Sub ValidateRequiredFields()
    Dim strMessage As String
    Dim intError As Integer
    
    intError = 1
    
    If mstrJobNumber = "" Then
        intError = 2
        strMessage = "JobNumber is missing."
        GoTo Proc_Err
    End If
    
    If mstrJobName = "" Then
        intError = 2
        strMessage = "JobName is missing."
        GoTo Proc_Err
    End If
        
Proc_Exit:
    Exit Sub
    
Proc_Err:
    Err.Raise vbObjectError + intError, "ValidateRequiredFields", strMessage
End Sub

Private Sub Class_Initialize()
    InitializeVariables
    'Set MYOBConnection = New clsMYOBConnection
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
    cn.Close
    Set cn = Nothing
End Sub

Public Property Let JobNumber(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 15 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrJobNumber = strNewValue
End Property

Public Property Get JobNumber() As String
    JobNumber = mstrJobNumber
End Property

Public Property Let JobName(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 25 Then
        'Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        'Exit Property
        strNewValue = Left(strNewValue, 25)
    End If
    
    mstrJobName = strNewValue
End Property

Public Property Get JobName() As String
    JobName = mstrJobName
End Property

Public Property Let SubJobOf(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 15 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrSubJobOf = strNewValue
End Property

Public Property Get SubJobOf() As String
    SubJobOf = mstrSubJobOf
End Property

Public Property Let Header(strNewValue As String)
    strNewValue = Trim(strNewValue)
    
    If strNewValue <> "H" And strNewValue <> "D" Then
        Err.Raise vbObjectError + 5, CLASS_NAME, GetErrorMessage(5)
        Exit Property
    End If
    
    mstrHeader = strNewValue
End Property

Public Property Get Header() As String
    Header = mstrHeader
End Property

Public Property Let Description(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 255 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrDescription = strNewValue
End Property

Public Property Get Description() As String
    Description = mstrDescription
End Property

Public Property Let Contact(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 25 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrContact = strNewValue
End Property

Public Property Get Contact() As String
    Contact = mstrContact
End Property

Public Property Let PercentComplete(dblNewValue As Double)
    If dblNewValue < 0 Or dblNewValue > 1 Then
        Err.Raise vbObjectError + 2, CLASS_NAME, GetErrorMessage(2)
        Exit Property
    End If
    
    mdblPercentComplete = dblNewValue
End Property

Public Property Get PercentComplete() As Double
    PercentComplete = mdblPercentComplete
End Property

Public Property Let StartDate(dteNewValue As Date)
    mstrStartDate = CStr(dteNewValue)
End Property

Public Property Get StartDate() As Date
    StartDate = CDate(mstrStartDate)
End Property

Public Property Let FinishDate(dteNewValue As Date)
    mstrFinishDate = CStr(dteNewValue)
End Property

Public Property Get FinishDate() As Date
    FinishDate = CDate(mstrFinishDate)
End Property

Public Property Let Manager(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 25 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrManager = strNewValue
End Property

Public Property Get Manager() As String
    Manager = mstrManager
End Property

Public Property Let LinkedCustomer(strNewValue As String)
    strNewValue = Replace(Replace(Trim(strNewValue), """", "''"), "'", "''")
    
    If Len(strNewValue) > 50 Then
        Err.Raise vbObjectError + 1, CLASS_NAME, GetErrorMessage(1)
        Exit Property
    End If
    
    mstrLinkedCustomer = strNewValue
End Property

Public Property Get LinkedCustomer() As String
    LinkedCustomer = mstrLinkedCustomer
End Property

Public Property Let InactiveJob(strNewValue As String)
    strNewValue = Trim(strNewValue)
    
    If UCase(strNewValue) = "Y" Or UCase(strNewValue) = "N" Then
        mstrInactiveJob = Trim(strNewValue)
    Else
        Err.Raise vbObjectError + 3, CLASS_NAME, GetErrorMessage(3)
        Exit Property
    End If
End Property

Public Property Get InactiveJob() As String
    InactiveJob = mstrInactiveJob
End Property

Public Property Let TrackReimburseables(strNewValue As String)
    strNewValue = Trim(strNewValue)
    
    If UCase(strNewValue) = "Y" Or UCase(strNewValue) = "N" Then
        mstrTrackReimburseables = Trim(strNewValue)
    Else
        Err.Raise vbObjectError + 3, CLASS_NAME, GetErrorMessage(3)
        Exit Property
    End If
End Property

Public Property Get TrackReimburseables() As String
    TrackReimburseables = mstrTrackReimburseables
End Property

Sub InitializeVariables()
    'This procedure exists only if needed in the future
    Exit Sub
    
    mstrJobNumber = ""
    mstrJobName = ""
    mstrSubJobOf = ""
    mstrHeader = ""
    mstrDescription = ""
    mstrContact = ""
    'mdblPercentComplete As Double
    mstrStartDate = ""
    mstrFinishDate = ""
    mstrManager = ""
    mstrLinkedCustomer = ""
    mstrInactiveJob = ""
    mstrTrackReimburseables = ""
End Sub