generateLicensesReportsToWord

Sub Initialize
    Dim ws As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim template As NotesDocument
    Dim rField1 As NotesRichTextItem
    Dim rField2 As NotesRichTextItem
    Dim embObject As NotesEmbeddedObject
    Dim path, fileName, templateName As String ' path to temp file, template for new files
    Dim countries List As String
    Dim pos, locNumber, locCount, rowCounter, entryCounter, generalCount As Integer

    'collecting data
    Dim collection As NotesView
    Dim CollectionDoc As NotesDocument
    Dim mailServer, FullName, intAddress, mailFile As String
    Dim amailServer, aFullName As NotesName
    Dim view As NotesView
    Dim countOfReports As Integer

    ' New document, which will be created somewhere
    Dim attSession As New NotesSession
    Dim attDb As NotesDatabase
    Dim attDoc As NotesDocument
    Set attDb = attSession.CurrentDatabase
    Set attDoc = New NotesDocument ( attDb )
    attDoc.Form = "SaveAttachments"
    Dim rtItem As NotesRichTextItem ' for storing attachments
    ' end of init


    Set db = session.CurrentDatabase
    Set view = db.GetView("reportTemplate")
    Set template = view.getfirstdocument
    If template Is Nothing Then
        MessageBox "No configuration document",16
        Exit Sub
    End If
    Set rField1 = template.getfirstitem("template")
    If rField1 Is Nothing Then
        MessageBox "No confiuration document with template",16
        Exit Sub
    End If
    Set embObject = rField1.GetEmbeddedObject("reporttemplate.doc")
    If embObject Is Nothing Then
        MessageBox "Configuration file was now found in configuration document",16
        Exit Sub
    End If


    path = "C:\Users\volodymyr.stoyko\Desktop\reports\"


    ' Initialize array of locations - those will be used as key to filter collected data
    countries("Marocco") = "AWSM"
    countries("Mexico") = "PUE"
    countries("Tunisia") = "JEN"
    countries("Germany") = "WOB|ING|BLN|CER|CHA|CHD|GRO|SHA|WUX|ZWI"
    countries("Poland") = "GOR"
    countries("Ukraine") = "TER|CHE"
    countries("Bolgaria") = "KAR|MEZ"
    countries("Slovakia") = "NIT|BRA"
    countries("Romania") = "CRN"

    ForAll location In countries
        pos = 0 'position in string with few locations

        'word variables
        Dim wordapp As Variant
        Dim worddoc As Variant
        Dim actDoc As Variant
        Dim table As Variant
        Dim selection As Variant

        templateName = Environ("TEMP") + "reporttemplate.doc"
        fileName = path + ListTag(location) + ".doc"
        Call embObject.Extractfile(templateName)
        Set wordapp = CreateObject("word.application")
        ' one way - extrct document and opent it for editing.
        ' second way - store the dot template and add this template for new docs
        Set worddoc = wordapp.documents.open(templateName)
        wordapp.visible = False

        countOfReports = template.GetItemValue("countOfReports")(0)
        countOfReports = countOfReports + 1

        rowCounter = 1
        generalCount = 0

        Set table = wordapp.Selection.Document.tables(2)

        ' there insert location and count of user in the en

        'Start collect data
        Set collection = db.GetView("_Personen")

        If InStr(location, "|") <> 0 Then

            While pos < Len(location)

                ' count locations for
                'Start collect data
                Set collection = db.Getview("_Personen")

                'If collection.Count  > 0 Then
                'MsgBox CStr(collection.Count) + " |  " + location
                'Беремо перший док
                Set CollectionDoc = collection.GetFirstDocument

                table.Rows.Add ' add location string
                rowCounter = rowCounter + 1
                locNumber = rowCounter
                entryCounter = 0 ' count of docs for location

                'поки не закінчилися дані
                Do While Not(CollectionDoc Is Nothing)
                    'MsgBox "blabla"
                    'if check if it is proper department
                    mailServer = CollectionDoc.GetItemValue("MailServer")(0)
                    FullName = CollectionDoc.GetItemValue("FullName")(0)
                    intAddress = CollectionDoc.GetItemValue("InternetAddress")(0)
                    mailFile = CollectionDoc.GetItemValue("MailFile")(0)
                    Set amailServer = New NotesName(mailserver)
                    Set aFullName = New NotesName(FullName)

                    If mailServer <> "" And FullName <> "" And  intAddress <> "" Then
                        If Right(FullName,Len(FullName) - InStr(FullName, "O=") - 1) = _
                        Left(Right(location,Len(location)-pos), 3)  Then
                            With table

                                .Rows.Add   ' add new row
                                rowCounter = rowCounter + 1
                                entryCounter = entryCounter + 1
                                ' NotesName abbreviated or this shit left-right-Instr
                                .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                                .Cell(rowcounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                                .Cell(rowCounter, 3).Range.InsertAfter(intAddress)
                                .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                                .Cell(rowCounter, 5).Range.InsertAfter(mailFile)

                            End With
                        End If

                    End If
                    'наступний документ
                    Set CollectionDoc  = collection.GetNextDocument(CollectionDoc)
                Loop
                'End If

                table.Rows(locNumber).Cells.Merge
                table.Cell(locNumber,1).Range.InsertAfter("Location: " + _
                Left(Right(location,Len(location)-pos), 3) + _
                ". Quantity of users: " + CStr(entryCounter))
                table.Rows(locNumber).Range.Font.Bold = True

                generalCount = generalCount + entryCounter
                pos = pos + 4
            Wend
            'general count of users count of rows - locations rows - first row
            'generalCount = rowCounter - locCount - 1
        Else

            Set collection = db.GetView("_Personen")

            '       If collection.Count>0 Then
            ' CStr(collection.Count) + " | " + location
            'Беремо перший док
            Set CollectionDoc  = collection.GetFirstDocument

            table.Rows.Add ' add location string
            locNumber = table.Rows.Count
            entryCounter = 0 ' count of docs for location
            rowCounter = rowCounter + 1
            'поки не закінчилися дані
            Do While Not(CollectionDoc Is Nothing)

                'if check if it is proper department
                mailServer = CollectionDoc.GetItemValue("MailServer")(0)
                FullName = CollectionDoc.GetItemValue("FullName")(0)
                intAddress = CollectionDoc.GetItemValue("InternetAddress")(0)
                mailFile = CollectionDoc.GetItemValue("MailFile")(0)
                Set amailServer = New NotesName(mailserver)
                Set aFullName = New NotesName(FullName)

                If mailServer <> "" And FullName <> "" And  intAddress <> "" Then
                    If Right(FullName,Len(FullName) - InStr(FullName, "O=") - 1) = _
                    location  Then
                        With table
                            .Rows.Add   ' add new row
                            rowCounter = rowCounter + 1
                            entryCounter = entryCounter + 1
                            ' NotesName abbreviated or this shit left-right-Instr
                            .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                            .Cell(rowCounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                            .Cell(rowCounter, 3).Range.InsertAfter(intAddress)
                            .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                            .Cell(rowCounter, 5).Range.InsertAfter(mailFile)
                            '                   MsgBox "Fuck"

                        End With
                    End If
                End If
                'наступний документ
                'MsgBox location
                Set CollectionDoc=collection.GetNextDocument(CollectionDoc)
            Loop

        End If

        generalCount = entryCounter

        table.Rows(locNumber).Cells.Merge
        table.Cell(locNumber,1).Range.InsertAfter("Location: " + location + _
        ". Quantity of users: " + CStr(generalCount))
        table.Rows(locNumber).Range.Font.Bold = True
        'End If

        ' summary
        Set table = wordapp.Selection.Document.tables(1)
        table.Cell(2,1).Range.InsertAfter(" " + CStr(countOfReports))
        table.Cell(2, 2).Range.InsertAfter(" " + ListTag(location))
        table.Cell(2, 3).Range.InsertAfter(" " + Format(Now, "mmmm yyyy"))
        'table.Cell(2, 3).Range.InsertAfter(" " + CStr(Day) + " " + CStr(Year))
        table.Cell(3, 1).Range.InsertAfter(" " + CStr(generalCount))

        'MsgBox CStr(rowCounter)
        Call worddoc.Saveas(fileName)
        worddoc.Close
        Set wordDoc = Nothing
        wordapp.Quit
        Set wordapp = Nothing

        'add new counter to template doc
        template.countOfReports = countOfReports
        Call template.Save(True, False)

    End ForAll


End Sub