BlackFlag

General suggestions on where to start

6 posts in this topic

I am not looking for any code here, just want to see if the method I am thinking about using makes sense.

The situation:

I have 800+ plus customers that have a list of navigation charts they have purchased from us for use with our software.  In each customer's account, there is a text file that lists the charts they own.  I need to take this information and create a list of charts with the number of vessels that own that chart.

For example, Vessel A has chart number 12345.  Vessel B has chart 54321.  Vessel C has chart 12345, 54321,11111.  At then end of processing I want to generate a report like this:

Chart Number  Number of owners

11111    1

12345   2

54321   2

My plan

Create a 2d array consisting of all possible chart numbers (ChartNo, number of owners)

Create an array of the chartlist of a vessel

Compare the chartlist array with the all possible charts array, where there are matchs, incurment all possible (number of owners) for that chart number by one.

Does this make sense or should I be looking a different method. 

 

Share this post


Link to post
Share on other sites



BlackFlag,

 

800+ plus

That number of elements cries out "SQLite database" to me - and of course there is a very nice SQLite library in AutoIt, plus a number of very helpful SQLite gurus to help when needed. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Its not terrible without sqllite,  its all about that 2d array.   Since Who owns the chart doesnt matter, only the number and how many times that number occurs, if you can get them in their own column you would have an abundance of ways of accomplishing the task.  I think this would be one:

#include<Array.au3>

Global $aArr[9][2]
$aArr[0][0] = "Vessel A"
$aArr[0][1] = "12345"
$aArr[1][0] = "Vessel B"
$aArr[1][1] = "54321"
$aArr[2][0] = "Vessel C"
$aArr[2][1] = "12345"
$aArr[3][0] = "Vessel C"
$aArr[3][1] = "54321"
$aArr[4][0] = "Vessel C"
$aArr[4][1] = "11111"
$aArr[5][0] = "Vessel D"
$aArr[5][1] = "12345"
$aArr[6][0] = "Vessel D"
$aArr[6][1] = "54321"
$aArr[7][0] = "Vessel D"
$aArr[7][1] = "22222"
$aArr[8][0] = "Vessel D"
$aArr[8][1] = "12345"


_ArrayDisplay(_CountItemByCol($aArr , 0))
_ArrayDisplay(_CountItemByCol($aArr , 1))

Func _CountItemByCol($aArray , $Column)

Local $aOut[0][2]

    For $i = ubound($aArray) - 1 to 0 step - 1

        $aMatch = _ArrayFindAll($aArray , $aArray[$i][$Column] , 0 , 0 , 0 , 0 , $Column)
        $sOut = $aArray[$i][$Column] & "|" & ubound($aMatch)
        _ArrayAdd($aOut , $sOut)
        _ArrayDelete($aArray , _ArrayToString($aMatch , ";"))
        $i-= ubound($aMatch) - 1

    Next

Return $aOut

EndFunc
Edited by boththose

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

BlackFlag,

Can you give an example of exactly what the input(s) are?

kylomas

edit: Also, I'm assuming that an account can have more than one vessel  Not applicable to this task

Edit2:

Rough example using SQLite.  Input file is generated by the script in whatever directory you run the script from.

#include <array.au3>
#include <sqlite.au3>

local $st = timerinit()
_generate_test_file()
ConsoleWrite('! Time to generate test file = ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF)

Local $DLL = _SQLite_Startup()
If @error Then Exit MsgBox(16, 'ERROR', 'Error on SQLite Startup')

Local $hDB = _SQLite_Open()
If Not $hDB Then _fini()
OnAutoItExitRegister('_fini')
ConsoleWrite("! SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF & '! Using DLL = ' & $DLL & @CRLF)

; create chart table
If _SQLite_Exec($hDB, 'create table if not exists Charts  (Chart) ;') <> $SQLITE_OK Then Exit MsgBox(16, '', _SQLite_ErrMsg)

; load chart table
Local $aCharts = StringSplit(FileRead(@ScriptDir & '\charts.txt'), @CRLF, 3)

$st = timerinit()
_sqlite_exec($hDB, 'begin immediate transaction;')

For $1 = 0 To UBound($aCharts) - 1

    If StringLen(StringStripWS($aCharts[$1], 3)) = 0 Then ContinueLoop                  ;   skip - blank line
    If StringIsAlpha(StringLeft(StringStripWS($aCharts[$1], 3), 1)) Then ContinueLoop   ;   skip - 1st position not numeric

    _SQLite_Exec($hDB, 'insert into Charts values(' & _X(StringStripWS($aCharts[$1], 3)) & ');')

Next

_sqlite_exec($hDB, 'commit;')
ConsoleWrite('! ' & _SQLite_TotalChanges($hDB) & ' entries added to DB in ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF)

Local $arows, $irows, $icols

; list all charts usage
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart ;', $arows, $irows, $icols)
_arraydisplay($arows,'Usage Of All Charts')

; list only charts used 2 or more times
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart having count(chart) > 1 ;', $arows, $irows, $icols)
_ArrayDisplay($arows,'Chart Usage Count > 1')

Func _X($str)
    Return _SQLite_FastEscape($str)
EndFunc   ;==>_X

Func _fini()
    _SQLite_Close($hDB)
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

func _generate_test_file()

    local $hfl = fileopen(@scriptdir & '\charts.txt',2), $str
    if $hfl = -1 then exit msgbox(16,'','File open error')

    for $1 = 65 to 90
        $str &= 'Vessel_' & chr($1) & @crlf & @crlf
        for $2 = 1 to random(20,50,1)
            $str &= @tab & stringformat('%05i',random(1,800,1)) & @CRLF
        Next
        $str &= @CRLF
    Next

    filewrite($hfl, $str)
    fileclose($hfl)

endfunc
Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

BlackFlag,

Here's a little better example using SQLite for your problem...

#include <array.au3>
#include <sqlite.au3>

local $st = timerinit()
_generate_test_file()
ConsoleWrite('! Time to generate test file = ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF)

; start SQLite, open "memory" DB and set exit function
Local $DLL = _SQLite_Startup()
If @error Then Exit MsgBox(16, 'ERROR', 'Error on SQLite Startup')
Local $hDB = _SQLite_Open()
If Not $hDB Then _fini()
OnAutoItExitRegister('_fini')
ConsoleWrite("! SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF & '! Using DLL = ' & $DLL & @CRLF)

; create chart table
If _SQLite_Exec($hDB, 'create table if not exists Charts (Chart) ;') <> $SQLITE_OK Then Exit MsgBox(16, '', _SQLite_ErrMsg)

; read chart file to array
Local $aCharts = StringSplit(FileRead(@ScriptDir & '\charts.txt'), @CRLF, 3), $SQL

; load charts to DB
$st = timerinit()
_sqlite_exec($hDB, 'begin immediate transaction;')  ; not significant for in memory DB but useful if writing DB to DASD

For $1 = 0 To UBound($aCharts) - 1

    If StringLen(StringStripWS($aCharts[$1], 3)) = 0 Then ContinueLoop                  ;   skip - blank line
    If StringIsAlpha(StringLeft(StringStripWS($aCharts[$1], 3), 1)) Then ContinueLoop   ;   skip - 1st significant character not numeric
    $SQL &= 'insert into charts values (' & stringstripws($aCharts[$1],3) & ');' & @CRLF

Next

_SQLite_Exec($hDB, $SQL)

_sqlite_exec($hDB, 'commit;')                       ; not significant for in memory DB but useful if writing DB to DASD

ConsoleWrite('! ' & _SQLite_TotalChanges($hDB) & ' entries added to DB in ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF)

Local $arows, $irows, $icols

; list all charts usage
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart ;', $arows, $irows, $icols)
_arraydisplay($arows,'Usage Of All Charts')

; list only charts used 2 or more times
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart having count(chart) > 1 ;', $arows, $irows, $icols)
_ArrayDisplay($arows,'Chart Usage Count > 1')

; list highest used chart
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart order by count(chart) desc limit 1;', $arows, $irows, $icols)
_ArrayDisplay($arows,'Most Used Chart')

; list top 10 used charts
_SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart order by count(chart) desc limit 10 ;', $arows, $irows, $icols)
_ArrayDisplay($arows,'Top 10 Used Charts')

Func _fini()
    _SQLite_Close($hDB)
    _SQLite_Shutdown()
    Exit
EndFunc   ;==>_fini

func _generate_test_file()

    local $hfl = fileopen(@scriptdir & '\charts.txt',2), $str
    if $hfl = -1 then exit msgbox(16,'','File open error')

    for $1 = 65 to 90
        $str &= 'Vessel_' & chr($1) & @crlf & @crlf
        for $2 = 1 to random(20,50,1)
            $str &= @tab & stringformat('%05i',random(1,800,1)) & @CRLF
        Next
        $str &= @CRLF
    Next

    filewrite($hfl, $str)
    fileclose($hfl)

endfunc

The input file is generated in the script.  I imagine that your input will be different but similar based on your description.  Just change the DB load routine to accommodate whatever your input looks like, or, format your input as I have.

There is a way to change the SQL "insert" stmt to "chain" values but I didn't bother with it.  As is, the code will load 90,000+ DB entries in around 1.5 seconds.  If volume / performance become an issue then post back.

kylomas

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now