SQLite Help

[eagle4life69]

SO I want to create a tracker for my company to track a jobs location... HOW?

It might be a Database thing I do not know how... Point me in the right direction.

So if a job is in location A then moves to Location B how can I know it was also at Location A (mainly a history)

How would my Table look?

jobnumber | location | locationdate

but if I do this do I have it create a new field every time it updates?

my current code is like this

expandcollapse popup

#include <GUIConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
HotKeySet("{Esc}","Quit")
Dim $1Query,$bRow,$About_GUI,$msg,$job
$GUI = GUICreate("Check Job In",300,100,0,0)
            GUICtrlCreateLabel("Job Number",15,1,90,20)
            GUICtrlSetFont(-1, 11, 600)
        $gui_check = GUICtrlCreateLabel("",20,85,290,20)
        $gui_job = GUICtrlCreateInput ("",5,18,110, 20,0x2000)
            GUICtrlSetFont(-1, 11, 600)
        GUICtrlCreateLabel("Press ENTER to Check In",5,40,110)
        GUICtrlSetFont(-1, 7, 300)

GUISetState (@SW_SHOW,$GUI)

While $msg <> $GUI_EVENT_CLOSE
$msg = GUIGetMsg()
    Select
        Case $msg=$gui_job
            $ERROR=0
        
    $job=GUICtrlRead($gui_job)
    $now = @MON & "-" & @MDAY & "-" & @YEAR  & " " & @HOUR & ":" & @MIN & ":" & @SEC
    _SQLite_Startup ()
    _SQLite_Open ("Q:\Shipping\db\Print_Services.sqlite") ; open :memory: Database
    _SQlite_Query (-1, "SELECT jobnumber FROM jobs WHERE jobnumber='" & $job & "';", $1Query) ; the query 
            While _SQLite_FetchData ($1Query, $bRow) = $SQLITE_OK
                _SQLite_Exec (-1, "UPDATE jobs SET location='" & @ComputerName & "' WHERE jobnumber='" & $job & "';") ; INSERT Data
                _SQLite_Exec (-1, "UPDATE jobs SET locationdate='" & $now & "' WHERE jobnumber='" & $job & "';") ; INSERT Data
                $ERROR=1
            WEnd
            _SQLite_QueryFinalize ($1Query) 
            If $ERROR = 0 Then
                _SQLite_Exec(-1,"INSERT INTO jobs (jobnumber, qty, mstop, description, price, deptid, duedate, accountingstring, entereddate, location, locationdate, salesperson) VALUES ('" & $job & "', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', '" & @ComputerName & "', '" & $now & "', ' ');")
            EndIf
            
    _SQLite_Close()
    _SQLite_Shutdown()
    GUICtrlSetData($gui_job,"")
    GUICtrlSetData($gui_check,"Job " & $job & " has been checked in to " & @ComputerName)

    EndSelect
WEnd
Func Quit()
        Exit
    EndFunc

Right now I am only showing the current location

and then how can I display the history?

HELP!!!!! I feel overwhelmed and need a good start this is my first huge program my others are basic but a tracking program feels huge.

[zorphnog]

Well your post is a bit confusing. Are you just trying to update an existing database, or are you creating an entirely new database? Personally I would just make a job_history table that tracked all of the movements. Something like:

CREATE TABLE job_history (jobnumber int, location text, locationdate datetime);

Then anytime the job moves you insert a new history row. To query the history you just query the jobnumber in the job_history table and sort by date.

[eagle4life69]

So yes I was thinking of doing a new DB just for the history. What is the basics for the Query the DB?

This is how I Query the DB

_SQLite_Startup ()
            _SQLite_Open ("Q:\Shipping\db\Print_Services.sqlite") ; open :memory: Database
            _SQlite_Query (-1, "SELECT * FROM jobs WHERE jobnumber='" & GUICtrlRead($job1) & "';", $1Query) ; the query 
            While _SQLite_FetchData ($1Query, $bRow) = $SQLITE_OK
                GUICtrlSetData($qty1,$bRow[10])
            WEnd
            _SQLite_QueryFinalize ($1Query)
            _SQLite_Close()
            _SQLite_Shutdown()

so if I have a DB like this

jobnumber | location | locationdate

------------------------------------

56785 | PC1 | 05-11-2010 02:03:11

56785 | PC2 | 05-09-2010 11:36:59

how do you display the history?

Would it be like I have done to show our Mail Stops

_SQLite_Startup ()
            _SQLite_Open ("Q:\Shipping\db\Print_Services.sqlite") ; open :memory: Database
            $sSearch = InputBox("Search", "Type the street address you're looking for?")
            $iRval = _SQLite_GetTable2d (-1, "SELECT * FROM Mail_Stops WHERE address LIKE '%" & $sSearch & "%';", $aResult, $iRows, $iColumns)
            If $iRval = $SQLITE_OK Then
                _ArrayDisplay($aResult,"All Address' that contain '" & $sSearch & "'")
            EndIf
            _SQLite_Close()
            _SQLite_Shutdown()

Hope this makes since

Edited May 11, 2010 by eagle4life69

[jchd]

You need to sit down and explain calmly the structure of the problem if you want to the point advice. Anything else is just moving air.

What's the schema, and such...

[eagle4life69]

You need to sit down and explain calmly the structure of the problem if you want to the point advice. Anything else is just moving air.

What's the schema, and such...

I basically want to track work orders throughout my job. We physically print out work orders and need to know where they are at any given point.

my goal in the end is to have a gui display a list of the jobs and current location. The jobs that have been sitting for (lets say) 2 weeks would be displayed with a red bar, then 1 week of non moving would be in yellow and the rest would be in green. When you click on the job you can see the history of the job where it has been and the times.

Sorry my mind is moving in about 100 ways and the thinking process goes crazy.

Does this make more since?

[jchd]

That's a little project of its own, probably a bit too much for a help forum.

Nonetheless, here's is my understanding of your context.

You deal with work orders. They have an ID, a status: accepted, sent out to third-party, received back from third-party, ready for delivery, paid, delivered, ... (I don't know what exactly) and can be in the hand of several third-parties in succession. Each new status also has a timestamp.

You deal with third-parties to which you entrust with partial tasks regarding your orders. Each third-party has an ID, a name, other coordinates (address, phone, mail, ...).

May be you have customers as well? Let's ignore this for now.

Anyway, do you see you have at least two tables with more or less clear design?

Your jobs table needs:

o) ID make it a primary key

o) timestamp make it _NowCalc or _NowCalcDate so it sorts (decreasingly) naturally yet is human friendly

o) third-party reference so you know where it is

o) status make it a numeric enumeration or acronym or whatever fits your bill

Make a new entry in this table for every status change or third-party change in the life of a job. It will be easy to grab the last informations for a given job, or display jobs getting late , or jobs finished 3 months ago, or whatever you need to query.

Your third-party table should be easy to setup. Information there could be for reference or participate in some automation today or later.

Does this clear some mud?

[eagle4life69]

Good Points

For now I am using this

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>
Local $hQuery, $aRow, $sMsg, $1Query, $2Query, $bRow, $aResult, $iRows, $iColumns, $iRval

Dim $jobArray,$qtyArray,$pkgArray,$d_btncan,$d_btn,$bypass,$d_ss1,$d_wc1,$d_cm1,$d_shipping,$d_name,$aRecords,$jobs,$About_btn,$About_GUI,$msg



_SQLite_Startup ()
            _SQLite_Open ("Q:\Shipping\db\Print_Services.sqlite") ; open :memory: Database
            $sSearch = InputBox("Search", "Type the Department you're looking for?")
            $iRval = _SQLite_GetTable2d (-1, "SELECT * FROM tracking WHERE jobnumber LIKE '%" & $sSearch & "%';", $aResult, $iRows, $iColumns)
            If $iRval = $SQLITE_OK Then
                _ArrayDisplay($aResult,"All Department Names that contain '" & $sSearch & "'")
            EndIf
            _SQLite_Close()
            _SQLite_Shutdown()

this will display what I want now to put it in a gui with bars that are colored... Now thats the hard part

[jchd]

So that's only a GUI question.

Try GuiCtrl*ListView* functions. I'm not sure you can paint them individually, but you can have an ad hoc icon displayed in a column to denote status.

Else the _GUICtrlListView* functions allow by far more control but generally need more coding.