Jump to content
Sign in to follow this  
eagle4life69

SQLite Help

Recommended Posts

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

#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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by eagle4life69

Share this post


Link to post
Share on other sites
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...


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.