OpenALPR Tool - Save and Query CSV Exports

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.
 
  • Like
Reactions: tech101
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.....
 
  • Like
Reactions: DLONG2
SyconsciousAu said:
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.
Click to expand...
I changed line 502 "zz" to correct offset for uk eastern standard time and i am getting same as you
 
riluxg said:
I changed line 502 "zz" to correct offset for uk eastern standard time and i am getting same as you
Click to expand...
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.
 
SyconsciousAu said:
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.
Click to expand...
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
 
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
 
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?
 
  • Like
Reactions: DLONG2
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
 
DLONG2 said:
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
Click to expand...

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
 
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()}
 
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
 
hopalong said:
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
Click to expand...

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.
 
  • Like
Reactions: hopalong
wittaj said:
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.
Click to expand...


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
 
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
 
  • Like
Reactions: tech101 and hopalong
wittaj said:
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
Click to expand...

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!
 
Randomly found the downloader not responding so I restarted it, Is there a way to download the time gap?
 
33696933 said:
Randomly found the downloader not responding so I restarted it, Is there a way to download the time gap?
Click to expand...

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.
 
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
 
33696933 said:
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
Click to expand...

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
 
  • Like
  • Wow
Reactions: 33696933 and tech101
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.
 
You must log in or register to reply here.
Top Bottom