Importing data from DailyQA3

[ill...@gmail.com]

Hello,

At CHUM we are implementing some tests that will import data from DQA3 automatically to avoid having to transfer the data manually.

We are currently using version 0.27 of QATrack+.

We added a composite string test called importdqa with the code found bellow. The code outputs the data in a dictionary that is converted to json format so it fits into the string. This json data is then loaded by the other composite tests that will hold the individual values for the measures with code that looks like this:

import json
dqa = json.loads(importdqa)
if ('output_6mv' in dqa):
spro_output_6mv = dqa['output_6mv']

Here we have a composite test named spro_output_6mv that loads the output of the dqa3 query string composite. There are additional composite tests that calculate symetry and other tests.

Any comments or questions are welcome.

<CODE>
importdqa='ok' # if we get an error, this test will contain an error message
# vim: set sw=4 ts=8 sts=4 et foldmethod=indent fileencoding=utf-8 :
#===============================================================================
#
# FILE: importdqa.py
#
# DESCRIPTION: Read DQA3 data to populate QATrack database
# DQA3 uses a Firebird 1.5 database to store it's data
#
# AUTHOR: Stefan Michalowski
# Ellis Mitrou
# COMPANY: CHUM
# VERSION: 1.0
# CREATED: 2018-07-25
# REVISION: ---
#===============================================================================
import MySQLdb # needed to get room serial number
import fdb # needed to talk with firebird database
import json # needed to send data out to other tests
exportdata=dict() # data that will be exported will be in a dictionary that will be jsoned into string
try: # catching errors with fdb
unit_number = META['unit_number'] # retrieve unit number but we need serial number

# we connect to the qatrack database to get the unit serial number (that is defined in the DQA3 database)
qatrack_db = MySQLdb.connect(host="localhost", user="qatrack_user", passwd="qatrack_password", db="qatrack_db")
qatrack_cursor = qatrack_db.cursor()
qatrack_cursor.execute("SELECT serial_number FROM units_unit WHERE number = %s",(unit_number,))
if qatrack_cursor.rowcount > 0:
row = qatrack_cursor.fetchone() # serial number is in row[0]

# now we connect to the DQA3 database, we had to create a special readdata user in the database
dqa3_conn = fdb.connect( host='dqa3_hostname', database='dqa3_database', port=3050, user='dqa_read_user', password='dqa_read_password')
dqa3_cursor = dqa3_conn.cursor()

# this is the query in the DQA3 database that retrieves today's latest measurements per room/energy
sql_query = """
SELECT
r.tree_name AS Room
,e.tree_name AS Energy
,SUBSTRING(CAST (a.measured_datetime AS VARCHAR(24)) FROM 1 FOR 16) AS measured_datetime
,((a.t_counts-a.t_offset)-a.t_bkg*(a.session_time-a.offset_time))*k.t_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS T_dose
,((a.r_counts-a.r_offset)-a.r_bkg*(a.session_time-a.offset_time))*k.r_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS R_dose
,((a.b_counts-a.b_offset)-a.b_bkg*(a.session_time-a.offset_time))*k.b_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS B_dose
,((a.l_counts-a.l_offset)-a.l_bkg*(a.session_time-a.offset_time))*k.l_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS L_dose
,((a.c_counts-a.c_offset)-a.c_bkg*(a.session_time-a.offset_time))*k.c_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS C_dose
FROM
dqa3_data a
INNER JOIN dqa3_template e ON a.set_key = e.set_key
INNER JOIN dqa3_machine m ON e.mach_key = m.mach_key
AND m.MACHSERIAL = ?
INNER JOIN room r ON m.room_key = r.room_key
INNER JOIN device v ON a.device_key = v.device_key
INNER JOIN dqa3_calibration k ON v.device_key = k.device_key
AND a.cal_key = k.cal_key
WHERE
a.flags != 5
AND CAST (a.measured_datetime AS DATE) = CAST ('Now' AS DATE)
AND a.measured_datetime = (
SELECT
MAX(data.measured_datetime)
FROM
dqa3_data data
INNER JOIN dqa3_template template ON data.set_key = template.set_key
AND template.tree_name = e.tree_name
INNER JOIN dqa3_machine machine ON template.mach_key = machine.mach_key
AND machine.MACHSERIAL = m.MACHSERIAL
WHERE
a.flags != 5
AND CAST (data.measured_datetime AS DATE) = CAST ('Now' AS DATE)
GROUP BY
machine.MACHSERIAL
,template.tree_name
)
"""
dqa3_cursor.execute(sql_query,(str(row[0]),)) # row[0] has the unit serial number

#loop over results, we output in a dictionary with the keys being of measure_energy format (ex.: bottom_6mv, left_10fff)
for (room,energy,measured_datetime,top,right,bottom,left,output) in dqa3_cursor:
exportdata['bottom_' + str(energy).replace(' ','').lower()] = bottom
exportdata['top_' + str(energy).replace(' ','').lower()] = top
exportdata['left_' + str(energy).replace(' ','').lower()] = left
exportdata['right_' + str(energy).replace(' ','').lower()] = right
exportdata['output_' + str(energy).replace(' ','').lower()] = output
if len(exportdata) > 0:
importdqa = json.dumps(exportdata) # convert output to json so it can be read by the other tests
else:
importdqa = 'No DQA3 data found'
else:
importdqa = "Unit not found"
except fdb.fbcore.ProgrammingError as e:
importdqa = "ProgrammingError: " + str(e)
except fdb.fbcore.DatabaseError as e:
importdqa = "DatabaseError: " + str(e)
except ValueError as e:
importdqa = "ValueError: " + str(e)
except TypeError as e:
importdqa = "TypeError: " + str(e)
except Exception as e:
importdqa = "Exception: " + str(e)
except:
importdqa = "Unknown Error"
</CODE>

Regards,
--
Stefan Michalowski (g_m...@ptaff.ca)

[Randle Taylor]

Thanks for sharing Stefan!  It is a nice idea for automatically importing from an external data source.   

Couple of comments:

1) It looks like you may run into issues if you go back and edit a previous days test since it will pull in the latest data from the DQA3.  For example if you edit Mondays test list on the following Wednesday, it will pull in the data from Wednesday rather than Monday?  Perhaps you have already thought of this though.  If not, you may be able to use the `META['work_started']` context variable rather than `Now` to ensure you are pulling data from the correct date. 

2) If you prefer you can get the unit serial number using the Django ORM rather than a raw SQL query.  Something like

from qatrack.units.models import Unit

serial_num = Unit.objects.get(number=META['unit_number']).serial_number

Thanks again!

Randy

--
You received this message because you are subscribed to the Google Groups "QATrack+" group.
To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.
To post to this group, send email to qat...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stefan Michalowski]

Hello Randy,

After I had posted my solution, I added an if on META['work_completed'] to avoid reloading data when reviewing. In addition, I modified the query to use META['work_started'] as you suggested instead of Now.

I will modify my code to use the Django ORM to get the unit serial as you suggest. It is cleaner.

I will then post the modified version on the google group.

Do you think it would be interesting to post a whole description of the configuration of such as test on the Wiki?

Regards,

Stefan

To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+unsubscribe@googlegroups.com.

To post to this group, send email to qat...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Stefan "Mitch" Michalowski
Email: ill...@gmail.com

/* vim:tw=78:ts=4:sw=4:foldmethod=marker: */

[Randle Taylor]

Nice! Yes collecting scripts/snippets like this on the wiki would probably be a good idea as it will be easier to find there than on this mailing list. RT

To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.

To post to this group, send email to qat...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[ill...@gmail.com]

Hello,

New version of the code, with some improvements: user can get data from a previous date by entering it in the work_started, uses Django ORM instead of MySQL to get unit serial number, checks if work_completed is set or not to avoid reloading the data if this is a review.

<CODE>
importdqa='ok' # if we get an error, this test will contain an error message
# vim: set sw=4 ts=8 sts=4 et foldmethod=indent fileencoding=utf-8 :
#===============================================================================
#
# FILE: importdqa.py
#
# DESCRIPTION: Read DQA3 data to populate QATrack database
# DQA3 uses a Firebird 1.5 database to store it's data
#

# AUTHOR: Stefan Michalowski (stefan.mich...@ssss.gouv.qc.ca)
# Ellis Mitrou (ellis.mi...@ssss.gouv.qc.ca)
# COMPANY: CHUM
# VERSION: 1.2
# CREATED: 2018-07-25
# REVISION: 1.0 -- 2018-07-25 Initial version
# 1.1 -- 2018-07-26 Do not load if work_completed is filled
# Use work_started to filter DQA3 data
# 1.2 -- 2018-07-30 Replace MySQL query for Unit serial number by Django ORM code
# as suggested by Randle Taylor
#===============================================================================

import fdb # needed to talk with firebird database
import json # needed to send data out to other tests

from qatrack.units.models import Unit # needed to get Unit serial number

exportdata=dict() # data that will be exported will be in a dictionary that will be jsoned into string

work_completed = META['work_completed']

try: # catching errors with fdb

if ((work_completed is None) or (work_completed == '')): # DO not load data if work_completed is entered

serial_num = Unit.objects.get(number=META['unit_number']).serial_number

work_started = META['work_started'] # we will use work_started to filter DQA3 data so user can specify data from a specific date
if not ( (serial_num is None) or (serial_num == '') ):

# now we connect to the DQA3 database, we had to create a special readdata user in the database

dqa3_conn = fdb.connect( host='dqa_hostname', database='dqa3_database', port=3050, user='dqa_read_user', password='dqa_read_password')
dqa3_cursor = dqa3_conn.cursor()

# this is the query in the DQA3 database that retrieves today's latest measurements per room/energy
sql_query = """
SELECT
r.tree_name AS Room
,e.tree_name AS Energy
,SUBSTRING(CAST (a.measured_datetime AS VARCHAR(24)) FROM 1 FOR 16) AS measured_datetime
,((a.t_counts-a.t_offset)-a.t_bkg*(a.session_time-a.offset_time))*k.t_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS T_dose
,((a.r_counts-a.r_offset)-a.r_bkg*(a.session_time-a.offset_time))*k.r_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS R_dose
,((a.b_counts-a.b_offset)-a.b_bkg*(a.session_time-a.offset_time))*k.b_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS B_dose
,((a.l_counts-a.l_offset)-a.l_bkg*(a.session_time-a.offset_time))*k.l_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS L_dose
,((a.c_counts-a.c_offset)-a.c_bkg*(a.session_time-a.offset_time))*k.c_factor*v.ab_dose_cf*e.template_dose_cf*((a.temperature+273.2)/295.2*(101.33/a.pressure)) AS C_dose
FROM
dqa3_data a
INNER JOIN dqa3_template e ON a.set_key = e.set_key
INNER JOIN dqa3_machine m ON e.mach_key = m.mach_key

AND m.MACHSERIAL = '%s'

INNER JOIN room r ON m.room_key = r.room_key
INNER JOIN device v ON a.device_key = v.device_key
INNER JOIN dqa3_calibration k ON v.device_key = k.device_key
AND a.cal_key = k.cal_key
WHERE
a.flags != 5

AND CAST (a.measured_datetime AS DATE) = CAST ('%s' AS DATE)

AND a.measured_datetime = (
SELECT
MAX(data.measured_datetime)
FROM
dqa3_data data
INNER JOIN dqa3_template template ON data.set_key = template.set_key
AND template.tree_name = e.tree_name
INNER JOIN dqa3_machine machine ON template.mach_key = machine.mach_key
AND machine.MACHSERIAL = m.MACHSERIAL
WHERE
a.flags != 5

AND CAST (data.measured_datetime AS DATE) = CAST (a.measured_datetime AS DATE)

GROUP BY
machine.MACHSERIAL
,template.tree_name
)
"""

dqa3_cursor.execute(sql_query % (serial_num,work_started.strftime("%Y-%m-%d")))

[Simon Biggs]

Hi Randle,

I remember you mentioned potentially creating a site where tests can be shared. This would be a nice addition to that I think.

Cheers,
Simon

[Sašo Pulko]

Hi Stefan and others,

this script looks awesome. In our clinic, we use Beamchecker Plus from Standard Imaging for morning QA and I wanted to do the same thing, but my level of coding is not sufficient to do it. Do you think it is possible to adjust this code for Beamchecker Plus? Database for Beamchecker Plus is mdb and not firebird so this could be a problem.

If someone is willing to do it I can send a database.

Best,

Sašo

[Randle Taylor]

Hi Simon,

Yes this is exactly the kind of test that I hope people will share on a site like that.  Construction of that site is likely to begin in the fall after 0.3 is out.

RT

[Stefan Michalowski]

Hello Sašo,

The code should be reusable for other systems. You just need to adjust the query string and method for other DBs.

When I come back from my vacation, I could checkout your DB. Somewhere in September.

Though it requires knowing how the data is stored in the database and how to calculate the doses.

Regards,

Stefan

Dne torek, 31. julij 2018 00.51.36 UTC+2 je oseba Simon Biggs napisala:

--
You received this message because you are subscribed to a topic in the Google Groups "QATrack+" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/qatrack/YvS4PcfQRNg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to qatrack+unsubscribe@googlegroups.com.

To post to this group, send email to qat...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rachid....@gmail.com]

Dear Stefan, thank you for the SQL code about daily QA3 data import.
I have a question about DailyQA3....is not really linked to QATrack + until now. But I need first to merge 2 firebird database from DailyQA3 could you help me for that ? Thank you

[Stefan Michalowski]

Hello Rachid,

I don't know if I can help you. I think you should contact Sun Nuclear to get help with that.. Sorry

Le mar. 21 mai 2019, à 17 h 05, <rachid....@gmail.com> a écrit :

Dear Stefan, thank you for the SQL code about daily QA3 data import.
I have a question about DailyQA3....is not really linked to QATrack + until now. But I need first to merge 2 firebird database from DailyQA3 could you help me for that ? Thank you

--
You received this message because you are subscribed to the Google Groups "QATrack+" group.

To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.

To post to this group, send email to qat...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/4030b50f-3afa-48ae-9551-51df5f834799%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[xia...@gmail.com]

Good afternoon,

I just had a question about what directory you are placing the import code or is this a test you place in qatrack+?

My apologies I am new but trying to catch up.

Thanks,

Jamey

[Randle Taylor]

Hi Jamey,

This would be in a composite test in QATrack+.

Randy

--

You received this message because you are subscribed to the Google Groups "QATrack+" group.

To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/cf0e212b-e6a7-4f15-a7d6-f5cdef694c6a%40googlegroups.com.

[Harry]

Hello All,

We would like to import Daily QA3 data onto QATrack+. Where can I find the username and password for firebird database?

Thanks

Harkirat

[Stefan Michalowski]

Hello,

I think that you can use one of the users defined in the application. And you could look at all the strings in the executable for DailyQA3... there might be some hidden gems there.

I recommend creating a readonly user for the QATrack+ reader.

--

You received this message because you are subscribed to the Google Groups "QATrack+" group.
To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/905891e4-4a70-4e49-86c9-efa530ad656b%40googlegroups.com.

[Harry]

Thanks for your reply Stefan,

I was trying to connect to fdb server using a simple script below:

import fdb
dqa3_conn = fdb.connect( host='OXNETWIKI01', database='SNCData', port=3050, user='QATUSER', password='qatpass')

running this simple script is giving me following error:

(qatrack3) C:\deploy\venvs\qatrack3\Scripts>python C:\Users

op\QATracFDB.py

Traceback (most recent call last):

  File "C:\Users\Harkirat.Singh\Desktop\QATracFDB.py", line

    dqa3_conn = fdb.connect( host='OXNETWIKI01', database='

user='QATUSER', password='qatpass')

  File "C:\deploy\venvs\qatrack3\lib\site-packages\fdb\fbco

connect

    load_api(fb_library_name)

  File "C:\deploy\venvs\qatrack3\lib\site-packages\fdb\fbco

load_api

    setattr(sys.modules[__name__], 'api', ibase.fbclient_AP

  File "C:\deploy\venvs\qatrack3\lib\site-packages\fdb\ibas

__init__

    fb_library = WinDLL(fb_library_name)

  File "C:\Program Files\Python36\lib\ctypes\__init__.py",

    self._handle = _dlopen(self._name, mode)

OSError: [WinError 193] %1 is not a valid Win32 application

Firebird website https://firebirdsql.org/en/devel-python-driver/ mentions that FDB works with Firebird 2.0 and above, DQA3 uses Firebird 1.5.3 - how did you guys manage to connect? which version of fdb are you using, I installed fdb==2.0.1?

Many Thanks

Harkirat

To unsubscribe from this group and stop receiving emails from it, send an email to qat...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/905891e4-4a70-4e49-86c9-efa530ad656b%40googlegroups.com.

[Harry]

Hey guys, any idea why am I not able to connect? 

[Stefan Michalowski]

Hello Harkirat,

I remember having trouble with this too. There seems to be more than one FDB package for python. I think I use this one: https://pypi.org/project/fdb/#description, which is installed by simply running pip install fdb. I think it requires you to have the FDB client installed on the computer.

I run QATrack+ on a CentOS system (Linux), so things might be different from Windows.

The package should be backward compatible, though I did have some compatibility issues with one version.

Can you establish a connection to the firebird database outside of the QATrack+ environment? Can you establish a connection outside of python use the Firebird isql client?

To unsubscribe from this group and stop receiving emails from it, send an email to qatrack+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/94cd2837-ecc4-4758-984a-57fa29d41e8e%40googlegroups.com.

[Kurtis Dekker]

Hi, i realize it has been a while but I'm interested in getting this going in my center. I have the basics functional at this point, just one thing i'm unclear on:

How did you create a "read only" user in the QA3 firebird database? The QA3 database management application only allows creation of "Physicist" and "Tech" users, both of which can write entries (i.e. for performing the measurements).

Cheers,

-kurtis

[Randle Taylor]

I use FlameRobin to connect to Firebird databases.  Then a query something like:

CREATE ROLE READONLY_ROLE;
GRANT SELECT ON DQA3_DATA TO ROLE READONLY_ROLE;
GRANT SELECT ON DQA3_TREND TO ROLE READONLY_ROLE;
GRANT SELECT ON DQA3_MACHINE TO ROLE READONLY_ROLE;
GRANT SELECT ON DQA3_CALIBRATION TO ROLE READONLY_ROLE;
GRANT SELECT ON ROOM TO ROLE READONLY_ROLE;

CREATE USER RO_USER PASSWORD 'password';
GRANT READONLY_ROLE TO RO_USER;

should work to create a read only user with select privileges on the relevant tables (not sure I got all of them from the query but should give you the right idea.

Also relevant to this thread, I've been working on an application that runs outside of QATrack+ and can read from the DQA3 database (and other data sources) and post to QATrack+ via the API.  It got set aside for a little bit while I worked on other projects for clients, but I'm hoping to return to it soon.

Randy

To view this discussion on the web visit https://groups.google.com/d/msgid/qatrack/f0df278a-feac-48f7-86b1-10c26335095an%40googlegroups.com.

[Harry]

Hi,

I found these instructions, hope they are useful.

1. Created QATUSER and qatpass user on DailyQA3
2. Used isql.exe (used cmd to navigate to directory) at D:\SNC\QADatabase\Firebird\bin on  <server>   to connect to SNCData database using CONNECT ' <server> :SNCData' user 'SYSDBA' password 'omnipotent';
3. Granted read-only writes to QATUSER for all the tables using following: GRANT SELECT ON USERS TO xxxtablesxxx; More commands can be found on firebird isql documentation.
4. QUIT and reconnect using QATUSER, make sure the user has select permissions. CONNECT ' <server>  :SNCData' user 'QATUSER' password 'qatpass';
5. Test using simple query below  

Downloaded and installed firebird v2.5 64bit client-only in existing location (overwriting existing 32 bit files) D:\SNC\QADatabase\Firebird.

import fdb
import sys

try:
print("trying to connect....")
dqa3_conn = fdb.connect( host=' <server>  ', database='SNCData', port=3050, user='QATUSER', password='qatpass')
print("connection established...")
sql_query = """
select * from room
"""
dqa3_cursor = dqa3_conn.cursor()
dqa3_cursor.execute(sql_query)
print("query executed...")
for tup_result in dqa3_cursor:
print(f'{tup_result}')
except:
print("Unexpected error: ", sys.exc_info())

Cheers,
harry

[Kurtis Dekker]

Thanks Randy, this worked for me with the addition of select priveleges on the DEVICE and DQA3_TEMPLATE tables.

Cheers,

Kurtis