OpenALPR Tool - Save and Query CSV Exports

SyconsciousAu

Getting comfortable
Joined
Sep 13, 2015
Messages
872
Reaction score
825
I have an idea where the bug is. The code at line 505 which sets the time offset throws a syntax error as written

+10%3A00&end=2022-07-06T19%3A30%3A59%2b+10%3A00&order=desc&format=csv

The plus sign before the time offset causes an invalid start time error. I changed the "zz" at line 502 to the correct offset for Australian Eastern Standard Time (10) and I can cut and past the resulting URL from Line 509 into a web browser, and the csv download happens, but I get no tables like the above poster.
 

tech101

Known around here
Joined
Mar 30, 2015
Messages
1,472
Reaction score
2,125
Location
SF BayArea, USA
Hi guys recently downloader stopped working on 7/6/22 there is a fix for it. Please follow these instructions.

First thing First let's take a good backup. Before we make ANY Changes. So in case something messes up you can always go back.

Step 1) - Open Sql Managment Studio

Step 2) - Login & Select LPR database By Right click. Click Task & Than Backup.

1657228916497.png



Step 3) Click Ok To Take backup by Giving it the path and file name..


1657229017425.png



Backup Is complete Now :) So Backup part is done...

IF, you are running the NSSM Service( aka the Non-Sucking Service Manager) Than PLEASE STOP YOUR BACKGROUND Downloader & Viewer Running Service. Before you Proceed.

Now Lets begin with the Actual Fix...

Step 1) - Open Sql Managment Studio Click on Tools--->Options--->Designers---> Uncheck Prevent saving changes that requires table-recreations.

1657229354204.png



1657229394604.png


Click Ok



Step 2) Click Plus Icon where it says LPR database on Sql Management Studio. Than Click on + Icon On Tables To Expand Than Click + on dbo.LPR_PlateHits


1657229194648.png



Step 3) Right Click on Pk and Click Modify

1657229564418.png



Step 4) From the Pk Filed Data Type Drop down the box and Choose bigint on top and close by hitting X and hit save and yes.


1657229640808.png



Step 5) - Expand the Column again by Clicking + icon under dbo.LPR_PlateHits_ToHide. and than + Icon of Keys.


1657229784434.png



Step 6) - Right Click on Pk_PlateHits_ToHide and Click Modify.


1657229888149.png



Step 7) - Column Name pk Needs to be changed here as well. DataType needs to be set to bigint from DropDown Menu and Close and save.


1657229951768.png



That is it.. Restart Service now. It should be working.....
 

riluxg

Getting the hang of it
Joined
Aug 1, 2015
Messages
63
Reaction score
26
Location
UK
I have an idea where the bug is. The code at line 505 which sets the time offset throws a syntax error as written

+10%3A00&end=2022-07-06T19%3A30%3A59%2b+10%3A00&order=desc&format=csv

The plus sign before the time offset causes an invalid start time error. I changed the "zz" at line 502 to the correct offset for Australian Eastern Standard Time (10) and I can cut and past the resulting URL from Line 509 into a web browser, and the csv download happens, but I get no tables like the above poster.
I changed line 502 "zz" to correct offset for uk eastern standard time and i am getting same as you
 

SyconsciousAu

Getting comfortable
Joined
Sep 13, 2015
Messages
872
Reaction score
825
I changed line 502 "zz" to correct offset for uk eastern standard time and i am getting same as you
I thavent applied @tech101 's fix to the database yet. I'm hoping when I do that will fix the other issues.

Going to have a play with the code to see if I can come up with a solution. Using zz as a variable then running regex over it to remove the plus sign seems promising.
 

riluxg

Getting the hang of it
Joined
Aug 1, 2015
Messages
63
Reaction score
26
Location
UK
I thavent applied @tech101 's fix to the database yet. I'm hoping when I do that will fix the other issues.

Going to have a play with the code to see if I can come up with a solution. Using zz as a variable then running regex over it to remove the plus sign seems promising.
i have applied tech101 fix and and spent hours changing the utc off set times to no avail the downloader will connect but will not download any info
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
I use Visual Basic in my application. Please don't try to use this code directly into the Downloader application, as it uses a different language. This is how I derive the true local times for the database:

These variables are set for a wide scope for the whole form:
Dim STRepoch_time_end As String
Dim STRepoch_time_start As String

And in the parsing of the JSON code, I set these values:
STRepoch_time_end = j("epoch_end")
STRepoch_time_start = j("epoch_start")

In the Import-To-Database sub, I use these lines to derive the true local time before inserting into the database:
Dim dteUnixDateEnd As DateTime
Dim dteLocalDateEnd As DateTime
Dim dteUnixDateStart As DateTime
Dim dteLocalDateStart As DateTime
Dim strUnixDateEnd As String
Dim strLocalDateEnd As String
Dim strUnixDateStart As String
Dim strLocalDateStart As String

dteUnixDateEnd = UnixTimeStampToDateTime(Val(STRepoch_time_end))
strUnixDateEnd = dteUnixDateEnd.ToString("yyyy-MM-dd" & "T" & "HH:mm:ss.fff" & "Z")

dteLocalDateEnd = UnixTimeStampToDateTimeLocal(Val(STRepoch_time_end))
strLocalDateEnd = dteLocalDateEnd.ToString("yyyy-MM-dd HH:mm:ss")

dteUnixDateStart = UnixTimeStampToDateTime(Val(STRepoch_time_start))
strUnixDateStart = dteUnixDateStart.ToString("yyyy-MM-dd" & "T" & "HH:mm:ss.fff" & "Z")

dteLocalDateStart = UnixTimeStampToDateTimeLocal(Val(STRepoch_time_start))
strLocalDateStart = dteLocalDateStart.ToString("yyyy-MM-dd HH:mm:ss")

And in the INSERT query, I use these values to insert into these fields:
epoch_time_end,epoch_time_start (field names)
strUnixDateEnd,strUnixDateStart (values)

And here are the functions which do the UNIX conversions to local time:

Public Shared Function UnixTimeStampToDateTime(ByVal unixTimeStamp As Double) As DateTime
Dim dtDateTime As System.DateTime = New DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc)
dtDateTime = dtDateTime.AddMilliseconds(unixTimeStamp).ToUniversalTime()
Return dtDateTime
End Function


Public Shared Function UnixTimeStampToDateTimeLocal(ByVal unixTimeStamp As Double) As DateTime
Dim dtDateTime As System.DateTime = New DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc)
dtDateTime = dtDateTime.AddMilliseconds(unixTimeStamp).ToLocalTime()
Return dtDateTime
End Function
 

Fubduck

Getting the hang of it
Joined
Jul 10, 2018
Messages
109
Reaction score
72
Location
colorado
There's a cookie option in the retrieval action that designates a timezone. I use the following curl command to grab data every minute and it is always in my timezone in the downloaded .csv file.

Code:
curl -m 5 --cookie 'csrftoken=64charactercsrftokengoeshere; username=openalpruserneamegoeshere; client-tz-name=America/Denver; sessionid=32characterlongsessionidgoeshere;' 'https://cloud.openalpr.com/api/search/group?topn=1000&start=2022-07-15T23:58:01-06:00&end=2022-07-15T00:01:01-06:00&order=desc&format=csv' --output openalpr-group-results.csv'
I'm not familiar with this application and how it retrieves the data but is this something that could help with the timezone issues?
 

riluxg

Getting the hang of it
Joined
Aug 1, 2015
Messages
63
Reaction score
26
Location
UK
I use the daily report quit often, and its a great feature but I don't use forensics so state, vin, year,
make, and model columns are empty.

alpr report.png


I would like to add the status and description fields to the report.
Anybody know how
 

riluxg

Getting the hang of it
Joined
Aug 1, 2015
Messages
63
Reaction score
26
Location
UK
I use Visual Basic in my application. Please don't try to use this code directly into the Downloader application, as it uses a different language. This is how I derive the true local times for the database:

These variables are set for a wide scope for the whole form:
Dim STRepoch_time_end As String
Dim STRepoch_time_start As String

And in the parsing of the JSON code, I set these values:
STRepoch_time_end = j("epoch_end")
STRepoch_time_start = j("epoch_start")

In the Import-To-Database sub, I use these lines to derive the true local time before inserting into the database:
Dim dteUnixDateEnd As DateTime
Dim dteLocalDateEnd As DateTime
Dim dteUnixDateStart As DateTime
Dim dteLocalDateStart As DateTime
Dim strUnixDateEnd As String
Dim strLocalDateEnd As String
Dim strUnixDateStart As String
Dim strLocalDateStart As String

dteUnixDateEnd = UnixTimeStampToDateTime(Val(STRepoch_time_end))
strUnixDateEnd = dteUnixDateEnd.ToString("yyyy-MM-dd" & "T" & "HH:mm:ss.fff" & "Z")

dteLocalDateEnd = UnixTimeStampToDateTimeLocal(Val(STRepoch_time_end))
strLocalDateEnd = dteLocalDateEnd.ToString("yyyy-MM-dd HH:mm:ss")

dteUnixDateStart = UnixTimeStampToDateTime(Val(STRepoch_time_start))
strUnixDateStart = dteUnixDateStart.ToString("yyyy-MM-dd" & "T" & "HH:mm:ss.fff" & "Z")

dteLocalDateStart = UnixTimeStampToDateTimeLocal(Val(STRepoch_time_start))
strLocalDateStart = dteLocalDateStart.ToString("yyyy-MM-dd HH:mm:ss")

And in the INSERT query, I use these values to insert into these fields:
epoch_time_end,epoch_time_start (field names)
strUnixDateEnd,strUnixDateStart (values)

And here are the functions which do the UNIX conversions to local time:

Public Shared Function UnixTimeStampToDateTime(ByVal unixTimeStamp As Double) As DateTime
Dim dtDateTime As System.DateTime = New DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc)
dtDateTime = dtDateTime.AddMilliseconds(unixTimeStamp).ToUniversalTime()
Return dtDateTime
End Function


Public Shared Function UnixTimeStampToDateTimeLocal(ByVal unixTimeStamp As Double) As DateTime
Dim dtDateTime As System.DateTime = New DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc)
dtDateTime = dtDateTime.AddMilliseconds(unixTimeStamp).ToLocalTime()
Return dtDateTime
End Function
string str_EndDate = DateTime.Now.AddMinutes(1).ToString("yyyy-MM-dd");
string str_EndHours = DateTime.Now.AddMinutes(1).ToString("HH");
string str_EndMinutes = DateTime.Now.AddMinutes(1).ToString("mm");

string str_UTC_Offset = DateTime.Now.ToString("zz");
if (!str_UTC_Offset.Contains("-") & str_UTC_Offset != "0" & str_UTC_Offset != "+0")
{
str_UTC_Offset = "%2b" + str_UTC_Offset;
}


string _url = "" + str_StartDate + "T" + str_StartHours + "%3A" + str_StartMinutes + "%3A00" + str_UTC_Offset + "%3A00&end=" + str_EndDate + "T" + str_EndHours + "%3A" + str_EndMinutes + "%3A59" + str_UTC_Offset + "%3A00&order=desc&format=csv";


This does not work for the uk
 

YouHF

n3wb
Joined
Aug 1, 2015
Messages
12
Reaction score
7
I've been hitting a bug pretty often recently - anyone have the same thing? I already fixed the bigInt issue from last month.

Error Message: This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row.

Exception object:
Data: {System.Collections.ListDictionaryInternal}
HResult: -2146232024
HelpLink: null
InnerException: null
Message: "This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row."
Source: "System.Data"
StackTrace (line numbers will be off since I have done some unrelated customization):
" at System.Data.DataRow.GetDefaultRecord()\r\n at System.Data.DataRow.get_Item(Int32 columnIndex)\r\n at LPR_Downloader.frm_LPR_Downloader.UploadStream(MemoryStream streamCSV) in C:\\Users\\sbail\\Downloads\\LPR_Downloader-master_20220712\\LPR_Downloader-master\\LPR_Downloader.cs:line 599\r\n at LPR_Downloader.frm_LPR_Downloader.<DownloadFile>d__26.MoveNext() in C:\\Users\\sbail\\Downloads\\LPR_Downloader-master_20220712\\LPR_Downloader-master\\LPR_Downloader.cs:line 515"
TargetSite: {Int32 GetDefaultRecord()}
 

hopalong

Getting the hang of it
Joined
Apr 19, 2021
Messages
68
Reaction score
33
Location
California
I accidentally checked the "Auto-Update All" box and now all my licenses plates are showing incorrectly. is there anyway to undo that?


1663006998322.png
 

wittaj

IPCT Contributor
Joined
Apr 28, 2019
Messages
24,428
Reaction score
47,544
Location
USA
I accidentally checked the "Auto-Update All" box and now all my licenses plates are showing incorrectly. is there anyway to undo that?


View attachment 139627
I hate when that happens.

Two ways - it did that due to not syncing before you made the change. So if it isn't many, manually change them. I had that happen once but fortunately it was only 8 plates.

The other option is to go to the most recent backup (which would be midnight if you checked the backup box) and restore from there, but you lose today unless it is pulled back in from Rekor (hit or miss). I had to do that once when my internet port went out and it wasn't updating from Rekor. Sucked to lose a day, but better than it all.
 

hopalong

Getting the hang of it
Joined
Apr 19, 2021
Messages
68
Reaction score
33
Location
California
I hate when that happens.

Two ways - it did that due to not syncing before you made the change. So if it isn't many, manually change them. I had that happen once but fortunately it was only 8 plates.

The other option is to go to the most recent backup (which would be midnight if you checked the backup box) and restore from there, but you lose today unless it is pulled back in from Rekor (hit or miss). I had to do that once when my internet port went out and it wasn't updating from Rekor. Sucked to lose a day, but better than it all.

It's showing ALL the licenses as the one that got accidentally updated. I do have daily backups. Any guidance on how to restore the backup? Much appreciated.

EDIT
Found it in the sql manager. DB restored. Thanks for the tip. Phew.

1663007983647.png
 

wittaj

IPCT Contributor
Joined
Apr 28, 2019
Messages
24,428
Reaction score
47,544
Location
USA
Awesome. It is a scare!

I try now to remember to make a backup before I start messing with plates so I don't have to go all the way to midnight! But I forget as well LOL
 

hopalong

Getting the hang of it
Joined
Apr 19, 2021
Messages
68
Reaction score
33
Location
California
Awesome. It is a scare!

I try now to remember to make a backup before I start messing with plates so I don't have to go all the way to midnight! But I forget as well LOL
When doing the DB Restore It gave me a scare as it said fail or something to that extent. I had to extract the file from the zip then all worked. Thanks again!
 

33696933

Pulling my weight
Joined
Mar 29, 2020
Messages
66
Reaction score
194
Location
NY
Randomly found the downloader not responding so I restarted it, Is there a way to download the time gap?
 

tech101

Known around here
Joined
Mar 30, 2015
Messages
1,472
Reaction score
2,125
Location
SF BayArea, USA
Randomly found the downloader not responding so I restarted it, Is there a way to download the time gap?
As Long OpenALPR Website has plates, I do NOT see why it wont download the missing plates once it reconnnects. It should download the plates as long it is showing on the OpenALPR Website.. You can also manually try to upload the CSV file into the downloader.
 

33696933

Pulling my weight
Joined
Mar 29, 2020
Messages
66
Reaction score
194
Location
NY
Yeah I swear it used to do that but it's not doing that. Where does one get the CSV files I vaguely remember something about it but not exactly? TIA
 

DLONG2

Known around here
Joined
May 17, 2017
Messages
763
Reaction score
454
Yeah I swear it used to do that but it's not doing that. Where does one get the CSV files I vaguely remember something about it but not exactly? TIA
For the CSV downloads, it is easy to miss. Log into your ALPR Dashboard. Look under the 'Plates this week' column, and click on the 'View details' link. Then on the next page, the download link will appear on the bottom-left.
csv-download-button.jpg
 

33696933

Pulling my weight
Joined
Mar 29, 2020
Messages
66
Reaction score
194
Location
NY
thanks I remember now.
Weirdly it froze again overnight and wont import the gaps it was frozen before restarting even with the manual import . Somethings not right but I'll see if it happens again.
 
Top