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

Private Const SEP = "ой"                  'standard armstrong separator
Private Const USERSEP = ";"                  'user separator
Private Const DEFAULTREPORTCOLUMNWIDTH = 1500

'-- begin database fields
Private ml_Rpt_Tpl_ID As Long
Public Rpt_Tpl_ID_Parent As Long
Public Rpt_Tpl_Name As String
Public Rpt_Tpl_Master_Table As String
Public Rpt_Tpl_Master_Table_Prefix As String
Public Sql_Specific_Grouping As String
Public Sql_Specific_Having As String
Public Sql_Specific_Where As String
Public Rpt_Tpl_Comment As String
Public Rpt_Inherit As String
Public Rpt_Execute As String
Public Table_Business_Name As String
Public Owner As String
Public Mdl_Id As Long
'-- end database fields

Public RepeatLines As Boolean
Public GrandTotal As Boolean

Private mo_Fields As Collection
Private mo_Db As ARMSYSCOMLib.ArmDb
Private ms_UserID As String
Private ms_Language As String
Private mo_Parent As ArmRptTemplate
Private ms_SQL_Flag As String

Public Function Load_A_COM() As Boolean

  Set mo_Fields = New Collection
  Load_A_COM = True
End Function

Public Function Unload_A_COM() As Boolean

  Set mo_Fields = Nothing
  Set mo_Db = Nothing
  If Not (mo_Parent Is Nothing) Then
    Call mo_Parent.Unload_A_COM
    Set mo_Parent = Nothing
  End If
  Unload_A_COM = True
End Function

Public Property Let SQL_Flag(as_Value As String)
  If as_Value = "U" Then
    If ms_SQL_Flag = "" Then ms_SQL_Flag = as_Value
  Else
    ms_SQL_Flag = as_Value
  End If
End Property

Public Property Get SQL_Flag() As String
  SQL_Flag = ms_SQL_Flag
End Property

Public Property Let Rpt_Tpl_ID(al_Value As Long)
Dim lo_FieldDef As ArmFieldDef

  ml_Rpt_Tpl_ID = al_Value
  For Each lo_FieldDef In mo_Fields
    lo_FieldDef.Rpt_ID = al_Value
  Next
End Property

Public Property Get Rpt_Tpl_ID() As Long
  Rpt_Tpl_ID = ml_Rpt_Tpl_ID
End Property

Public Property Get Full_Master_Table() As String
  Full_Master_Table = Rpt_Tpl_Master_Table & " " & Rpt_Tpl_Master_Table_Prefix
End Property

Public Function LoadParent() As Boolean

  If Rpt_Tpl_ID_Parent = 0 Then
    LoadParent = Parent.Clear
  Else
    LoadParent = Parent.Load(Rpt_Tpl_ID_Parent)
  End If
End Function

Public Property Get Parent() As ArmRptTemplate

  If mo_Parent Is Nothing Then
    Set mo_Parent = New ArmRptTemplate
    Call mo_Parent.Load_A_COM
    Set mo_Parent.ArmDb = mo_Db
    mo_Parent.UserID = ms_UserID
    mo_Parent.Language = ms_Language
  End If
  Set Parent = mo_Parent
End Property

Public Property Set ArmDb(ao_Db As ARMSYSCOMLib.ArmDb)

  Set mo_Db = ao_Db
End Property

Public Property Get ArmDb() As ARMSYSCOMLib.ArmDb
  Set ArmDb = mo_Db
End Property

Public Property Let UserID(as_UserID As String)

  ms_UserID = as_UserID
End Property

Public Property Get UserID() As String
  UserID = ms_UserID
End Property

Public Property Let Language(as_Language As String)

  ms_Language = as_Language
End Property

Public Property Get Language() As String

  Language = ms_Language
End Property

Public Property Get ReadOnly() As Boolean
  ReadOnly = StrComp(ms_UserID, Owner, vbTextCompare) <> 0
End Property

Public Property Get Changed() As Boolean
Dim lo_FieldDef As ArmFieldDef

  Changed = False
  If SQL_Flag <> "" Then
    Changed = True
  Else
    For Each lo_FieldDef In mo_Fields
      If lo_FieldDef.SQL_Flag <> "" Then
        Changed = True
        Exit For
      End If
    Next
  End If
End Property

Public Function CanInherite() As Boolean
  CanInherite = Rpt_Inherit <> ""
End Function

Public Function CopyTo(ao_Template As ArmRptTemplate) As Boolean
Dim lb_Result As Boolean
Dim lo_FieldDef As ArmFieldDef
Dim lo_FieldDefCopy As ArmFieldDef

  lb_Result = False
  If Not (ao_Template Is Nothing) Then
    Call ao_Template.Clear
    ao_Template.Rpt_Tpl_ID = Rpt_Tpl_ID
    ao_Template.Rpt_Tpl_ID_Parent = Rpt_Tpl_ID_Parent
    ao_Template.Rpt_Tpl_Name = Rpt_Tpl_Name
    ao_Template.Rpt_Tpl_Master_Table = Rpt_Tpl_Master_Table
    ao_Template.Rpt_Tpl_Master_Table_Prefix = Rpt_Tpl_Master_Table_Prefix
    ao_Template.Sql_Specific_Grouping = Sql_Specific_Grouping
    ao_Template.Sql_Specific_Having = Sql_Specific_Having
    ao_Template.Sql_Specific_Where = Sql_Specific_Where
    ao_Template.Rpt_Tpl_Comment = Rpt_Tpl_Comment
    ao_Template.Table_Business_Name = Table_Business_Name
    ao_Template.Rpt_Inherit = Rpt_Inherit
    ao_Template.Rpt_Execute = Rpt_Execute
    ao_Template.Owner = Owner
    ao_Template.Mdl_Id = Mdl_Id
    For Each lo_FieldDef In mo_Fields
      Set lo_FieldDefCopy = New ArmFieldDef
      Call lo_FieldDef.CopyTo(lo_FieldDefCopy)
      Call ao_Template.Fields.Add(lo_FieldDefCopy)
    Next
    lb_Result = True
  End If
  CopyTo = lb_Result
End Function

Public Function Clone(as_Name As String) As Boolean
Dim lo_FieldDef As ArmFieldDef

  Rpt_Tpl_ID = 0
  Rpt_Tpl_Name = as_Name
  Rpt_Tpl_Comment = ""
  Rpt_Inherit = "I"
  Rpt_Execute = "E"
  Owner = ms_UserID
  SQL_Flag = "I"
  For Each lo_FieldDef In mo_Fields
    lo_FieldDef.SQL_Flag = "I"
  Next
  Clone = True
End Function

Public Function Inherite(as_Name As String) As Boolean
Dim lo_FieldDef As ArmFieldDef

  Rpt_Tpl_ID_Parent = Rpt_Tpl_ID
  Rpt_Tpl_ID = 0
  Rpt_Tpl_Name = as_Name
  Rpt_Tpl_Comment = ""
  Rpt_Inherit = "I"
  Rpt_Execute = "E"
  Owner = ms_UserID
  SQL_Flag = "I"
  For Each lo_FieldDef In mo_Fields
    lo_FieldDef.SQL_Flag = "I"
  Next
  Inherite = True
End Function

Public Function Create(as_Name As String, al_Mdl_Id As Long) As Boolean

  Call Clear
  Rpt_Tpl_Name = as_Name
  Rpt_Tpl_Comment = ""
  Rpt_Inherit = "I"
  Rpt_Execute = "E"
  Owner = ms_UserID
  SQL_Flag = "I"
  Mdl_Id = al_Mdl_Id
  Create = True
End Function

Public Function AddShare(as_Share As String) As Boolean
  AddShare = mo_Db.ExecuteSQL("rpt_share_ins " & Rpt_Tpl_ID & "," & _
    SQLStr(ms_UserID) & "," & SQLStr(as_Share))
End Function

Public Function CopyShareFrom(rpt_tpl_Id_Src As Long) As Boolean
  
  CopyShareFrom = False
  If (Rpt_Tpl_ID <> 0) And (rpt_tpl_Id_Src <> 0) Then
    CopyShareFrom = mo_Db.ExecuteSQL("exec rpt_share_copy " & rpt_tpl_Id_Src & "," & Rpt_Tpl_ID)
  End If
End Function

Public Function Refresh() As Boolean
  Refresh = Load(Rpt_Tpl_ID)
End Function

Public Function Load(al_rpt_tpl_ID As Long) As Boolean
Dim ll_Cursor As Long

  Load = False
  Call Clear
  If al_rpt_tpl_ID <> 0 Then
    ll_Cursor = mo_Db.OpenSQL("rpt_tpl_list_sel " & al_rpt_tpl_ID & ",'" & ms_UserID & "','" & ms_Language & "'")
    If (ll_Cursor <> 0) And (mo_Db.RowCount(ll_Cursor) = 1) Then
      Rpt_Tpl_ID = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_ID")
      Rpt_Tpl_ID_Parent = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_ID_Parent")
      Rpt_Tpl_Name = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_Name")
      Rpt_Tpl_Master_Table = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_Master_Table")
      Rpt_Tpl_Master_Table_Prefix = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_Master_Table_Prefix")
      Sql_Specific_Grouping = mo_Db.GetFields(ll_Cursor, "Sql_Specific_Grouping")
      Sql_Specific_Having = mo_Db.GetFields(ll_Cursor, "Sql_Specific_Having")
      Sql_Specific_Where = mo_Db.GetFields(ll_Cursor, "Sql_Specific_Where")
      Rpt_Tpl_Comment = mo_Db.GetFields(ll_Cursor, "Rpt_Tpl_Comment")
      Table_Business_Name = mo_Db.GetFields(ll_Cursor, "Table_Business_Name")
      Owner = mo_Db.GetFields(ll_Cursor, "Owner")
      Mdl_Id = mo_Db.GetFields(ll_Cursor, "Mdl_Id")
      Rpt_Inherit = mo_Db.GetFields(ll_Cursor, "Rpt_Inherit")
      Rpt_Execute = mo_Db.GetFields(ll_Cursor, "Rpt_Execute")
      Call mo_Db.Close(ll_Cursor)
      Load = LoadFields
    End If
  End If
End Function

Private Function LoadFields() As Boolean
Dim ll_Cursor As Long
Dim lo_FieldDef As ArmFieldDef

On Error GoTo ErrorHandler

  LoadFields = False
  Call ClearCollection(mo_Fields)
  ll_Cursor = mo_Db.OpenSQL("exec rpt_def_info_sel " & Rpt_Tpl_ID & ",'" & ms_Language & "'")
    Call mo_Db.First(ll_Cursor)
    Do While Not mo_Db.EOF(ll_Cursor)
      Set lo_FieldDef = New ArmFieldDef
      
      lo_FieldDef.Rpt_ID = mo_Db.GetFields(ll_Cursor, "Rpt_ID")
      lo_FieldDef.Out_Field_ID = mo_Db.GetFields(ll_Cursor, "Out_Field_ID")
      lo_FieldDef.Code_Selected = mo_Db.GetFields(ll_Cursor, "code_selected")
      lo_FieldDef.Code_Operator = mo_Db.GetFields(ll_Cursor, "code_operator")
      lo_FieldDef.Sql_Clause = Trim(mo_Db.GetFields(ll_Cursor, "sql_clause"))
      lo_FieldDef.Info_Displayed_To_User = Trim(mo_Db.GetFields(ll_Cursor, "Info_displayed_to_user"))
      lo_FieldDef.Field_selected = Trim(mo_Db.GetFields(ll_Cursor, "Field_selected"))
      lo_FieldDef.Total_Visible = Trim(mo_Db.GetFields(ll_Cursor, "total_visible"))
      lo_FieldDef.Field_sorted = Trim(mo_Db.GetFields(ll_Cursor, "field_sorted"))
      lo_FieldDef.Field_order = mo_Db.GetFields(ll_Cursor, "field_order")
      lo_FieldDef.Measure = Trim(mo_Db.GetFields(ll_Cursor, "measure"))
      lo_FieldDef.Group_By = Trim(mo_Db.GetFields(ll_Cursor, "group_by"))
      lo_FieldDef.Group_By_Weight = mo_Db.GetFields(ll_Cursor, "Group_By_Weight")
      lo_FieldDef.Report_Order = mo_Db.GetFields(ll_Cursor, "report_order")
      lo_FieldDef.Locked = Trim(mo_Db.GetFields(ll_Cursor, "locked"))
      lo_FieldDef.Join_Type = Trim(mo_Db.GetFields(ll_Cursor, "join_type"))
      lo_FieldDef.Logic = Trim(mo_Db.GetFields(ll_Cursor, "logic"))
      lo_FieldDef.Out_Field_Name = Trim(mo_Db.GetFields(ll_Cursor, "Out_Field_Name"))
      lo_FieldDef.Out_Business_Name = Trim(mo_Db.GetFields(ll_Cursor, "Out_Business_Name"))
      lo_FieldDef.Out_Business_Comment = Trim(mo_Db.GetFields(ll_Cursor, "Out_Business_Comment"))
      lo_FieldDef.Out_Come_From_Table = Trim(mo_Db.GetFields(ll_Cursor, "Out_Come_From_Table"))
      lo_FieldDef.Out_Field_Prefix = Trim(mo_Db.GetFields(ll_Cursor, "Out_Field_Prefix"))
      lo_FieldDef.Out_Master_Table = Trim(mo_Db.GetFields(ll_Cursor, "Out_Master_Table"))
      lo_FieldDef.Out_Master_Table_Prefix = Trim(mo_Db.GetFields(ll_Cursor, "Out_Master_Table_Prefix"))
      lo_FieldDef.Out_Field_Code = Trim(mo_Db.GetFields(ll_Cursor, "Out_Field_Code"))
      lo_FieldDef.Out_Can_Be_Measure = Trim(mo_Db.GetFields(ll_Cursor, "Out_Can_Be_Measure"))
      lo_FieldDef.Out_Can_Be_Group_By = Trim(mo_Db.GetFields(ll_Cursor, "Out_Can_Be_Group_By"))
      lo_FieldDef.Out_Measure_Function = Trim(mo_Db.GetFields(ll_Cursor, "Out_Measure_Function"))
      lo_FieldDef.Out_Order = mo_Db.GetFields(ll_Cursor, "Out_Order")
      lo_FieldDef.Out_Field_Join = Trim(mo_Db.GetFields(ll_Cursor, "out_field_join"))
      lo_FieldDef.Out_Hidden = Trim(mo_Db.GetFields(ll_Cursor, "out_hidden"))
      lo_FieldDef.Out_Format = mo_Db.GetFields(ll_Cursor, "Out_Format")
      lo_FieldDef.Out_Alignment = mo_Db.GetFields(ll_Cursor, "Out_Alignment")
      lo_FieldDef.Out_Width = mo_Db.GetFields(ll_Cursor, "Out_Width")
      lo_FieldDef.Out_Can_Change_join = mo_Db.GetFields(ll_Cursor, "Out_Can_Change_Join")
      lo_FieldDef.Out_Can_Select_Logic = mo_Db.GetFields(ll_Cursor, "Out_Can_Select_Logic")
      lo_FieldDef.Out_Crosslink_Request = mo_Db.GetFields(ll_Cursor, "Out_Crosslink_Request")
      lo_FieldDef.Out_Column_Hdr_Request = mo_Db.GetFields(ll_Cursor, "Out_Column_Hdr_Request")
      
      
      lo_FieldDef.Crt_Field_Code = mo_Db.GetFields(ll_Cursor, "Crt_Field_Code")
      lo_FieldDef.Crt_Field_Name = mo_Db.GetFields(ll_Cursor, "Crt_Field_Name")
      lo_FieldDef.Crt_Type = mo_Db.GetFields(ll_Cursor, "crt_type")
      lo_FieldDef.Crt_Come_From_Table = mo_Db.GetFields(ll_Cursor, "Crt_Come_From_Table")
      lo_FieldDef.Crt_Field_Prefix = mo_Db.GetFields(ll_Cursor, "Crt_Field_Prefix")
      lo_FieldDef.Crt_Report_Operator = mo_Db.GetFields(ll_Cursor, "Crt_Report_Operator")
      lo_FieldDef.Crt_Static_Request = mo_Db.GetFields(ll_Cursor, "crt_static_request")
      lo_FieldDef.Crt_Static_Request_Addition = mo_Db.GetFields(ll_Cursor, "crt_static_request_addition")
      lo_FieldDef.Crt_Grid_Tag = mo_Db.GetFields(ll_Cursor, "crt_grid_tag")
      
      lo_FieldDef.Out_Field_Tag = mo_Db.GetFields(ll_Cursor, "out_field_tag")
      'save initial code selected for exec mode, user can change original code_selected
      lo_FieldDef.Init_Code_Selected = lo_FieldDef.Code_Selected
      lo_FieldDef.Original = True
      Call mo_Fields.Add(lo_FieldDef)
      Call mo_Db.Next(ll_Cursor)
    Loop
  Call mo_Db.Close(ll_Cursor)
  LoadFields = True
  Exit Function
ErrorHandler:

End Function

Public Function AddFieldDef(ByVal al_Rpt_ID As Long, ByVal al_Out_Field_ID As Long) As ArmFieldDef
Dim lo_FieldDef As ArmFieldDef

  Set lo_FieldDef = New ArmFieldDef
  lo_FieldDef.Rpt_ID = al_Rpt_ID
  lo_FieldDef.Out_Field_ID = al_Out_Field_ID
  Call mo_Fields.Add(lo_FieldDef)
  Set AddFieldDef = lo_FieldDef
End Function

Public Property Get Fields() As Collection
  Set Fields = mo_Fields
End Property

Public Function GetFieldDef(ByVal av_Out_Field_ID As Variant) As ArmFieldDef
Dim lo_FieldDef As ArmFieldDef
  
  Set lo_FieldDef = Nothing
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Field_ID = Val(av_Out_Field_ID) Then Exit For
  Next
  Set GetFieldDef = lo_FieldDef
End Function
'generic method to clear collection
Private Sub ClearCollection(lo_Collection As Collection)

  If Not (lo_Collection Is Nothing) Then
    While lo_Collection.Count > 0
      Call lo_Collection.Remove(1)
    Wend
  End If
End Sub

Public Sub SortByReportOrder()
Dim ll_Count, ll_Index  As Integer
  
  ll_Count = mo_Fields.Count
  Do
    For ll_Index = 1 To ll_Count - 1
      If mo_Fields(ll_Index).Report_Order > mo_Fields(ll_Index + 1).Report_Order Then
        Call mo_Fields.Add(mo_Fields(ll_Index), , , ll_Index + 1)
        Call mo_Fields.Remove(ll_Index)
      End If
    Next ll_Index
    ll_Count = ll_Count - 1
  Loop Until ll_Count <= 1
End Sub

Public Sub SortByFieldOrder()
Dim ll_Count, ll_Index  As Integer
  
  ll_Count = mo_Fields.Count
  Do
    For ll_Index = 1 To ll_Count - 1
      If mo_Fields(ll_Index).Field_order > mo_Fields(ll_Index + 1).Field_order Then
        Call mo_Fields.Add(mo_Fields(ll_Index), , , ll_Index + 1)
        Call mo_Fields.Remove(ll_Index)
      End If
    Next ll_Index
    ll_Count = ll_Count - 1
  Loop Until ll_Count <= 1
End Sub

Public Sub SortByField(as_Field As String)
  
  If StrComp(as_Field, "REPORT_ORDER", vbTextCompare) = 0 Then
    Call SortByReportOrder
  ElseIf StrComp(as_Field, "FIELD_ORDER", vbTextCompare) = 0 Then
    Call SortByFieldOrder
  Else
    'this should never happen
    MsgBox ("Wrong order field")
  End If
End Sub

Public Function IsOKGroup_By_Weight() As Boolean
Dim lo_FieldDef As ArmFieldDef
Dim lb_Result As Boolean
Dim ll_Group_By_Weight As Long

  Call SortByField("Field_Order")
  lb_Result = True
  ll_Group_By_Weight = 999999999
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If lo_FieldDef.Field_selected <> "" Then
        If lo_FieldDef.Group_By <> "" Then
          If lo_FieldDef.Group_By_Weight > ll_Group_By_Weight Then
            lb_Result = False
            Exit For
          End If
          ll_Group_By_Weight = lo_FieldDef.Group_By_Weight
        Else
          Exit For
        End If
      End If
    End If
  Next
  IsOKGroup_By_Weight = lb_Result
End Function

Public Function GenerateSQL(ab_Distinct As Boolean) As String
Dim ls_Select_Fields As String
Dim ls_SQL_Master As String
Dim ls_Join As String
Dim ls_Where As String
Dim ls_having As String
Dim ls_Group_By As String
Dim ls_Order_By As String
Dim ls_Compute_By As String
Dim ls_SQL As String
Dim ls_Const As String 'mw 1.8.2008

  ls_SQL = ""
  ls_Const = Generate_Const 'mw 1.8.2008
  Call SortByField("Field_Order")
  ls_Select_Fields = Generate_Select_Fields
  ls_SQL_Master = SQL_Master
  ls_Join = Generate_Join
  ls_Where = Generate_Where
  ls_having = Generate_Having
  ls_Group_By = Generate_Group_By
  ls_Order_By = Generate_Order_By
  ls_Compute_By = Generate_Compute_By
  
  ls_SQL = ls_Const & vbCrLf & "SELECT " 'mw 1.8.2008
  If ab_Distinct And (Not ContainMeasureFields) Then ls_SQL = ls_SQL & "DISTINCT "
  ls_SQL = ls_SQL & ls_Select_Fields
  ls_SQL = ls_SQL & vbCrLf & "FROM " & ls_SQL_Master
  If ls_Join <> "" Then ls_SQL = ls_SQL & vbCrLf & ls_Join
  If ls_Where <> "" Then ls_SQL = ls_SQL & vbCrLf & "WHERE " & ls_Where
  If ls_Group_By <> "" Then ls_SQL = ls_SQL & vbCrLf & "GROUP BY " & ls_Group_By
  If ls_having <> "" Then ls_SQL = ls_SQL & vbCrLf & "HAVING " & ls_having
  If ls_Order_By <> "" Then ls_SQL = ls_SQL & vbCrLf & "ORDER BY " & ls_Order_By
  If ls_Compute_By <> "" Then ls_SQL = ls_SQL & vbCrLf & "COMPUTE " & ls_Compute_By
  
  GenerateSQL = ls_SQL
End Function

Public Function Generate_Select_Fields() As String
Dim lo_FieldDef As ArmFieldDef
Dim ls_Select_Fields As String
Dim ll_Index As Long

  ls_Select_Fields = ""
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
      For ll_Index = 0 To lo_FieldDef.DisplayFieldsCount - 1
        If ls_Select_Fields <> "" Then ls_Select_Fields = ls_Select_Fields & ","
        'ls_Select_Fields = ls_Select_Fields & lo_FieldDef.ColumnFieldName(ll_Index) & " AS """ & lo_FieldDef.ColumnFieldName(ll_Index) & """"
        ls_Select_Fields = ls_Select_Fields & lo_FieldDef.ColumnFieldName(ll_Index) & " AS """ & GetColumnTitle(lo_FieldDef, ll_Index) & """" ' mw 5.8.2008
      Next
    End If
  Next
  Generate_Select_Fields = ls_Select_Fields
End Function

'return variant array of used criteria
Public Function Generate_Criteria_List() As Variant
On Error GoTo ErrorHandler
Dim lo_FieldDef As ArmFieldDef
Dim ll_retCount As Long
Dim lv_retVal As Variant

    ll_retCount = 0
    lv_retVal = Empty
    
    For Each lo_FieldDef In mo_Fields
      If lo_FieldDef.Out_Hidden = "" And lo_FieldDef.Code_Operator <> "" And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
        ll_retCount = ll_retCount + 1
      End If
    Next
    
    If ll_retCount > 0 Then
      ReDim lv_retVal(1, ll_retCount - 1) As Variant
      ll_retCount = 0
      For Each lo_FieldDef In mo_Fields
        If lo_FieldDef.Out_Hidden = "" And lo_FieldDef.Code_Operator <> "" And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
            lv_retVal(0, ll_retCount) = lo_FieldDef.Out_Business_Name
            lv_retVal(1, ll_retCount) = lo_FieldDef.Info_Displayed_To_User
            ll_retCount = ll_retCount + 1
        End If
      Next
    End If
    Generate_Criteria_List = lv_retVal
    Exit Function
ErrorHandler:
End Function

Public Property Get SQL_Master() As String
  SQL_Master = Rpt_Tpl_Master_Table & " " & Rpt_Tpl_Master_Table_Prefix
End Property

Private Function IsSameRelation(ao_FieldDef1 As ArmFieldDef, ao_FieldDef2 As ArmFieldDef) As Boolean
  IsSameRelation = (StrComp(ao_FieldDef1.Out_Full_Come_From_Table, ao_FieldDef2.Out_Full_Come_From_Table, vbTextCompare) = 0) And _
                   (StrComp(ao_FieldDef1.Out_Full_Master_Table, ao_FieldDef2.Out_Full_Master_Table, vbTextCompare) = 0)
End Function

Private Function AddUniqueJoin(ao_Collection As Collection, ao_FieldDef As ArmFieldDef) As Boolean
Dim lo_FieldDef As ArmFieldDef

  AddUniqueJoin = False
  For Each lo_FieldDef In ao_Collection
    If IsSameRelation(ao_FieldDef, lo_FieldDef) Then
      Exit Function
    End If
  Next
  Call ao_Collection.Add(ao_FieldDef)
  AddUniqueJoin = True
End Function

Private Function AddUniqueField(ao_Collection As Collection, ao_FieldDef As ArmFieldDef) As Boolean
Dim lo_FieldDef As ArmFieldDef

  AddUniqueField = False
  For Each lo_FieldDef In ao_Collection
    If StrComp(ao_FieldDef.Out_Full_Field_Name, lo_FieldDef.Out_Full_Field_Name, vbTextCompare) = 0 Then
      Exit Function
    End If
  Next
  Call ao_Collection.Add(ao_FieldDef)
  AddUniqueField = True
End Function

Private Function FindJoinFieldDef(ao_Collection As Collection, as_Table As String) As ArmFieldDef
Dim lo_FieldDef As ArmFieldDef

  Set FindJoinFieldDef = Nothing
  For Each lo_FieldDef In ao_Collection
    If StrComp(lo_FieldDef.Out_Full_Come_From_Table, as_Table, vbTextCompare) = 0 Then
      
      If (lo_FieldDef.Out_Field_Join <> "") Or _
         (lo_FieldDef.Out_Full_Come_From_Table = Full_Master_Table) Then
      
      Set FindJoinFieldDef = lo_FieldDef
      Exit Function
    End If
    End If
  Next
End Function

Private Function AddMaster(ao_Collection As Collection, ao_FieldDef As ArmFieldDef) As Boolean
Dim lo_FieldDef As ArmFieldDef

  AddMaster = False
  If StrComp(ao_FieldDef.Out_Full_Come_From_Table, Full_Master_Table, vbTextCompare) = 0 Then
    'we don't need to add this join field, it is part of master table
    AddMaster = True
    Exit Function
  End If
  
  Set lo_FieldDef = FindJoinFieldDef(mo_Fields, ao_FieldDef.Out_Full_Master_Table)
  If lo_FieldDef Is Nothing Then
    'we didn't find master table in the definition, this bad template definition
    AddMaster = False
    Exit Function
  ElseIf lo_FieldDef Is ao_FieldDef Then
    'we would go into infinite loop in this case, so return false
    AddMaster = False
    Exit Function
  Else
    If AddMaster(ao_Collection, lo_FieldDef) Then
      'after ading master of this add this field join
      Call AddUniqueJoin(ao_Collection, ao_FieldDef)
      AddMaster = True
    End If
  End If
End Function

Private Function ReplaceJoinType(as_Join As String, as_Join_Type As String) As String

  'if no join type set, just copy the join to result
  Select Case UCase(as_Join_Type)
  Case "L"
    as_Join = Replace(as_Join, "INNER JOIN", "LEFT JOIN", , , vbTextCompare)
  Case "I"
    as_Join = Replace(as_Join, "LEFT JOIN", "INNER JOIN", , , vbTextCompare)
    as_Join = Replace(as_Join, "LEFT OUTER JOIN", "INNER JOIN", , , vbTextCompare)
  End Select
  ReplaceJoinType = as_Join
End Function

Public Sub SetJoin_Type(ao_FieldDef As ArmFieldDef, as_Join_Type As String)
Dim lo_FieldDef As ArmFieldDef

  For Each lo_FieldDef In mo_Fields
    'search only for all fields with the same relation as we want to add to join clause
    If IsSameRelation(ao_FieldDef, lo_FieldDef) Then
      '!!! all field from the relation MUST have can_change join type, especially hidden
      If lo_FieldDef.Out_Can_Change_join <> "" Then
        'update to the same join type
        lo_FieldDef.Join_Type = as_Join_Type
      End If
    End If
  Next
End Sub

Public Function GetJoin_Type(ao_FieldDef As ArmFieldDef) As String
Dim lo_FieldDef As ArmFieldDef
Dim ls_Join_Type As String

  ls_Join_Type = ""
  'take always first detected field - because fields in same relation MUST have the same join type
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      'search only for all fields with the same relation as we want to add to join clause
      If IsSameRelation(ao_FieldDef, lo_FieldDef) Then
        If lo_FieldDef.Out_Can_Change_join <> "" Then
          'check if left join is set
          If StrComp(lo_FieldDef.Join_Type, "L", vbTextCompare) = 0 Then
            'if there is at least one "LEFT" join field, then set type to left join and exit
            ls_Join_Type = "L"
            Exit For
          'check if inner join is set
          ElseIf StrComp(lo_FieldDef.Join_Type, "I", vbTextCompare) = 0 Then
            ls_Join_Type = "I"
            Exit For
          End If
        End If
      End If
    End If
  Next
  'we didn't detect by join type, try detect by text in join string - check all fields which have join string
  If ls_Join_Type = "" Then
    For Each lo_FieldDef In mo_Fields
      'search only for all fields with the same relation
      If IsSameRelation(ao_FieldDef, lo_FieldDef) Then
        If lo_FieldDef.Out_Field_Join <> "" Then
          If InStr(1, lo_FieldDef.Out_Field_Join, "LEFT JOIN", vbTextCompare) > 0 Then
            ls_Join_Type = "L"
            Exit For
          ElseIf InStr(1, lo_FieldDef.Out_Field_Join, "LEFT OUTER JOIN", vbTextCompare) > 0 Then
            ls_Join_Type = "L"
            Exit For
          ElseIf InStr(1, lo_FieldDef.Out_Field_Join, "INNER JOIN", vbTextCompare) > 0 Then
            ls_Join_Type = "I"
            Exit For
          End If
        End If
      End If
    Next
  End If
  GetJoin_Type = ls_Join_Type
End Function

Public Function Generate_Join() As String
Dim ls_Join As String
Dim lo_FieldDef As ArmFieldDef
Dim mo_JoinFields As New Collection

  ls_Join = ""
  
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then ' mw 1.8.2008 Then
      'add to sql only in case field is selected or there is criteria to be considered
      If (lo_FieldDef.Field_selected <> "") Or (lo_FieldDef.Sql_Clause <> "") Then
        'this will recursively add all masters to the root in proper order !!!
        If Not AddMaster(mo_JoinFields, lo_FieldDef) Then
          Call MsgBox("Template definition is missing master join condition for field: " & lo_FieldDef.Out_Business_Name)
          'sql will not work !!!
          Generate_Join = ""
          Exit Function
        End If
      End If
    End If
  Next
  
  For Each lo_FieldDef In mo_JoinFields
    If ls_Join <> "" Then ls_Join = ls_Join & vbCrLf
    
    ls_Join = ls_Join & ReplaceJoinType(lo_FieldDef.Out_Field_Join, GetJoin_Type(lo_FieldDef))
  Next
  Generate_Join = ls_Join
End Function

Public Function Generate_Where() As String
Dim ls_Where As String
Dim lo_FieldDef As ArmFieldDef

  ls_Where = Sql_Specific_Where
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then ' mw 1.8.2008
      If lo_FieldDef.Sql_Clause <> "" And Not lo_FieldDef.FieldNameContainMeasure Then
        If ls_Where <> "" Then ls_Where = ls_Where & " AND "
        ls_Where = ls_Where & "(" & lo_FieldDef.Sql_Clause & ")"
      End If
    End If
  Next
  Generate_Where = ls_Where
End Function

'mw 1.8.2008 >>
Public Function Generate_Const() As String
Dim ls_Const_part1 As String
Dim ls_Const_part2 As String
Dim lo_FieldDef As ArmFieldDef

  ls_Const_part1 = ""
  ls_Const_part2 = ""
  For Each lo_FieldDef In mo_Fields
    If InStr(lo_FieldDef.Crt_Type, "CONST") <> 0 Then ' mw 1.8.2008
        ls_Const_part1 = ls_Const_part1 + "declare " & lo_FieldDef.Out_Field_Name
        ls_Const_part2 = ls_Const_part2 + "set " & lo_FieldDef.Sql_Clause & ";" & vbCrLf
        Select Case lo_FieldDef.Crt_Type
        Case "NUM CONST"
            ls_Const_part1 = ls_Const_part1 & " float" & vbCrLf
        Case "CHAR CONST"
            ls_Const_part1 = ls_Const_part1 & " varchar(1000)" & vbCrLf
        Case "DATE CONST"
            ls_Const_part1 = ls_Const_part1 & " date" & vbCrLf
        End Select
    End If
  Next
  Generate_Const = ls_Const_part1 & ls_Const_part2
End Function
' mw <<

Public Function Generate_Having() As String
Dim ls_having As String
Dim lo_FieldDef As ArmFieldDef

  Generate_Having = ""
  If ContainMeasureFields Then
    ls_having = Sql_Specific_Having
    For Each lo_FieldDef In mo_Fields
      If lo_FieldDef.Out_Hidden = "" Then
        If lo_FieldDef.Sql_Clause <> "" And lo_FieldDef.FieldNameContainMeasure Then
          If ls_having <> "" Then ls_having = ls_having & " AND "
          ls_having = ls_having & "(" & lo_FieldDef.Sql_Clause & ")"
        End If
      End If
    Next
    Generate_Having = ls_having
  End If
End Function

Public Function Generate_Group_By() As String
Dim ls_Group_By As String
Dim lo_FieldDef As ArmFieldDef
Dim lo_GroupCol As New Collection

  ls_Group_By = ""
  If ContainMeasureFields Then
    ls_Group_By = Sql_Specific_Grouping
    Call SortByField("Report_Order")
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
        If (lo_FieldDef.Measure = "" Or lo_FieldDef.Group_By <> "") And _
           (lo_FieldDef.Report_Order > 0) And (Not lo_FieldDef.FieldNameContainMeasure) Then
          Call AddUniqueField(lo_GroupCol, lo_FieldDef)
        End If
      End If
    Next
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then  'mw 1.8.2008
        If (lo_FieldDef.Measure = "" Or lo_FieldDef.Group_By <> "") And _
           (lo_FieldDef.Report_Order = 0) And (Not lo_FieldDef.FieldNameContainMeasure) Then
          Call AddUniqueField(lo_GroupCol, lo_FieldDef)
        End If
      End If
    Next
    For Each lo_FieldDef In lo_GroupCol
      If ls_Group_By <> "" Then ls_Group_By = ls_Group_By & ","
      ls_Group_By = ls_Group_By & lo_FieldDef.Out_Full_Field_Name
    Next
  End If
  Generate_Group_By = ls_Group_By
End Function

Public Function Generate_Order_By() As String
Dim ls_Order_By As String
Dim lo_FieldDef As ArmFieldDef
Dim lb_MeasureExists As Boolean
Dim ll_Index As Long
Dim lo_OrderCol As New Collection

  ls_Order_By = ""
  Call SortByField("Report_Order")
  If GetCountOfMeasureFields > 0 Then
    'first put into order by clause all ordered columns
    For Each lo_FieldDef In mo_Fields
      If lo_FieldDef.Report_Order > 0 And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
        Call AddUniqueField(lo_OrderCol, lo_FieldDef)
      End If
    Next
    'then put all unordered columns in order they appear in report
    Call SortByField("Field_Order")
    For Each lo_FieldDef In mo_Fields
      If lo_FieldDef.Report_Order = 0 And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
        Call AddUniqueField(lo_OrderCol, lo_FieldDef)
      End If
    Next
  Else
    'put into report only fields which are ordered, there is for sure no measure
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Out_Hidden = "") And (lo_FieldDef.Field_selected <> "") And InStr(lo_FieldDef.Crt_Type, "CONST") = 0 Then 'mw 1.8.2008
        If lo_FieldDef.Field_sorted <> "" Then
          Call AddUniqueField(lo_OrderCol, lo_FieldDef)
        End If
      End If
    Next
  End If
  For Each lo_FieldDef In lo_OrderCol
    For ll_Index = 0 To lo_FieldDef.DisplayFieldsCount - 1
      If ls_Order_By <> "" Then ls_Order_By = ls_Order_By & ","
      If lo_FieldDef.Field_sorted = "ZA" Then
        ls_Order_By = ls_Order_By & lo_FieldDef.ColumnFieldName(ll_Index) & " DESC"
      Else
        ls_Order_By = ls_Order_By & lo_FieldDef.ColumnFieldName(ll_Index) & " ASC"
      End If
    Next
  Next
  Generate_Order_By = ls_Order_By
End Function

Public Function Generate_Compute_By() As String

  Generate_Compute_By = ""
End Function

Public Function Clear() As Boolean

  Rpt_Tpl_ID = 0
  Rpt_Tpl_ID_Parent = 0
  Rpt_Tpl_Name = ""
  Rpt_Tpl_Master_Table = ""
  Rpt_Tpl_Master_Table_Prefix = ""
  Sql_Specific_Grouping = ""
  Sql_Specific_Having = ""
  Sql_Specific_Where = ""
  Rpt_Tpl_Comment = ""
  Rpt_Inherit = ""
  Rpt_Execute = ""
  Owner = ""
  Mdl_Id = 0
  SQL_Flag = ""
  Call ClearCollection(mo_Fields)
  Clear = True
End Function

Public Function Validate() As String

  Validate = ""
  If Len(Rpt_Tpl_Comment) < 20 Then
    Validate = "COMMENT"
  End If
End Function

Public Function GetReportColumnCount() As Long
Dim ll_ColumnCount As Long
Dim lo_FieldDef As ArmFieldDef

  ll_ColumnCount = 0
  For Each lo_FieldDef In mo_Fields
    ll_ColumnCount = ll_ColumnCount + lo_FieldDef.DisplayFieldsCount
  Next
  GetReportColumnCount = ll_ColumnCount
End Function

Public Function GetGridSetColumnArray() As Variant
Dim lv_Columns As Variant
Dim lo_FieldDef As ArmFieldDef
Dim ll_Index As Long
Dim ll_ColumnIndex As Long
Dim ls_Measure As String, lb_Group_By As Boolean, lb_Total_Visible As Boolean
Dim ls_ColumnTitle As String
Dim ls_ColumnName As String

  ll_ColumnIndex = 0
  Call SortByField("Field_Order")
  ReDim lv_Columns(GetReportColumnCount - 1)
  For Each lo_FieldDef In mo_Fields
    For ll_Index = 0 To lo_FieldDef.DisplayFieldsCount - 1
      
      ls_Measure = lo_FieldDef.ColumnMeasure(ll_Index)
      lb_Group_By = (ls_Measure = "") And (lo_FieldDef.Group_By <> "")
      lb_Total_Visible = (ls_Measure = "") And (lo_FieldDef.Total_Visible <> "")
      
      ' mw 28.10.2008
      ls_ColumnName = lo_FieldDef.ColumnName(ll_Index)
      If ls_ColumnName = "" Then
        ls_ColumnName = lo_FieldDef.ColumnFieldName(ll_Index)
      End If
      
      ls_ColumnTitle = GetColumnTitle(lo_FieldDef, ll_Index)
      '<< mw
      
      lv_Columns(ll_ColumnIndex) = Join(Array( _
        ls_ColumnName, _
        IIf(lo_FieldDef.Out_Width = 0, DEFAULTREPORTCOLUMNWIDTH, lo_FieldDef.Out_Width), _
        "0", _
        ls_ColumnTitle, _
        ls_ColumnTitle, _
        "", _
        lo_FieldDef.Out_Format, _
        lo_FieldDef.Out_Alignment, _
        CStr(lb_Group_By), _
        CStr(lb_Total_Visible), _
        lo_FieldDef.ColumnMeasure(ll_Index)), SEP)

        ' mw 4.8.2008 - used ls_ColumnTitle for grid FieldName instead of -> lo_FieldDef.ColumnFieldName(ll_Index), _
        ' mw 4.8.2008 - used ls_ColumnName for grid Name instead of -> lo_FieldDef.ColumnFieldName(ll_Index), _

      ll_ColumnIndex = ll_ColumnIndex + 1
    Next
  Next
  GetGridSetColumnArray = lv_Columns
End Function

Public Function GetCountOfMeasureFields() As Long
Dim lo_FieldDef As ArmFieldDef
Dim la_Measure() As String
Dim ll_MeasureCount As Long

  ll_MeasureCount = 0
  For Each lo_FieldDef In mo_Fields
    If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
      If lo_FieldDef.Measure <> "" Then
        la_Measure = Split(lo_FieldDef.Measure, USERSEP)
        ll_MeasureCount = ll_MeasureCount + UBound(la_Measure) + 1
      End If
    End If
  Next
  GetCountOfMeasureFields = ll_MeasureCount
End Function

Public Function ContainMeasureFields() As Boolean
Dim lo_FieldDef As ArmFieldDef

  ContainMeasureFields = False
  For Each lo_FieldDef In mo_Fields
    If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
      If (lo_FieldDef.Measure <> "") Or lo_FieldDef.FieldNameContainMeasure Then
        ContainMeasureFields = True
        Exit Function
      End If
    End If
  Next
End Function

Public Function GetCountOfGroup_ByFields() As Long
Dim lo_FieldDef As ArmFieldDef
Dim ll_Group_ByCount As Long

  ll_Group_ByCount = 0
  For Each lo_FieldDef In mo_Fields
    If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
      If lo_FieldDef.Group_By <> "" Then
        ll_Group_ByCount = ll_Group_ByCount + 1
      End If
    End If
  Next
  GetCountOfGroup_ByFields = ll_Group_ByCount
End Function

Public Function GetMeasureColumnIndexes() As Variant
Dim lo_FieldDef As ArmFieldDef
Dim la_Measure() As String
Dim ll_Index As Long
Dim ll_ColumnIndex As Long
Dim ll_MeasureCount As Long
Dim lv_MeasureColumnIndexes As Variant

  lv_MeasureColumnIndexes = Empty
  ll_ColumnIndex = 0
  ll_MeasureCount = GetCountOfMeasureFields
  If ll_MeasureCount > 0 Then
    ReDim lv_MeasureColumnIndexes(ll_MeasureCount - 1)
    ll_MeasureCount = 0
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
        If lo_FieldDef.Measure <> "" Then
          la_Measure = Split(lo_FieldDef.Measure, USERSEP)
          For ll_Index = 0 To UBound(la_Measure)
            lv_MeasureColumnIndexes(ll_MeasureCount) = ll_ColumnIndex
            ll_ColumnIndex = ll_ColumnIndex + 1
            ll_MeasureCount = ll_MeasureCount + 1
          Next
        Else
          ll_ColumnIndex = ll_ColumnIndex + 1
        End If
      End If
    Next
  End If
  GetMeasureColumnIndexes = lv_MeasureColumnIndexes
End Function

Public Function GetMeasureColumnFunctions() As Variant
Dim lo_FieldDef As ArmFieldDef
Dim la_Measure() As String
Dim ll_Index As Long
Dim ll_MeasureCount As Long
Dim lv_MeasureColumnFunctions As Variant

  lv_MeasureColumnFunctions = Empty
  ll_MeasureCount = GetCountOfMeasureFields
  If ll_MeasureCount > 0 Then
    ReDim lv_MeasureColumnFunctions(ll_MeasureCount - 1)
    ll_MeasureCount = 0
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
        If lo_FieldDef.Measure <> "" Then
          la_Measure = Split(lo_FieldDef.Measure, USERSEP)
          For ll_Index = 0 To UBound(la_Measure)
            lv_MeasureColumnFunctions(ll_MeasureCount) = la_Measure(ll_Index)
            ll_MeasureCount = ll_MeasureCount + 1
          Next
        End If
      End If
    Next
  End If
  GetMeasureColumnFunctions = lv_MeasureColumnFunctions
End Function

Public Function GetMeasureCol(ByVal as_MeasureFunction As String) As String
Dim lo_FieldDef As ArmFieldDef
Dim la_Measure() As String
Dim ll_Index As Long

  GetMeasureCol = ""
  For Each lo_FieldDef In mo_Fields
    If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
      If lo_FieldDef.Measure <> "" Then
        la_Measure = Split(lo_FieldDef.Measure, USERSEP)
        For ll_Index = 0 To UBound(la_Measure)
          If StrComp(Trim(la_Measure(ll_Index)), Trim(as_MeasureFunction), vbTextCompare) = 0 Then
            ' GetMeasureCol = lo_FieldDef.Out_Full_Field_Name
            GetMeasureCol = lo_FieldDef.Out_Field_Tag 'mw 27.10.2008
            Exit Function
          End If
        Next
      End If
    End If
  Next
End Function


Public Function GetGroup_ByVisibility() As Variant
Dim lo_FieldDef As ArmFieldDef
Dim ll_Group_ByCount As Long
Dim lv_Group_ByVisibility As Variant

  lv_Group_ByVisibility = Empty
  ll_Group_ByCount = GetCountOfGroup_ByFields
  If ll_Group_ByCount > 0 Then
    ReDim lv_Group_ByVisibility(ll_Group_ByCount - 1)
    ll_Group_ByCount = 0
    For Each lo_FieldDef In mo_Fields
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Out_Hidden = "") Then
        If lo_FieldDef.Group_By <> "" Then
          lv_Group_ByVisibility(ll_Group_ByCount) = lo_FieldDef.Total_Visible <> ""
          ll_Group_ByCount = ll_Group_ByCount + 1
        End If
      End If
    Next
  End If
  GetGroup_ByVisibility = lv_Group_ByVisibility
End Function


Public Function GetSQL() As String
Dim ls_SQL As String

  ls_SQL = ""
  If SQL_Flag <> "" Then
    Select Case UCase(SQL_Flag)
    Case "I"
      ls_SQL = "exec rpt_tpl_list_ins " & _
        Rpt_Tpl_ID_Parent & "," & SQLStr(Rpt_Tpl_Name) & "," & _
        SQLStr(Rpt_Tpl_Master_Table) & "," & SQLStr(Rpt_Tpl_Master_Table_Prefix) & "," & _
        SQLStr(Sql_Specific_Grouping) & "," & SQLStr(Sql_Specific_Having) & "," & _
        SQLStr(Sql_Specific_Where) & "," & SQLStr(Rpt_Tpl_Comment) & "," & _
        SQLStr(Owner) & "," & Mdl_Id
    Case "U"
      ls_SQL = "exec rpt_tpl_list_upd " & Rpt_Tpl_ID & "," & _
        Rpt_Tpl_ID_Parent & "," & SQLStr(Rpt_Tpl_Name) & "," & _
        SQLStr(Rpt_Tpl_Master_Table) & "," & SQLStr(Rpt_Tpl_Master_Table_Prefix) & "," & _
        SQLStr(Sql_Specific_Grouping) & "," & SQLStr(Sql_Specific_Having) & "," & _
        SQLStr(Sql_Specific_Where) & "," & SQLStr(Rpt_Tpl_Comment) & "," & _
        SQLStr(Owner) & "," & Mdl_Id
    Case "D"
      ls_SQL = "exec rpt_tpl_list_del " & Rpt_Tpl_ID
    End Select
  End If
  GetSQL = ls_SQL
End Function

Public Function HasChildren() As Boolean
Dim ll_Cursor As Long

  If Rpt_Tpl_ID = 0 Then
    HasChildren = False
  Else
    HasChildren = True
    ll_Cursor = mo_Db.OpenSQL("SELECT COUNT(rpt_tpl_id) FROM rpt_tpl_list WHERE rpt_tpl_id_parent=" & Rpt_Tpl_ID)
    If (ll_Cursor <> 0) And (mo_Db.RowCount(ll_Cursor) = 1) Then
      HasChildren = mo_Db.GetFields(ll_Cursor, 0) > 0
    End If
    Call mo_Db.Close(ll_Cursor)
  End If
End Function

Public Function Save(al_ErrorCode As Long, Optional ab_ClearSQLFlags As Boolean = True, Optional ab_ApplyDelete As Boolean = True) As Long
Dim lb_Result As Boolean
Dim lo_FieldDef As ArmFieldDef
Dim ls_SQL As String
Dim ll_Cursor As Long
Dim ll_Index As Long

  al_ErrorCode = 0
  lb_Result = mo_Db.ExecuteSQL("BEGIN TRANSACTION")
  If lb_Result Then
    If SQL_Flag <> "" Then
      If SQL_Flag = "D" Then
        'delete all definition info for the template
        lb_Result = mo_Db.ExecuteSQL("exec rpt_def_info_del " & Rpt_Tpl_ID & ",NULL")
        If lb_Result Then
          'delete all share for the template
          lb_Result = mo_Db.ExecuteSQL("exec rpt_share_del " & Rpt_Tpl_ID & ",NULL,NULL")
        End If
      End If
      If lb_Result Then
        ls_SQL = GetSQL
        If ls_SQL = "" Then
          lb_Result = False
        Else
          ll_Cursor = mo_Db.OpenSQL(ls_SQL)
          If ll_Cursor Then
            If mo_Db.RowCount(ll_Cursor) = 1 Then
              Rpt_Tpl_ID = mo_Db.GetFields(ll_Cursor, 0)
            Else
              lb_Result = False
            End If
          Else
            al_ErrorCode = GetArrayValue(mo_Db.SQLErrorCodes, 0)
            lb_Result = False
          End If
          Call mo_Db.Close(ll_Cursor)
        End If
      End If
    End If
    If lb_Result Then
      If SQL_Flag <> "D" Then
        For Each lo_FieldDef In mo_Fields
          If lo_FieldDef.SQL_Flag <> "" Then
            ls_SQL = lo_FieldDef.GetSQL
            If ls_SQL = "" Then
              lb_Result = False
            Else
              lb_Result = mo_Db.ExecuteSQL(ls_SQL)
            End If
          End If
          If Not lb_Result Then
            Exit For
          End If
        Next
      End If
    End If
    If lb_Result Then
      lb_Result = mo_Db.ExecuteSQL("COMMIT TRANSACTION")
    Else
      Call mo_Db.ExecuteSQL("ROLLBACK TRANSACTION")
    End If
    If lb_Result Then
      If ab_ClearSQLFlags Then SQL_Flag = ""
      For ll_Index = mo_Fields.Count To 1 Step -1
        Set lo_FieldDef = mo_Fields(ll_Index)
        If lo_FieldDef.SQL_Flag = "D" Then
          If ab_ApplyDelete Then Call mo_Fields.Remove(ll_Index)
        Else
          If ab_ClearSQLFlags Then lo_FieldDef.SQL_Flag = ""
        End If
      Next
    End If
  End If
  Save = lb_Result
  Exit Function
ErrorHandler:
  Call mo_Db.ExecuteSQL("ROLLBACK TRANSACTION")
  Save = False
End Function

Public Function Delete() As Boolean
Dim lo_FieldDef As ArmFieldDef
Dim ll_ErrorCode As Long

  SQL_Flag = "D"
  Delete = Save(ll_ErrorCode)
End Function

Public Function SQLStr(ByVal as_str As String) As String
    
  SQLStr = "'" & Replace(as_str, "'", "''") & "'"
End Function

Public Function ReorderFields() As Boolean
Dim ll_FieldIndex As Long
Dim ll_ReportIndex As Long
Dim lo_FieldDef As ArmFieldDef

On Error GoTo ErrorHandler

  'resort fields
  Call SortByField("Field_Order")
  ll_FieldIndex = 1
  ll_ReportIndex = 1
  'put group by fields on begin of list and put hidden order to 0
  For Each lo_FieldDef In mo_Fields
    lo_FieldDef.SQL_Flag = "U"
    If lo_FieldDef.Out_Hidden = "" Then
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Group_By <> "") Then
        lo_FieldDef.Field_order = ll_FieldIndex
        lo_FieldDef.Report_Order = ll_ReportIndex
        ll_FieldIndex = ll_FieldIndex + 1
        ll_ReportIndex = ll_ReportIndex + 1
      End If
    Else
      lo_FieldDef.Field_order = 0
      lo_FieldDef.Report_Order = 0
    End If
  Next

  'put all other fields behind group by in the same sequence they was before
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If (lo_FieldDef.Field_selected <> "") And (lo_FieldDef.Group_By = "") Then
        lo_FieldDef.Field_order = ll_FieldIndex
        ll_FieldIndex = ll_FieldIndex + 1
      End If
    End If
  Next

  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If (lo_FieldDef.Field_selected = "") And (lo_FieldDef.Info_Displayed_To_User <> "") Then
        lo_FieldDef.Field_order = ll_FieldIndex
        ll_FieldIndex = ll_FieldIndex + 1
      End If
    End If
  Next

  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If (lo_FieldDef.Field_selected = "") And (lo_FieldDef.Info_Displayed_To_User = "") Then
        lo_FieldDef.Field_order = ll_FieldIndex
        ll_FieldIndex = ll_FieldIndex + 1
      End If
    End If
  Next
  'resort fields again by report order and set order of detail fields !!!
  Call SortByField("Report_Order")

  'then reorder already numbered field_order
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If (lo_FieldDef.Group_By = "") Or ((lo_FieldDef.Group_By <> "") And (lo_FieldDef.Field_selected = "")) Then
        If lo_FieldDef.Field_sorted = "" Then
          lo_FieldDef.Report_Order = 0
        Else
          If lo_FieldDef.Report_Order > 0 Then
            lo_FieldDef.Report_Order = ll_ReportIndex
            ll_ReportIndex = ll_ReportIndex + 1
          End If
        End If
      End If
    End If
  Next
  
  Call SortByField("Field_Order")

  'and on last assign all new detail ordered fields, give them order like they appear in list
  For Each lo_FieldDef In mo_Fields
    If lo_FieldDef.Out_Hidden = "" Then
      If lo_FieldDef.Field_sorted <> "" Then
        If lo_FieldDef.Report_Order = 0 Then
          lo_FieldDef.Report_Order = ll_ReportIndex
          ll_ReportIndex = ll_ReportIndex + 1
        End If
      End If
    End If
  Next
  ReorderFields = True
  Exit Function
ErrorHandler:
  ReorderFields = False
End Function

Public Function GetColumnTitle(ByRef ao_FieldDef As ArmFieldDef, ByVal al_Index As Long) As String
Dim ls_Request As String
Dim ls_Measure As String
Dim ls_Business_Name As String
Dim ll_Cursor As Long

  GetColumnTitle = ""
  ls_Request = ao_FieldDef.Out_Column_Hdr_Request
  ls_Measure = ao_FieldDef.ColumnMeasure(al_Index)
  ls_Business_Name = ao_FieldDef.Out_Business_Name
  
  If Trim(ls_Request) <> "" Then
    ls_Request = ReplacePlaceholderCode_Selected(ls_Request)
    ll_Cursor = mo_Db.OpenSQL(ls_Request)
    If ll_Cursor <> 0 Then
      If mo_Db.RowCount(ll_Cursor) > 0 Then
        ls_Business_Name = mo_Db.GetFields(ll_Cursor, 0)
      End If
      Call mo_Db.Close(ll_Cursor)
    End If
  End If

  If ao_FieldDef.Measure = "" Then
    GetColumnTitle = ls_Business_Name
  Else
    If ls_Measure = "" Then
      GetColumnTitle = ls_Business_Name
    Else
      GetColumnTitle = ls_Measure & "(" & ls_Business_Name & ")"
    End If
  End If

End Function


Private Function ReplacePlaceholderCode_Selected(ByVal as_Request As String) As String
Dim lo_FieldDef As ArmFieldDef
Dim ls_Placeholder As String
Dim ls_Data As String
Dim lv_Items As Variant
Dim ll_Index As Long

  For Each lo_FieldDef In mo_Fields
    ls_Data = ""
    ls_Placeholder = "$" & lo_FieldDef.Out_Field_Prefix & "." & lo_FieldDef.Out_Field_Name & "$"
    If Trim(lo_FieldDef.Code_Selected) <> "" Then
      Select Case UCase(lo_FieldDef.Crt_Type)
      Case "COMBO"
        ls_Data = lo_FieldDef.Code_Selected
      Case "GRID", "LIST"
        lv_Items = Split(lo_FieldDef.Code_Selected, USERSEP)
        For ll_Index = 0 To UBound(lv_Items)
          If ls_Data <> "" Then ls_Data = ls_Data & ","
          ls_Data = ls_Data & "'" & lv_Items(ll_Index) & "'"
        Next ll_Index
      Case "CHAR", "NUMERIC"
        lv_Items = Split(lo_FieldDef.Code_Selected, SEP)
        ls_Data = lv_Items(0)
      Case "DATE"
        ' in DB date is stored in US format (mm/dd/yyyy). Conversion to SQL fromat (yyyy-mm-dd) needed
        lv_Items = Split(lo_FieldDef.Code_Selected, SEP)
        ls_Data = lv_Items(0)
        lv_Items = Split(ls_Data, "/")
        If UBound(lv_Items) = 2 Then
          ls_Data = lv_Items(2) & "-" & lv_Items(0) & "-" & lv_Items(1)
        Else
          ls_Data = ""
        End If
      End Select
    End If
    as_Request = Replace(as_Request, ls_Placeholder, ls_Data, , , vbTextCompare)
  Next
  as_Request = Replace(as_Request, "$Lang$", "'" & ms_Language & "'")
  as_Request = Replace(as_Request, "$UID$", "'" & ms_UserID & "'")
  ReplacePlaceholderCode_Selected = as_Request
End Function

Private Function GetArrayValue(ByRef ao_variantArray As Variant, ByVal al_Index As Long) As Variant
    If IsArray(ao_variantArray) Then
        If UBound(ao_variantArray) <= al_Index Then
            GetArrayValue = ao_variantArray(al_Index)
        Else
            GetArrayValue = 0
        End If
    Else
        GetArrayValue = 0
    End If
End Function

