VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSELECT"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

'This class provides an easier and more consistent way to build a SQL SELECT statement
'Developed by Daniel Chirca

Private Const cnstSELECT = "SELECT"
Private Const cnstWHERE = "WHERE"
Private Const cnstAND = "AND"
Private Const cnstOR = "OR"
Private Const cnstNOT = "NOT"
Private Const cnstFROM = "FROM"
Private Const cnstORDERBY = "ORDER BY"
Private Const cnstAS = "AS"
Private Const cnstSep = " "

Private Type tTableType
  sTableName As String
  sTableAlias As String
End Type

Private Type tColumnType
  sColumnName As String
  sColumnAlias As String
End Type

Private Type tSelectColumnType
  tColumn As tColumnType
  tTable As tTableType
End Type

Private Type tAggregateColumnType
  sAggregateColumnName As String   'must contain the complete string
  sAggregateColumnAlias As String
End Type

Private Type tValueType
  vValue As Variant
  eComparedDataType As eDataType
  bCompareValueNotTranslated As Boolean
End Type

Private Type tConditionType
  tConditionColumn As tSelectColumnType
  eOperator As eOperatorType
  tValue As tValueType
End Type

Private Type tCustomConditionType
  sCustomCondition As String
End Type

Private Type tOrderType
  tOrderColumn As tSelectColumnType
  eOrderByType As eOrderType
End Type

Private m_Tables() As tTableType
Private m_SelectColumns() As tSelectColumnType
Private m_AggregateColumns() As tAggregateColumnType
Private m_OrderColumns() As tSelectColumnType
Private m_Conditions() As tConditionType
Private m_CustomCondition As tCustomConditionType
Private m_Order() As tOrderType

Private m_NullTable As tTableType

Private m_SELECT As String

Public Sub AddTable(strTableName As String, Optional strTableAlias As String)
  If Len(strTableName) > 0 Then
    ReDim Preserve m_Tables(0 To UBound(m_Tables) + 1)
    
    m_Tables(UBound(m_Tables)).sTableName = strTableName
    
    If Not IsMissing(strTableAlias) And Len(strTableAlias) > 0 Then
      m_Tables(UBound(m_Tables)).sTableAlias = strTableAlias
    Else
      m_Tables(UBound(m_Tables)).sTableAlias = vbNullString
    End If
  End If
End Sub

Public Sub AddSelectColumn(strColumnName As String, Optional strTableName As String, _
                           Optional strColumnAlias As String, _
                           Optional strTableAlias As String)
  Dim i As Integer
  Dim bFound As Boolean
  
  If Len(strColumnName) > 0 Then
    ReDim Preserve m_SelectColumns(0 To UBound(m_SelectColumns) + 1)
    m_SelectColumns(UBound(m_SelectColumns)).tColumn.sColumnName = strColumnName
    
    If Not IsMissing(strColumnAlias) And Len(strColumnAlias) > 0 Then
      m_SelectColumns(UBound(m_SelectColumns)).tColumn.sColumnAlias = strColumnAlias
    Else
      m_SelectColumns(UBound(m_SelectColumns)).tColumn.sColumnAlias = vbNullString
    End If
  
    If Not IsMissing(strTableName) And Len(strTableName) > 0 Then
      bFound = False
      For i = 0 To UBound(m_Tables)
        If strTableName = m_Tables(i).sTableName Then
          m_SelectColumns(UBound(m_SelectColumns)).tTable.sTableName = m_Tables(i).sTableName
          m_SelectColumns(UBound(m_SelectColumns)).tTable.sTableAlias = m_Tables(i).sTableAlias
          bFound = True
          Exit For
        End If
      Next i
      If Not bFound Then
        m_SelectColumns(UBound(m_SelectColumns)).tTable = m_NullTable
      End If
    Else
      If Not IsMissing(strTableAlias) And Len(strTableAlias) > 0 Then
        bFound = False
        For i = 1 To UBound(m_Tables)
          If strTableAlias = m_Tables(i).sTableAlias Then
            m_SelectColumns(UBound(m_SelectColumns)).tTable.sTableName = m_Tables(i).sTableName
            m_SelectColumns(UBound(m_SelectColumns)).tTable.sTableAlias = m_Tables(i).sTableAlias
            bFound = True
            Exit For
          End If
        Next i
        If Not bFound Then
          m_SelectColumns(UBound(m_SelectColumns)).tTable = m_NullTable
        End If
      Else
        m_SelectColumns(UBound(m_SelectColumns)).tTable = m_NullTable
      End If
    End If
  End If
End Sub

Public Sub AddAggregateColumn(strAggregateColumnName As String, _
                              Optional strAggregateColumnAlias As String)
  If Len(strAggregateColumnName) > 0 Then
    ReDim Preserve m_AggregateColumns(0 To UBound(m_AggregateColumns) + 1)
    
    m_AggregateColumns(UBound(m_AggregateColumns)).sAggregateColumnName = strAggregateColumnName
    
    If Not IsMissing(strAggregateColumnAlias) And Len(strAggregateColumnAlias) > 0 Then
      m_AggregateColumns(UBound(m_AggregateColumns)).sAggregateColumnAlias = strAggregateColumnAlias
    Else
      m_AggregateColumns(UBound(m_AggregateColumns)).sAggregateColumnAlias = vbNullString
    End If
  End If
End Sub

Public Sub AddOrderColumn(strOrderColumnName As String, _
                          Optional strOrderColumnAlias As String, _
                          Optional strTableName As String, _
                          Optional strTableAlias As String, _
                          Optional eOrder As eOrderType)
  Dim i As Integer
  Dim bFound As Boolean
  
  If Len(strOrderColumnName) > 0 Then
    ReDim Preserve m_Order(0 To UBound(m_Order) + 1)
    
    m_Order(UBound(m_Order)).tOrderColumn.tColumn.sColumnName = strOrderColumnName
    
    If Not IsMissing(strOrderColumnAlias) And Len(strOrderColumnAlias) > 0 Then
      m_Order(UBound(m_Order)).tOrderColumn.tColumn.sColumnAlias = strOrderColumnAlias
    Else
      m_Order(UBound(m_Order)).tOrderColumn.tColumn.sColumnAlias = vbNullString
    End If
    
    If Not IsMissing(strTableName) And Len(strTableName) > 0 Then
      bFound = False
      For i = 1 To UBound(m_Tables)
        If strTableName = m_Tables(i).sTableName Then
          m_Order(UBound(m_Order)).tOrderColumn.tTable.sTableName = m_Tables(i).sTableName
          m_Order(UBound(m_Order)).tOrderColumn.tTable.sTableAlias = m_Tables(i).sTableAlias
          bFound = True
          Exit For
        End If
      Next i
      If Not bFound Then
        m_Order(UBound(m_Order)).tOrderColumn.tTable = m_NullTable
      End If
    Else
      If Not IsMissing(strTableAlias) And Len(strTableAlias) > 0 Then
        bFound = False
        For i = 1 To UBound(m_Tables)
          If strTableAlias = m_Tables(i).sTableAlias Then
            m_Order(UBound(m_Order)).tOrderColumn.tTable.sTableName = m_Tables(i).sTableName
            m_Order(UBound(m_Order)).tOrderColumn.tTable.sTableAlias = m_Tables(i).sTableAlias
            bFound = True
            Exit For
          End If
        Next i
        If Not bFound Then
          m_Order(UBound(m_Order)).tOrderColumn.tTable = m_NullTable
        End If
      Else
        m_Order(UBound(m_Order)).tOrderColumn.tTable = m_NullTable
      End If
    End If
    
    If Not IsMissing(eOrder) Then
      m_Order(UBound(m_Order)).eOrderByType = eOrder
    Else
      m_Order(UBound(m_Order)).eOrderByType = eOrderType.cASC
    End If
  End If
End Sub

Public Sub AddConditionColumn(strConditionColumnName As String, _
                        eOperator As eOperatorType, _
                        varVal As Variant, _
                        Optional eValDataType As eDataType = eString, _
                        Optional bDoNotTranslate As Boolean = False, _
                        Optional strColumnAlias As String, _
                        Optional strTableName As String, Optional strTableAlias As String)

  Dim i As Integer
  Dim bFound As Boolean
  
  If Len(strConditionColumnName) > 0 Then
    ReDim Preserve m_Conditions(0 To UBound(m_Conditions) + 1)