Conquer Club

Posted: **Thu May 08, 2014 7:30 pm**

Welcome back, my friends, to the show that never ends! We have come at last to the 117th running of the CC Dispatch!

Happy Victoria Day to those of us fortunate to live in the former Dominions and enjoy the blessings of the British parliamentary system! And for all those others, I wish that you too may find something to be happy about on this beautiful sunny Victoria Day weekend!

Two Dispatch contests are currently available. You will find Jigs Fantasy Draft in the Platoon Report, and the 9th round of iAmCaffeine's Dispatch Trivia Contest in the Mess Hall. Both of these contests include fabulous prizes, so be sure check them out.

War Games includes updates on the current state of the TPA, our monthly list of tournament winners, and a new article in the Dave's Tips Series!

In Cartography, [player]Seamus76[/player] has returned and has another roundup of maps in development, as well as a description of his very own Ancient Text Technique.

Our Interrogation Room features [player]Hayesez[/player]. I'll say no more and let it speak for itself!

Finally, I would like to welcome [player]jak111[/player] to the Dispatch staff. His first article is in the Mess Hall. Also in the Mess Hall, you will find [player]Razorvich[/player]'s summary of the Live Chat Event during the Easter Celebration.

Community Tab

show

Whac-a-Mod

show

Use our convenient navigation buttons to get to your favourite section.

Posted: **Thu May 15, 2014 12:27 am**

Hello friends, and welcome to your favourite part of the Newsletter, "War Games", where you can find information about CC Tournaments, and other stuff in the Tournament scene!

We continue to deliver our news staples with [player]iAmCaffeine[/player]'s April summary of Tournament Winners, my update of TPA happenings in TPA Wrap, and I have also added one of my occasional Tournament Tips which was prompted by a query from [player]BGtheBrain[/player] and features fantastic xml coding from [player]MrBenn[/player].

Good Reading and Good Luck!

[player]DaveH[/player]
Tournament Executive

Tournament Winners Update: April '14 by iAmCaffeine

show

TPA Wrap by DaveH

show

Tournament Tips #53 by DaveH
[spoiler=getting game data from a list of game numbers.][player]BGtheBrain[/player] posted the following query in the Tools and Tips forum:

Would it be possible to make a sheet formula where I could input a game # in column A, then column B would reflect Map Name, Column C would show player x points won/lost for each player?

The only way I know is to copy the game details and paste into Excel where you can then play with the data. However the wonderful [player]MrBenn[/player] knows xml inside out and posted a routine which draws out the details directly from the website into Excel. I have added a few minor details into the code to meet [player]BGtheBrain[/player]'s requirements and the result is a routine that TO's can use to give them most of the data from a list of games. Not satisfied with that, [player]MrBenn[/player] has updated and tidied the coding to extract all the data from a given tournament. However, you will have to wait for the next edition to receive that - and there may even be further "tweeks" to come!

The Excel macro routine is as follows:

Code: Select all

Sub SumScores()
'
' Game Nos titles in cell(1A)
' List of game numbers in column A from cell(2A)
'
Call get_cc_gamedata(R)

Range(Cells(2, 5), Cells(R, 5)).Select

Selection.Copy
Cells(2, 12).Select
ActiveSheet.Paste

Range(Cells(2, 7), Cells(R, 7)).Select
Selection.Copy
Cells(2, 13).Select
ActiveSheet.Paste

Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

j = 1
While j = 1
    j = 0
    For i = 2 To R - 1
        A = Cells(i, 12).Value
        B = Cells(i + 1, 12).Value
        If A = B And A <> "" Then
         Cells(i, 13).Value = Cells(i, 13).Value + Cells(i + 1, 13).Value
            Cells(i + 1, 12).Value = ""
            Cells(i + 1, 13).Value = ""
            j = 1
        End If

    Next i

    Range(Cells(2, 12), Cells(R, 13)).Select
    Selection.Sort Key1:=Range(Cells(2, 12), Cells(R, 13)), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Wend
    
    Range(Cells(2, 12), Cells(R, 13)).Select
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Sort Key1:=Range(Cells(2, 13), Cells(R, 13)), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

        
End Sub
    Sub Get_cc_tournament()

    Dim tourney As String
    Dim APIpath As String
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xNode As MSXML2.IXMLDOMNode

    Dim gData()
    Dim pData()

    Dim pRange As Range
    Dim pStats As Range

    Dim headers(1 To 2)
    headers(1) = Array("Game No", "Tournament Label", "Map", "Players", "Game Type", "Round Limit", "Status")
    headers(2) = Array("Player Name", "Elim Order", "Kills", "Points", "Result", "Round")

    'Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
    Col = UBound(headers(1)) - LBound(headers(1)) + 1
    Cells(1, 1).Resize(1, Col).Value = headers(1)

    Cells(1, Col + 1).Resize(1, UBound(headers(2)) - LBound(headers(2)) + 1).Value = headers(2)
    Col = Col + UBound(headers(2)) - LBound(headers(2)) + 1

    CurPage = 1
    nextRow = 2
        'Get tournament name from inputbox
        Deftourney = ActiveSheet.Name
        'Deftourney = "7 Man Madness!"
        tourney = InputBox("Please enter a Conquer Club Tournament Name:", , Deftourney)
        If tourney = "" Then Exit Sub
       
    Do
        APIpath = "http://www.conquerclub.com/api.php?mode=gamelist&to=" & tourney _
                    & "&names=Y&events=Y"
        If CurPage > 1 Then APIpath = APIpath & "&page=" & CurPage
       
        Set xmlDoc = New MSXML2.DOMDocument
       
        With xmlDoc
            'Load the xml from CC API
            .async = False
            .validateOnParse = False
            .Load (APIpath)
             
            'Get number of pages
            Set xNode = .FirstChild.SelectSingleNode("page")
            With xNode
                l = InStr(.Text, " ")
                CurPage = CInt(Left(.Text, l))
                l = InStrRev(.Text, " ")
                MaxPage = CInt(Mid(.Text, l))
            End With
           
            'Get number of games in xml
            Set xNode = .FirstChild.SelectSingleNode("games")
            MaxGames = xNode.Attributes.getNamedItem("total").Text
            FileGames = xNode.ChildNodes.Length
           
            For g = 0 To (FileGames - 1) Step 1
               
                With xNode.ChildNodes(g)
       
                    ReDim gData(1 To 7)
                    gData(1) = .SelectSingleNode("game_number").Text
                    gData(1) = "=HYPERLINK(""http://www.conquerclub.com/game.php?game=" & _
                                        gData(1) & """,""" & gData(1) & """)"
                    gData(2) = .SelectSingleNode("tournament").Text
                    gData(3) = .SelectSingleNode("map").Text
                    gData(4) = .SelectSingleNode("players").ChildNodes.Length
                    gData(5) = .SelectSingleNode("game_type").Text
                    gData(6) = .SelectSingleNode("round_limit").Text
                    gData(7) = .SelectSingleNode("game_state").Text
                   
                   
                    Select Case gData(5) 'Game Type
                        Case "S"
                            gData(5) = "Standard"
                        Case "C"
                            gData(5) = "Terminator"
                        Case "A"
                            gData(5) = "Assassin"
                        Case "P"
                            gData(5) = "Polymorphic"
                        Case "D"
                            gData(5) = "Doubles"
                        Case "T"
                            gData(5) = "Triples"
                        Case "Q"
                            gData(5) = "Quads"
                        Case Else
                            'Unknown Game Type
                    End Select
                   
                    Select Case gData(7) 'game state (W)aiting, (A)ctive or (F)inished
                        Case "W"
                            gData(7) = "Waiting"
                        Case "A"
                            gData(7) = "Active"
                        Case "F"
                            gData(7) = "Finished"
                        Case Else
                            'Unknown Game State
                    End Select
                   
                   
                    'Range("A1:A" & UBound(GData)) = WorksheetFunction.Transpose(GData)
                    Range(Cells(nextRow, 1), Cells(nextRow, UBound(gData))) = gData
                    'nextRow = nextRow + 1
                       
                    ReDim pData(1 To gData(4), 0 To 5)
                    '(playerNo, (0=name, 1=Elim Order, 2=Kills, 3=Points, 4=Result, 5=Round) )
           
                    For p = 1 To gData(4)
                        With .SelectSingleNode("players").ChildNodes(p - 1)
                            pData(p, 0) = .Text
                            pData(p, 4) = .Attributes.getNamedItem("state").NodeValue
                        End With 'Players
                        pData(p, 5) = .SelectSingleNode("round").Text
                    Next p
                   
                    ko = 1
                    For e = 1 To .SelectSingleNode("events").ChildNodes.Length
                        With .SelectSingleNode("events").ChildNodes(e - 1)
                            'GameXML.childNodes(19).childNodes(e - 1)
                           
                            If Right(.Text, 7) = " points" Then
                                l = InStr(.Text, " ")
                                p = CInt(Left(.Text, l))
                                pData(p, 3) = pData(p, 3) + _
                                                 CInt(Replace(Replace(Replace( _
                                                      Mid(.Text, l, Len(.Text)), _
                                                      "loses", "-"), "gains", "+"), "points", ""))
                            ElseIf Right(.Text, 14) = " from the game" Then
                                l = InStr(.Text, " ")
                                p = CInt(Left(.Text, l))
                                If Not p = 0 Then 'not eliminated by neutral
                                    pData(p, 2) = pData(p, 2) + 1
                                End If
                                t = .Text
                                t = Mid(.Text, l, Len(.Text))
                                pData(CInt(Replace(Replace( _
                                            Mid(.Text, l, Len(.Text)), _
                                            "eliminated", ""), "from the game", "")) _
                                            , 1) = ko     ' pData(p,4) = Kill Order
                                ko = ko + 1
                               
                            End If
                        End With
                    Next e
                               
                    Cells(nextRow, UBound(gData) + 1).Resize(UBound(pData, 1) - LBound(pData, 1) + 1, UBound(pData, 2) - LBound(pData, 2) + 1).Value = pData
                    nextRow = nextRow + gData(4)
                   
                    With Rows(nextRow).EntireRow.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                   
                End With 'Game(g) 'xNode.childNodes(g)
                   
            Next g
           
        End With 'xmlDoc

    CurPage = CurPage + 1
    Loop While CurPage <= MaxPage

    '#### All Game Data has now been imported (will error if too many rows for spreadsheet)
    Cells(1, Col + 1).Value = CStr(MaxGames) & " Games"

    Set pRange = Range(Cells(2, 8), Cells(nextRow - 1, 8))
    uPlayers = UniqueList(pRange)

    StatCol = 16   'Col P
    Set pStats = Range("P2:P" & CStr(UBound(uPlayers) + 2))

    Cells(1, StatCol).Value = "Player Name"
    pStats = WorksheetFunction.Transpose(uPlayers)

    Set pStats = pRange.Offset(0, 1)

    ' NEED TO ADD IN SOME STATS FORMULAE
    ' ALSO NEED TO CALCULATE WHICH ROUND AN ELIMINATION TOOK PLACE


    End Sub
    Function UniqueList(Optional ListRange As Range)

        If ListRange Is Nothing Then Set ListRange = Selection

        Dim List As Variant
        List = ListRange.Value
       
        With CreateObject("scripting.dictionary")
            For Each element In List
                If Not element = Empty Then c00 = .Item(element)
            Next
            UniqueList = .keys
        End With
           
    End Function

You write in the list of game numbers that you wish to interrogate starting in cell A2.

You run the routine "SumScores" and get the following data written into your spreadsheet:

Across the top you get the number of players in the game; the type of game; map name; list of players' names; whether they won or lost; points gained or lost; number of kills; elimination order and the final round number. There is also a summary of the total points won or lost by each player.

Note that the game types are as follows:
"S" = "Standard"; "C" = "Terminator"; "A" = "Assassin"; "P" = "Polymorphic"; "D" = "Doubles"; "T" = "Triples" and "Q"= "Quads".

With many thanks to [player]MrBenn[/player] for allowing me to share his complex and powerful code and [player]BGtheBrain[/player] for stimulating the creation of a routine that will make TOs' job in summarising tournament points far easier. (I can see complex scoring tournaments coming up!)[/spoiler]

Use our convenient navigation buttons to get to your favourite section.

Posted: **Fri May 16, 2014 9:49 am**

Welcome to another edition of the Platoon Report, where we bring you the latest happenings in the clan world here on Conquer Club!

Week 1 of Clan League 6 continues, with week 2 coming right up. Visit the Clan Leagues tab of the Clans page yet, where you can get a quick look at where all the matches stand - in real time! The page is here!

Have you checked to see who your clan faces in Conquer Cup 5 yet? The bracket is updated here.

The CC4 semi-final match between TOFU and ACE now stands at 29-28 in favor of TOFU... what a match-up that one is! Keep your eyes here for the conclusion of that one!

This week in the Platoon Report, jig brings an update on the status of his fantasy clan event, and I take a look at our clan members who joined or moved clans during the month of April.

Enjoy - and good luck out there on the battlefield!

[player]Swifte[/player]
Clan Executive

Jigs Fantasy Draft Update by jigger1986

show

April 2014 Shakers and Movers by Swifte

show

Use our convenient navigation buttons to get to your favourite section.

Posted: **Sat May 17, 2014 10:45 pm**

Hello 117, it's nice to be home. Speaking of being home, if you don't know by now, one of CC's greatest map makers, and probably the creator of some of your favorite maps, has returned. The ever evolving Dim is back, and in a big way. Not only does he already have Slovakia in BETA, he has dusted off three other amazingly complex and beautiful maps, Knights & Warlocks, Clash of Kings, and Pirates and Merchants.

So take a look below, and head over to the foundry to let the map makers know your thoughts.

And now on with the show.

[player]Seamus76[/player] -- Foundry Executive

Maps in Development by Seamus76

show

Antique Text Technique by Seamus76

show

And as Alex Berger said, "Maps are the foundation on which adventure is built." Enjoy.

Submitting Your Own Articles

If you have a story for the Foundry section you think the ConquerClub Community would find interesting, you can send your article thoughts to [player]Seamus76[/player] and you may just see your article published in the Newsletter!

Use our convenient navigation buttons to get to your favourite section.

Posted: **Sun May 18, 2014 6:58 am**

Interrogation of Hayesez

show

For issue 118, we are preparing the Interrogation of [player]TeeGee[/player]. TeeGee is one of our loyal Australian members, holding up that inconvenient end of the clock when the Americans are sound asleep and the Asians haven't woken up yet. Chatter, Discussions Mod, Global Mod, former member of AFOS and recent KORT recruit, member of half a dozen other groups, TeeGee is now Chief Global. That means he's the one who brings the ban hammer down on your head when you misbehave. He's making a list, he's checking it twice, and he damn well knows if you're naughty or nice, so you'd best ask him some entertaining questions in THIS THREAD

Use our convenient navigation buttons to get to your favourite section.

Posted: **Sun May 18, 2014 8:16 am**

We have 3 articles in the Mess Hall. First, we have a roundup of the Live Chat Event that was part of the Easter celebrations on Conquer Club. Is it a bit late? Yes, it is. I must admit the fault is entirely mine. [player]Razorvich[/player] sent me the article in plenty of time for Issue 116, but with all the things I am involved in, I misplaced it. I'm sure the delay will not be fatal: there are some very funny lines in there and I think you'd enjoy them regardless of what month you read them in! So, thank you Razorvich, and my apologies for the publication delay.
Here's what I said in live chat terms

Second, we have a "first" article. [player]Jak111[/player] has just joined the Dispatch staff and will be anchoring the Mafia desk for the next little while. Please give a very warm welcome to jak111 on his first appearance in this publication!
=D>

Finally, we have the penultimate round in Caff's Dispatch Trivia Contest. Kudos to [player]iAmCaffeine[/player] for his work on this project, one of the toughest and most ambitious trivia contests ever deployed on this site. Caff has just won his 3-month GC medal for this contest, and YOU can still win a GA! Yes, that's right. There is still this round and the next to be fought over, and all five top players get GA medals. First place is now out of reach for anyone except [player]Ffraid[/player] or [player]Dakky21[/player], but 3rd, 4th, and 5th are still very much in doubt, so get your entries in there!

Live Chat Event by Razorvich