Option Explicit
'database variables
Public Database As Object
Public Session As Object

'localization variables
Public NumberFormat As String

' Report variables
Dim DataBeginRow As Integer
Sub OpenDatabase()
    Set Database = CreateObject("TCPOS.ComServer.Database")
    Set Session = Database.CreateSession(GetConnection())
End Sub

'****************************************************************
Function GetConnection()
Dim Filename As String
Dim Result As String

  Filename = ThisWorkbook.Path & "\XLSConnection.ini"
  Result = ""
  If Dir(Filename, vbHidden) <> "" Then
    Open Filename For Input As #1
      Input #1, Result
    Close #1
  End If
  
  ' If the syntax SERVER connectionstring is used, retrieves the connection string
  If InStr(UCase(Result), "SERVER") > 0 Then
    Result = Mid(Result, InStr(Result, " ") + 1)
  End If
  
  GetConnection = Result
End Function

Sub SetDecimalChar(Session As Object)
Dim Separators As String

  Separators = Format(1000, "#,##0.00")
  Separators = Mid(Separators, 6, 1) + """"
End Sub

Sub SetupWorkbook()
  DataBeginRow = 2
  OpenDatabase
  ClearSheet
  DoReport
End Sub

Sub ClearSheet()
'
' Efface_Feuille Macro
'
'Effacement des feuilles Ets1 Ets2 et Global

Sheets("Global").Select
Range("A5:AD30000").Select
Selection.ClearContents


'Retour Feuille Paramètres
    Sheets("Paramètres").Select

End Sub

Sub DoReport()
Dim currRow As Integer
Dim sql As String
Dim table As Object
Dim record As Integer
  
'Application.ScreenUpdating = False
  
'Lecture des paramètres
Dim DateDebut As String
Dim DateFin As String
Dim lignedebut As Integer


Sheets("Paramètres").Select
     
DateDebut = Range("L14")
DateFin = Range("L23")

'----------------------------------------------
'Commentaires
'----------------------------------------------

Sheets("Global").Select
sql = " select TC.comment_id as Id, TC.comment_text as Description, COUNT(TC.comment_id) as NbreComment "
sql = sql + " from trans_comments TC"
sql = sql + " left join comments C on C.id = TC.comment_id"
sql = sql + " left join transactions TRA on TRA.id = TC.transaction_id"
sql = sql + " where TRA.bookkeeping_date between '" & DateDebut & "' and '" & DateFin & "' and C.sap_code is not null"
sql = sql + " group by TC.comment_id, TC.comment_text"
sql = sql + " order by TC.comment_id, TC.comment_text"

Set table = Session.SelectTable(sql)
lignedebut = 4
Cells(lignedebut, 1).Select

For record = 0 To table.RecordCount - 1
    table.RecordIndex = record
   'Nouvelle ligne
    lignedebut = lignedebut + 1
    Cells(lignedebut, 1) = CStr(table.fieldvaluebyName("Id"))
    Cells(lignedebut, 2) = CStr(table.fieldvaluebyName("Description"))
    Cells(lignedebut, 3) = CStr(table.fieldvaluebyName("NbreComment"))
       
   table.gonext
Next


'Application.ScreenUpdating = True
  
End Sub

Sub FetchGroups(StartRow As Long, startCol As Long, table As Object)
Dim recNum As Long
Dim fieldNum As Long

  For recNum = 0 To table.RecordCount - 1
    table.RecordIndex = recNum
    For fieldNum = 1 To table.FieldCount - 1
      ActiveSheet.Cells(StartRow + recNum, startCol + fieldNum - 1) = table.fieldValue(fieldNum)
    Next
  Next
  
End Sub


Function SqlDump(SqlStmt As String)
    Open "c:\dump.sql" For Output As #1
    Print #1, SqlStmt
    Close #1
End Function