OpenALPR Tool - Save and Query CSV Exports

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Updated 2021.08.20
Both Downloader and Viewer have been updated.
Most changes relate to being able to pull from the local web-server as well as the cloud CSV to allow much faster imports and thus alerts.
Tied into this improvements made to alerting, searching, backups, etc.
Please see This Link for more detail.

Updated 2020.11.14
Search functionality expanded.
Directional arrows displayed.
Please see This link for additional details.

Updated 2020.10.18
Few minor updates.
Please see This Link for some of the new features.

Shout Out to @tech101 - please see his PDF walkthrough on how to do the install. He's also helped come up with some of the ideas in the newest updates.

Updated 2020.10.16
Both the Downloader and Viewer have been updated.
The downloader functionality is fairly minor (some settings better saved, auto-login fixed for new page, ability to kick off the download when app starts).
The viewer has some more significant updates, including much speedier queries, additional filters, ability to email some reports (single plate history, all plates for a day history), and more. This update will require running two SQL queries (included) to update two of the stored procedures. The queries should be ran first, they are backwards compatible, then use the new programs.
I'd suggest making a copy of your .config file so you can easily migrate any lost settings into the new config file (new config options are in both programs).

Thanks @tech101 for the ideas and testing this.

@DLONG2 and I have worked separately, and together, to bring what we created for our own use to everyone here. The goal of this project was to create an automated method to capture the CSV Exports from OpenALPR and save them to a local database that could then be queried.

Please keep in mind that this was done as a hobby. There may be some bugs, and the setup isn't for the faint of heart.

Suggested setup includes the following, all on a single Windows machine will make this easiest.
  • OpenALPR agent installed with the local webserver enabled and retention of local images set as high as reasonable for your system.
  • Visual Studio Community Edition - This is free, you'll just need some basic knowledge of C# or the ability to learn it quickly. Every attempt was made to extract the settings into a settings file so minimal editing should be required.
  • SQL Server Express - This is free, and is the engine that will store the data.
  • SQL Server Management Studio - This is free, a separate install from SQL Server Express. This will be used to manage the SQL Server instance to create the initial tables and stored procedures.
Once everything is installed, create a new database - suggested that it be called "LPR" - if you choose something different you'll have to edit the first line in each of the SQL Procedures I created.

The following code should be retrieved next. It is the program that performs the routine tasks.
Gymratz/LPR_Downloader

Within this code is a folder that contains all of the SQL Queries that need to be ran in order to set up the database. Run them each in order to set up the tables, and Stored Procedures that are needed both for this and the Viewing application.

You should be able to compile the program (or run in debug mode from Visual Studio). When you first run it there is a tab that requires settings to be input. The settings are all stored in the app.config file.

This applications does a few things:
  • It keeps you signed into the OpenALPR Web Page.
  • It downloads the CSV based on a timer you specify.
  • It sends an e-mail / push alert (if you set up this service) to you when plates you tag are seen.
I have mine on a 1 minute timer so that I get alerts quickly after tagged cars are seen. I've had this running for over two weeks without issue or requiring user interaction.
upload_2019-8-11_9-35-23.png


Only after you get this running, you'll want to retrieve the viewer application.
Gymratz/LPR_Viewer

All of the SQL Setup was done in LPR_Downloader setup, so this one will have minimal setup. Before you Compile/Run the program please review the app.config and set the SQL Connection String the same as you used in LPR_Downloader. The program will error on start without it (I'll work on fixing this in a future release).

With this application you'll be able to review plates going back since you started collection, and associated images based on the retention size you set within the OpenALPR Agent.

You can use the "Add / Update Known Plate" to set details about specific plates -- including an e-mail address which would trigger the alerts to that address. You can use "Fix Entry" if the plate read is wrong and correct it to the right one. When doing so, you can also choose to make this an auto-fix (all current and future plates that have the exact wrong reading will be changed to the correct one). You can also opt to "Hide All" if it is a common read of a non-plate that you want to hide from view.
There's also a stand-alone button "Duplicate" that can be used to manually hide a plate, whether because it is a true duplicate that was read multiple times before the car left camera view, or just a wrong read that needs to go away.

If you're able to get through the process of setting everything up, the settings/views should make sense to you. Definitely open to suggestions and helping people through this as needed - but please keep in mind this was just a hobby and not intended to be a complete/polished program. More time went into making it portable than went into the initial creation for our own use.
 
Last edited:

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Starting....will let you know

THANK YOU!!!!!

Hit a few snags

After installing everything, after running all the data bases, I am at the part of compiling....

When I first opened VB, it had the "design" of the app in a window, and I ran

Build--->Build LPR_Downloader

Would get an Error



So i went into properties, stopped the ClickOnce signing, still came up with error.

Now my last error when compiling is.


\LPR_Downloader-master\LPR_Downloader-master\LPR_Downloader.cs(358,25,358,35): error CS7036: There is no argument given that corresponds to the required formal parameter 'thisDevice' of 'frm_LPR_Downloader.Alert_Push(string, string, string)'



EDIT 2: So I went a step farther, and created a Key that matched the name it was looking for....still cannot get past the "Alert Push" error.
 
Last edited:

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Edit: Nyght, the code allows for push notifications to a cellular device from an account at Pushover.net. You would need to register for an account and purchase their $4.99 mobile app in order to use that service, while Gym looks over the code for that error you found, as the use of a push notification within the LPR app should just be an option.
 
Last edited:

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Nyght, the code allows for push notifications to a cellular device from the free tier at Pushover.net. You would need to register for a free account in order to use that service, while Gym looks over the code for that error you found.
I have tried commenting out the PUSH code, and was unsuccessful....as for the previous error, I just created a certificate with that name, and it skipped that part....

I have edited the config file DIRECTLY to insert, ALPR username, EMAIL SMTP etc...just missing push over, if I have to sign up for an account to avoid this I will...however,

I put "NA" and "NA" for username and token, which should accept it, and continue on with out the error, but it seems the error is pointing to the string "thisDevice" which is where the error is occuring.


So I went and removed the string thisDevice, and the argument add thisDevice and was able to compile.


So after removing string thisDevice and add argument thisDevice and compiling, all my settings I put into the appconfig file directly, showed up properly in the app, including the NA and NA for the push over....so possibly thisDevice was either mistyped or some other reference is not working, however since I am not using that (for now, as I am just testing the DB setup and download setup) I will leave it with the edits.


LPR_Downloader Successfully downloads a file according to the "log"....however, (not sure how its supposed to work) there are NO files in the "Archived" folder I setup. So possibly downloading to a temp location first assuming.

Main screen is "blank" does not show databases, so probably am not connecting to my database at this time....

 
Last edited:

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
I thought I had the code set so that it would only try the push if there was a value in the config, leave out values for the push portion and it should skip that part.
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Ok, two things.
1. I screwed up something right before pushing the code!
if (Constants.pushToken != "" && Constants.pushUser != "")
{
Alert_Push("Watched Plate", "You may be interested in this plate that just went by the house...", "Blah");
}

(Add the part in red, comma "Blah") to get rid of the compiler error.

Because of the != "" -- if you leave the config section blank, it won't try to push, so you don't need an account. The compiler error is just a mistake I made. I'll fix and push to git for the next people...
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
The code should always first try to download a full 2 days of data whenever you start the auto download. Curious which timezone you are in?
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Updated (to help others)

Added your code.
"Excluded" the temp key that the project is looking for.
Created a NEW temp key (so the names match)
Compiled.

No errors in compiling.

Will move on to the settings tab, and connecting to the database (I forgot my database string, so I am figuring out how to find it)
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
The program reads the CSV directly to memory to import into the database, and then saves to a CSV to the archive location only if that is set. It only archives if new information, so if you ran it once (full download) without Archive set and than set Archive and the next import had no new plates, it doesn't save CSV.

Key part here is row 243
upload_2019-8-11_11-48-15.png

And row 290.
upload_2019-8-11_11-48-33.png


If no DB string, it couldn't import to DB, it may have been hitting a try/catch error before it ever tried to Archive.
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Yup, i cannot figure out for the life of me the correct ways to connect to the database with the LPR_Downloader, with the Micrososft SQL Server Management Studio, I can connect, no problems, but I cannot get the string right for LPR_Downloader.
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Here's a more detailed explanation of what I believe I left as a default...
Data Source=.\LPR;Initial Catalog=LPR;Persist Security Info=True;Integrated Security=true

Data Source = the SQL Server and Instance. I named my instance LPR and have it sitting on the local server, so .\LPR
If you left the SQL Instance default of SQLEXPRESS it would be .\SQLEXPRESS
If you have the SQL Server on another machine with the default instance, it would be MACHINENAME\SQLEXPRESS

Initial Catalog is the database name, I named my DB LPR.
As long as you went with the default of Windows Authentication (and not SQL Authentication) and have the LPR Downloader on the same machine as the Database, then the last two variables should take care of it.
If you use SQL Authentication or have it on another machine, you may need to specify more information there. @DLONG2 may be able to better help with alternate connection variables when it's remote like that.
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
Here's a more detailed explanation of what I believe I left as a default...
Data Source=.\LPR;Initial Catalog=LPR;Persist Security Info=True;Integrated Security=true

Data Source = the SQL Server and Instance. I named my instance LPR and have it sitting on the local server, so .\LPR
If you left the SQL Instance default of SQLEXPRESS it would be .\SQLEXPRESS
If you have the SQL Server on another machine with the default instance, it would be MACHINENAME\SQLEXPRESS

Initial Catalog is the database name, I named my DB LPR.
As long as you went with the default of Windows Authentication (and not SQL Authentication) and have the LPR Downloader on the same machine as the Database, then the last two variables should take care of it.
If you use SQL Authentication or have it on another machine, you may need to specify more information there. @DLONG2 may be able to better help with alternate connection variables when it's remote like that.
So yeah not sure what I did wrong (I thought that was the initial reason wrong, so I adjusted it and tried different variations.

This time I adjusted it and LET it run for about 10 minutes.

Now I get



Would that be correct? 0 imported, 0 skipped?

EDIT: I have even tried "uploading manually" my old CSV files and I get no data in the database (viewer)





LPR Downloader Settings: (I do not know why there is LOCAL LPR WebServer, as this is a PAID extra from OPENLPR, so i left that as default)


AS you can see from the main LPR Downloader Page (logs me in), sees plate, but does not seem to download anything to the DB.


Neighborhood LPR does have the same DB connection but it seems the DB has no data.




AS you can see from my DB, I have the default SQLEXPRESS with a database created LPR
 
Last edited:

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Can you expand the LPR database and look at the tables?
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
I'd say you have the DB connection correct, because that "Import History" actually writes to and reads from the DB.
I don't think it's hitting an error because the section that I have in a try/catch includes the "Save Import History" which is at the end. So if it hit an error loading into the DB, it should never get to the "Save Import History" portion.

Unless... It's only getting to "Save Import History" when there are no new records to import -- and the bigger imports (manual, and when it starts auto and does a full two-days) are getting an error.
Probably an easy test there, if you Stop / Start auto download, does it put an entry in the History? If it's not, best option would probably be to "step through" the code and see at what point it is hitting an error.
 

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
If it had an error here, it would never hit the Import History, so I think this is fine. Which means it is getting the Stream and correctly checking the size.
upload_2019-8-11_14-26-6.png

So my initial guess, if it's hitting an error, is that it is after this but before Save Import History. So row 210 to 299. Some break points in here and stepping through it should help identify the issue.
 

Nyghthawk

Getting the hang of it
Joined
May 29, 2018
Messages
118
Reaction score
23
Location
92679
I'd say you have the DB connection correct, because that "Import History" actually writes to and reads from the DB.
I don't think it's hitting an error because the section that I have in a try/catch includes the "Save Import History" which is at the end. So if it hit an error loading into the DB, it should never get to the "Save Import History" portion.

Unless... It's only getting to "Save Import History" when there are no new records to import -- and the bigger imports (manual, and when it starts auto and does a full two-days) are getting an error.
Probably an easy test there, if you Stop / Start auto download, does it put an entry in the History? If it's not, best option would probably be to "step through" the code and see at what point it is hitting an error.
I have stop and started the auto download, everything still says 0 imported, 0 skipped. edited: With added time stamp for new time that I started and stopped.
 
Last edited:

Gymratz

Pulling my weight
Joined
Jun 24, 2017
Messages
114
Reaction score
130
Did it create a new import row when you did that, or not and waited for the next timer-tick to put in an entry?
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Go into the "LPR_Downloader.cs" code and place a breakpoint as indicated in the snapshot below. Then run the code (pressing the Green "Start" arrow at the top toolbar). Then try to manually upload a CSV, and when the code stops at the breakpoint, keep slowly pressing the F10 key to step through the code, and follow what it does.

downloader_break.png
 
Top