We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

how to get data from Openclinica

Hello,

I’m new in that community. We’ve just began to use OpenClinica and looking for different ways to increase OC functionalities.
After long and unsuccessful search thru forum and on the web, i finally post my question here.

I’m looking for a simple way (specific development or Web-service) to get of our openclinica site a list of users, roles and their password. My goal would be to use this list in another program (in PHP) to verify if users can be allowed do some things. I have to precise that I’m a not very good in programming in OC technical environment.

Thanks for your help

Regards

Cordialement,

Pierre HEBEL
Système d'Information DRCI, SIG

http://www.chu-nice.fr/images/stories/pro/recherche/Logo_DRCI.jpg

Département de la Recherche Clinique et de l’Innovation
Hôpital de Cimiez
4 avenue Reine Victoria – BP 1179 – 06003 Nice Cedex 1

Comments

  • mvirtosumvirtosu Posts: 276 ✭✭
    Pierre,

    I use the following SQL query to retrieve the user information from the Postgres database:

    select distinct ua.user_name, ua.email, ua.passwd as PASSWORD, s.study_ID as SITEID from user_account ua JOIN study_user_role sur ON ua.user_name = sur.user_name JOIN study s ON s.study_ID = sur.study_ID AND sur.status_id = 1 WHERE s.study_ID = XXX or s.parent_study_ID = XXX

    I am running the above query from a .NET program I wrote using the Npgsql ADO.NET driver.
    Hope this helps,

    Mihai
    Sent: Tuesday, February 26, 2013 4:09 AM
    To: [email protected]
    Subject: [Users] how to get data from Openclinica

    Hello,

    I’m new in that community. We’ve just began to use OpenClinica and looking for different ways to increase OC functionalities.
    After long and unsuccessful search thru forum and on the web, i finally post my question here.

    I’m looking for a simple way (specific development or Web-service) to get of our openclinica site a list of users, roles and their password. My goal would be to use this list in another program (in PHP) to verify if users can be allowed do some things. I have to precise that I’m a not very good in programming in OC technical environment.

    Thanks for your help

    Regards

    Cordialement,

    Pierre HEBEL
    Système d'Information DRCI, SIG

    http://www.chu-nice.fr/images/stories/pro/recherche/Logo_DRCI.jpg

    Département de la Recherche Clinique et de l’Innovation
    Hôpital de Cimiez
    4 avenue Reine Victoria – BP 1179 – 06003 Nice Cedex 1
  • jjahrjjahr Posts: 41
    Pierre,

    I don’t pull or decrypt passwords from the db, as it only adds to your security risk and not required for any OC functionality. However, below are two scripts that we use for pulling user access. Both require access to the Postgres data tables.

    System Access:

    SELECT distinct
    user_account.user_name
    , user_account.first_name
    , user_account.last_name
    , user_type.user_type
    , user_account.email
    , status.name AS user_status
    FROM
    user_account
    , user_type
    , status
    WHERE
    user_account.user_type_id = user_type.user_type_id
    AND user_account.status_id = status.status_id
    ORDER BY
    status.name
    , user_account.user_name

    User study roles:

    SELECT
    study_user_role.user_name
    , user_account.first_name
    , user_account.last_name
    -- , study_user_role.role_name
    , CASE
    WHEN study_user_role.role_name = 'coordinator'
    THEN 'Data Manager'
    WHEN study_user_role.role_name = 'ra'
    THEN 'Data Entry Person'
    WHEN study_user_role.role_name = 'Data Specialist'
    THEN 'Data Specialist'
    WHEN study_user_role.role_name = 'monitor'
    THEN 'Monitor'
    WHEN study_user_role.role_name = 'director'
    THEN 'Study Director'
    WHEN study_user_role.role_name = 'admin'
    THEN 'root'
    END AS user_role
    , study.name AS study_name
    , status.name AS user_status
    FROM
    user_account
    , study_user_role
    , study
    , status
    WHERE
    user_account.user_name = study_user_role.user_name
    AND study_user_role.study_id = study.study_id
    AND user_account.status_id = status.status_id
    ORDER BY
    status.name
    , study_user_role.user_name
    , study.name

    Joe
    Sent: Tuesday, February 26, 2013 11:32 AM
    To: [email protected]
    Subject: Re: [Users] how to get data from Openclinica

    Pierre,

    I use the following SQL query to retrieve the user information from the Postgres database:

    select distinct ua.user_name, ua.email, ua.passwd as PASSWORD, s.study_ID as SITEID from user_account ua JOIN study_user_role sur ON ua.user_name = sur.user_name JOIN study s ON s.study_ID = sur.study_ID AND sur.status_id = 1 WHERE s.study_ID = XXX or s.parent_study_ID = XXX

    I am running the above query from a .NET program I wrote using the Npgsql ADO.NET driver.
    Hope this helps,

    Mihai
    Sent: Tuesday, February 26, 2013 4:09 AM
    To: [email protected]
    Subject: [Users] how to get data from Openclinica

    Hello,

    I’m new in that community. We’ve just began to use OpenClinica and looking for different ways to increase OC functionalities.
    After long and unsuccessful search thru forum and on the web, i finally post my question here.

    I’m looking for a simple way (specific development or Web-service) to get of our openclinica site a list of users, roles and their password. My goal would be to use this list in another program (in PHP) to verify if users can be allowed do some things. I have to precise that I’m a not very good in programming in OC technical environment.

    Thanks for your help

    Regards

    Cordialement,

    Pierre HEBEL
    Système d'Information DRCI, SIG

    http://www.chu-nice.fr/images/stories/pro/recherche/Logo_DRCI.jpg

    Département de la Recherche Clinique et de l’Innovation
    Hôpital de Cimiez
    4 avenue Reine Victoria – BP 1179 – 06003 Nice Cedex 1

    This message contains confidential information and is intended only for the individual(s) named. If you are not an addressee, any dissemination, distribution, or copying of this communication is strictly prohibited. Please notify the sender immediately if you have inadvertently received this message and delete it from your computer system.
This discussion has been closed.