Interesting array question

[OldCodger]

Newbie here.. absolute virgin as far as AutoIt is concened although I do have a little programming background.

I have inherited AutoIt support for my company and have been tasked with providing our users with an application to help them formulate report templates for feeding into Teradata.

I could spend weeks learning how to do this myself or take the cowards way out and ask the experts.. the latter is chosen.

OK.

I have this file, xxxx.csv, which looks like this..

DIRECTOR_CODE,MERCH_MANAGER_CODE,BUYER_CODE
W  ,31,0P8
W  ,31,0P9
W  ,31,0PA
W  ,31,0PR
W  ,31,0SR
W  ,31,0ST
E  ,67,0KN
E  ,67,0KP
E  ,67,0KQ
E  ,67,0KR
E  ,68,0LD
E  ,68,0LG
E  ,68,0LH
E  ,68,0LJ
E  ,68,0LK
P  ,71,0NR
P  ,71,0NS
P  ,71,0NT
P  ,71,0NU
P  ,71,0PD
P  ,72,0QC
P  ,72,0WC
P  ,72,0WD
P  ,73,0RE
P  ,73,0WF
P  ,73,0WG
P  ,73,0WJ.. etc.. etc..

I load it into an array which displays thus..

[0]|DIRECTOR_CODE|MERCH_MANAGER_CODE|BUYER_CODE
[1]|W  |31|0P8
[2]|W  |31|0P9
[3]|W  |31|0PA
[4]|W  |31|0PR
[5]|W  |31|0SR
[6]|W  |31|0ST
[7]|E  |67|0KN
[8]|E  |67|0KP
[9]|E  |67|0KQ
[10]|E  |67|0KR
[11]|E  |68|0LD
[12]|E  |68|0LG
[13]|E  |68|0LH
[14]|E  |68|0LJ
[15]|E  |68|0LK
[16]|P  |71|0NR
[17]|P  |71|0NS
[18]|P  |71|0NT
[19]|P  |71|0NU
[20]|P  |71|0PD
[21]|P  |72|0QC
[22]|P  |72|0WC
[23]|P  |72|0WD
[24]|P  |73|0RE
[25]|P  |73|0WF
[26]|P  |73|0WG
[27]|P  |73|0WJ

What I require is a GUI process which allows selectable listview(s) for users to select rows by these criteria.

By DIRECTOR_CODE (Eg. list all rows associated with director 'E')

By MERCH_MANAGER_CODE (Eg. list all rows associated with manager '71')

By BUYER_CODE (Eg. list the row(unique) associated with buyer '0RE')

(Directors can have mulple manager associations, Managers can have multiple buyer associations, Buyers are unique.)

The selections are saved to build up an output file(.csv)

I've been struggling to think of the best way to present the options.. Lists?.. Combos? and to formulate the listview(s).

I've heard of associative arrays but don't really understand them or if they are the answer.

Anyhow, the input file is attached and any help, suggestions or code would be vey welcome.

Thanks in advance.

Glenn.

Dir_man_Buyer.txt

[FireFox]

Hi,

Welcome to the autoit forum

A first thought would be to use combos, and trigger changes to fill the others depending on the data of the previous ones.

Edit: I see some MVPs looking at the topic, I will let them answer.

Br, FireFox.

Edited August 14, 2013 by FireFox

[JLogan3o13]

Edit: I see some MVPs looking at the topic, I will let them answer.

Br, FireFox.

 

Eh, you're an MVP too bud

[FireFox]

I thought some of you would give an answer more consistent than mine, I will be busy so I can't really answer.

Edited August 14, 2013 by FireFox

[kylomas]

Glen,

Can a manager be associated with more than one director or a buyer be associated with more than one manager?

kylomas

edit:  I answered the question myself with the following code

#include<array.au3>

local $str = fileread(@scriptdir & '\dir_man_buyer.txt')

local $aBase = stringsplit($str,@crlf,3)

local $aSales[ubound($aBase)][3]

for $1 = 0 to ubound($aBase) - 1

    $aTmp = stringsplit($aBase[$1],',',3)
    $aSales[$1][0] = $aTmp[0]
    $aSales[$1][1] = $aTmp[1]
    $aSales[$1][2] = $aTmp[2]

Next

_arraysort($aSales,0,0,0,0)
_arraydisplay($aSales,'Sort on Director')

_arraysort($aSales,0,0,0,1)
_arraydisplay($aSales,'Sort on Manager')

_arraysort($aSales,0,0,0,2)
_arraydisplay($aSales,'Sort on Sales')

So now that we know that we have all 1>many relationships, do you want to use a DB?

This is just as easily done with the existing 2D array in the code above.

Edited August 14, 2013 by kylomas

[OldCodger]

kylomas..

Thanks for the prompt reply..

I tried your snippet but am getting this error?

C:Documents and Settingssts043My DocumentsAU FilesDevtewib2.au3 (16) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
$aSales[$1][1] = $aTmp[1]
$aSales[$1][1] = ^ ERROR

Regards..

[kylomas]

Try getting rid of the blank line at the end of your file.  I did'nt code much error checking into this demo.

Or run  this (blank line check added)

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
; *** End added by AutoIt3Wrapper ***

#include<array.au3>

local $str = fileread(@scriptdir & '\dir_man_buyer.txt')

local $aBase = stringsplit($str,@crlf,3)

local $aSales[ubound($aBase)][3]

for $1 = 0 to ubound($aBase) - 1

    $aTmp = stringsplit($aBase[$1],',',3)
    if ubound($aTmp) < 3 then exitloop
    $aSales[$1][0] = $aTmp[0]
    $aSales[$1][1] = $aTmp[1]
    $aSales[$1][2] = $aTmp[2]

Next

_arraysort($aSales,0,0,0,0)
_arraydisplay($aSales,'Sort on Director')

_arraysort($aSales,0,0,0,1)
_arraydisplay($aSales,'Sort on Manager')

_arraysort($aSales,0,0,0,2)
_arraydisplay($aSales,'Sort on Sales')

Edited August 14, 2013 by kylomas

[OldCodger]

kylomas..

Thanks for this.

I see how I can sort by each category but how do I visualise this for the user?

[kylomas]

Here is a rudimentary example.  I only coded the function to list Director, you should be able to complete the rest.

expandcollapse popup

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
; *** End added by AutoIt3Wrapper ***
#include <GuiListView.au3>

#include<array.au3>

local $str = fileread(@scriptdir & '\dir_man_buyer.txt')

local $aBase = stringsplit($str,@crlf,3)

local $aSales[ubound($aBase)][3]

for $1 = 0 to ubound($aBase) - 1

    $aTmp = stringsplit($aBase[$1],',',3)
    if ubound($aTmp) < 3 then exitloop
    $aSales[$1][0] = $aTmp[0]
    $aSales[$1][1] = $aTmp[1]
    $aSales[$1][2] = $aTmp[2]

Next

;~ _arraysort($aSales,0,0,0,0)
;~ _arraydisplay($aSales,'Sort on Director')

;~ _arraysort($aSales,0,0,0,1)
;~ _arraydisplay($aSales,'Sort on Manager')

;~ _arraysort($aSales,0,0,0,2)
;~ _arraydisplay($aSales,'Sort on Sales')


#AutoIt3Wrapper_Add_Constants=n



local $gui010   =   guicreate('Sales Org',500,300)
                    guictrlcreatelabel('Specify Director Code, Manager Code or Merchant Code',20,20,400,20)
                    guictrlcreatelabel('Director Code',20,50,90,20)
                    guictrlcreatelabel('Manager Code',170,50,90,20)
                    guictrlcreatelabel('Merchant Code',320,50,90,20)
local $dirIn    =   guictrlcreateinput('',120,50,40,20)
local $manIn    =   guictrlcreateinput('',250,50,40,20)
local $merIn    =   guictrlcreateinput('',420,50,40,20)
local $lv010    =   guictrlcreatelistview('Director | Manager | Merchant',20,90,460,200)

                    guisetstate()


while 1
    switch guigetmsg()
        case $gui_event_close
            Exit
        case $dirIn
            _pop_dir()
;~      case $manIn
;~          _pop_man()
;~      case $merIn
;~          _pop_mer()

    EndSwitch
WEnd

func _pop_dir()

    ConsoleWrite('at popdir' & @LF)

    _GUICtrlListView_DeleteAllItems($lv010)

    for $1 = 0 to ubound($aSales) - 1
        if stringstripws($aSales[$1][0],3) = stringstripws(guictrlread($dirin),3) then
            guictrlcreatelistviewitem($aSales[$1][0] & '|' & $aSales[$1][1] & '|' & $aSales[$1][2],$lv010)
        endif
    Next

endfunc

kylomas 

edit: sightly spiffed up gui to give you an example of some of the things that can be done

expandcollapse popup

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
; *** End added by AutoIt3Wrapper ***
#include <GuiListView.au3>

#include<array.au3>

local $str = fileread(@scriptdir & '\dir_man_buyer.txt')

local $aBase = stringsplit($str,@crlf,3)

local $aSales[ubound($aBase)][3]

for $1 = 0 to ubound($aBase) - 1

    $aTmp = stringsplit($aBase[$1],',',3)
    if ubound($aTmp) < 3 then exitloop
    $aSales[$1][0] = $aTmp[0]
    $aSales[$1][1] = $aTmp[1]
    $aSales[$1][2] = $aTmp[2]

Next

;~ _arraysort($aSales,0,0,0,0)
;~ _arraydisplay($aSales,'Sort on Director')

;~ _arraysort($aSales,0,0,0,1)
;~ _arraydisplay($aSales,'Sort on Manager')

;~ _arraysort($aSales,0,0,0,2)
;~ _arraydisplay($aSales,'Sort on Sales')


#AutoIt3Wrapper_Add_Constants=n



local $gui010   =   guicreate('Sales Org',500,400)
                    guictrlcreategroup('Specify Director Code, Manager Code or Merchant Code',20,20,460,100)
                    guictrlsetfont(-1,10,800)
                    GUICtrlSetColor(-1,0xaa0000)
                    guictrlcreatelabel('Director Code',30,50,90,20)
                    guictrlcreatelabel('Manager Code',170,50,90,20)
                    guictrlcreatelabel('Merchant Code',300,50,90,20)
                    guictrlcreatelabel('"*" in Director Code = List all entries',30,85,300,15)
                    guictrlsetfont(-1,9,800,-1,'courier new')
                    guictrlsetcolor(-1,0x0000ff)
                    guictrlcreategroup('',-99,-99,1,1)
local $dirIn    =   guictrlcreateinput('',120,50,40,20)
local $manIn    =   guictrlcreateinput('',250,50,40,20)
local $merIn    =   guictrlcreateinput('',390,50,40,20)
local $lv010    =   guictrlcreatelistview('Director | Manager | Merchant',20,150,460,200)
local $LVsize   =   controlgetpos($gui010,'',$lv010)
                    for $1 = 0 to _GUICtrlListView_GetColumnCount($lv010) + 1
                        _GUICtrlListView_SetColumnWidth($lv010, $1, ($LVSize[2])/_GUICtrlListView_GetColumnCount($lv010))
                    next

                    guisetstate()


while 1
    switch guigetmsg()
        case $gui_event_close
            Exit
        case $dirIn
            _pop_dir()
        case $manIn
            _pop_man()
;~      case $merIn
;~          _pop_mer()

    EndSwitch
WEnd

func _pop_dir()

    _GUICtrlListView_DeleteAllItems($lv010)

    for $1 = 0 to ubound($aSales) - 1
        if stringstripws(guictrlread($dirin),3) = '*' then
            guictrlcreatelistviewitem($aSales[$1][0] & '|' & $aSales[$1][1] & '|' & $aSales[$1][2],$lv010)
        else
            if stringstripws($aSales[$1][0],3) = stringstripws(guictrlread($dirin),3) then
                guictrlcreatelistviewitem($aSales[$1][0] & '|' & $aSales[$1][1] & '|' & $aSales[$1][2],$lv010)
            endif
        endif
    Next

endfunc

func _pop_man()

    _GUICtrlListView_DeleteAllItems($lv010)

    for $1 = 0 to ubound($aSales) - 1
        if stringstripws($aSales[$1][1],3) = stringstripws(guictrlread($manin),3) then
            guictrlcreatelistviewitem($aSales[$1][0] & '|' & $aSales[$1][1] & '|' & $aSales[$1][2],$lv010)
        endif
    Next

endfunc

Edited August 14, 2013 by kylomas

[JLogan3o13]

klyomas' version is definitely cleaner, but you mentioned a GUI process. If you're going that route, you could do something like this: 

Highlight the DIRECTOR_CODE and click the button

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <GUIListView.au3>
#include <WindowsConstants.au3>

_Main()

Func _Main()

    $oExcel = _ExcelBookOpen(@DesktopDir & "\forum.csv")
    Local $aArray = _ExcelReadSheetToArray($oExcel, 2, 1, 0, 0)
    _ExcelBookClose($oExcel)

    Local $msg
    $mainGUI = GUICreate("Test", 390, 450)
    $listview1 = GUICtrlCreateListView(".", 10, 10, 110, 350)
    $listview2 = GUICtrlCreateListView(".", 130, 10, 140, 350)
    $listview3 = GUICtrlCreateListView(".", 280, 10, 100, 350)
    _GUICtrlListView_SetColumn($listview1, 0, "DIRECTOR_CODE", 106, 0)
    _GUICtrlListView_SetColumn($listview2, 0, "MERCH_MANAGER_CODE", 136, 0)
    _GUICtrlListView_SetColumn($listview3, 0, "BUYER_CODE", 96, 0)

    $dirCode = GUICtrlCreateButton("Sort by DIRECTOR_CODE", 10, 380, 140, 30)
    $buyCode = GUICtrlCreateButton("Sort by BUYER_CODE", 160, 380, 140, 30)

    For $i = 1 To UBound($aArray) - 1
        GUICtrlCreateListViewItem($aArray[$i][1], $listview1)
        GUICtrlCreateListViewItem($aArray[$i][2], $listview2)
        GUICtrlCreateListViewItem($aArray[$i][3], $listview3)
    Next

    GUISetState(@SW_SHOW)

    While 1
        $msg = GUIGetMsg()
            Select
                Case $msg = $GUI_EVENT_CLOSE
                    ExitLoop
                Case $msg = $dirCode
                    $var = _GUICtrlListView_GetItemTextString($listview1, -1)
                    If $var = "" Then
                        MsgBox(0, "", "Please select the DIRECTOR_CODE to sort by")
                    Else
                        _Child($mainGUI, $aArray, $var)
                    EndIf
            EndSelect
    WEnd

GUIDelete()

EndFunc

Func _Child($mainGUI, $aArray, $var)

    GUISetState(@SW_HIDE, $mainGUI)

    Local $childMsg

    GUICreate("Child Test", 400, 450)
        $childlistview1 = GUICtrlCreateListView(".", 10, 10, 110, 350)
        $childlistview2 = GUICtrlCreateListView(".", 130, 10, 140, 350)
        $childlistview3 = GUICtrlCreateListView(".", 280, 10, 100, 350)
        _GUICtrlListView_SetColumn($childlistview1, 0, "DIRECTOR_CODE", 106, 0)
        _GUICtrlListView_SetColumn($childlistview2, 0, "MERCH_MANAGER_CODE", 136, 0)
        _GUICtrlListView_SetColumn($childlistview3, 0, "BUYER_CODE", 96, 0)

    For $i = 1 To UBound($aArray) - 1
        If $aArray[$i][1] = $var Then
            GUICtrlCreateListViewItem($aArray[$i][1], $childlistview1)
            GUICtrlCreateListViewItem($aArray[$i][2], $childlistview2)
            GUICtrlCreateListViewItem($aArray[$i][3], $childlistview3)
        EndIf
    Next

    GUISetState(@SW_SHOW)

    While 1
        $childMsg = GUIGetMsg()
            Select
                Case $childMsg = $GUI_EVENT_CLOSE
                    ExitLoop
            EndSelect
    WEnd

GUIDelete()
GUISetState(@SW_SHOW, $mainGUI)

EndFunc

[OldCodger]

Thanks to both of you.

It gives me a start.

JL.. I intermittantly get the following error Excel error when I run your code.

"An unhandled win32 exception occured in EXCEL.EXE[4264]" Mea culpa.. I had an orphan Excel process which I've now removed.

Anyway I'll have a play at enhancing the code..

But if there ARE any more suggestions they will be very welcome.

Edited August 15, 2013 by OldCodger

[OldCodger]

Wow.. someone pointed me to this thread

It looks like it could work for what I'm hoping to achieve.

Could I use the selection code from your offerings to populate the left view so that the user can then drag the items to build the output?

Has anyone done anything similar to this GUI?

[kylomas]

 Could I use the selection code from your offerings to populate the left view so that the user can then drag the items to build the output?

 

That can certainly be built in, as demonstrated in the thread you linked to.  However, your original problem was presenting the Dir|Man|Client selection to the user.  Has this changed?

Did you run the second example that I posted above?

kylomas

[OldCodger]

That can certainly be built in, as demonstrated in the thread you linked to.  However, your original problem was presenting the Dir|Man|Client selection to the user.  Has this changed?

Did you run the second example that I posted above?

kylomas

I did indeed ky.. and I like it.

...I'm trying to integrate your code with the add/remove example but it's giving me brain ache.. I'm ok with logic but I'm so new to this GUI stuff, being a mainframe analyst and all.

I'll conquer it eventually.. it's just that I need to provide the users with something soon.

The presentation process remains the same.

• Let the user select any number of records by Director, Manager or Buyer
• Show all the records associated with the selection(s) in the source listview
• Select individually required records and populate the target listview
• Use the contents of the target listview to create a segment of report parameters, probably as a preview listview.
• Repeat the process until entire report template requirement is complete.
• Write the preview content to .csv ouput

 

That's the scenario if it makes sense.

See attached for example.

Cheers..

example.doc

Edited August 16, 2013 by OldCodger

[kylomas]

OldGodger,

being a mainframe analyst and all.

 

I empathize with you (20+years as mainframe OS support, never was a programmer).  There are several of us old dinosaurs here.

Indeed, The gui stuff is still a challenge to me.  If no one else jumps in I'll see what I can do tomorrow (I vagely recall having seen this done before).

kylomas

edit:  What is the difference between a "source" item and a "segment" item?

Edited August 16, 2013 by kylomas

[OldCodger]

OldGodger,

 

I empathize with you (20+years as mainframe OS support, never was a programmer).  There are several of us old dinosaurs here.

Indeed, The gui stuff is still a challenge to me.  If no one else jumps in I'll see what I can do tomorrow (I vagely recall having seen this done before).

kylomas

edit:  What is the difference between a "source" item and a "segment" item?

 

Ok.. A user would 'search' using your code which would populate the 'source' view.

The user would then select the individual records that he/she needs and populate the 'segment' view to produce one set of parameters. (I forgot to mention that they would need an input for the segmentation to add an extra field to the segment record as a description of the segment.. <Director | Manager | Buyer | Description>)

The process would be repeated until all required segments were created.

At any time, the user should be able to preview the current 'build'.

So basically.. we are building segments of a template which, when input to Teradata, will drive some valuable merchandising reports.(We are a large home shopping company)

btw. a 'Buyer' in our terms is not the client it is an employee who purchases our merchandise. not that it matters in this forum but just to clarify for you.

[FireFox]

I can make it for you if you want. Avoiding you headaches

Edited August 16, 2013 by FireFox

[OldCodger]

FF.. I'd gladly accept any help

This is my first complicated AutoIT task. I've just done maintenance on a couple of applications, so I am raw.

Thanks.

 

Edit: I have attached an overview to help you get your head around it.

Overview.doc

Edited August 16, 2013 by OldCodger

[FireFox]

Totally forgot your topic, sorry.

Can you tell me when the listviews are filled?

Especially between the segments and the preview.

Because for me the segments listview is already a preview, or it's emptied on every new filter as the source listview, but if it's the case then the contents of the segments' listview are moved to the preview listview.

Br, FireFox.

[OldCodger]

Totally forgot your topic, sorry.Can you tell me when the listviews are filled?Especially between the segments and the preview.Because for me the segments listview is already a preview, or it's emptied on every new filter as the source listview, but if it's the case then the contents of the segments' listview are moved to the preview listview.Br, FireFox.

they need to move records from source to segment and move them out again if they make an error..when they are hapy with that particular segment they need to annotate the segment so we need input.. and a commit button which would move the segment to preview and empty the segment listview.. then they would start a new segment.

When they have finished they will submit the preview to csv.

Many thanks

Glenn