Introduction

This is a User Guide (wiki) for Open Enventory. The links to the software (original version by FR, and forked version by KV) are on the top bar.

About Open Enventory

• • a lab notebook,

  • a database for all spectroscopic data,

  • a chemical inventory and

  • a literature database.

• It is designed for the requirements of university groups and small companies, focuses primarily in Chemistry research.

• The laboratory journal uses the data from inventory management and makes the results accessible to all members of the working group. Both inventory and laboratory journal allow the data to be shared with other working groups for the purpose of collaboration.

The goals of Open Enventory are:

• to make unnecessary and boring tasks in the laboratory obsolete: searching for chemicals, price surveys.

• to make knowledge accessible inside and outside (if access is granted) the workgroup, for longer periods.

• to improve working safety be easily accessible safety instructions.

• to reduce waste amounts and unnecessary costs by an easily searchable inventory database.

Currently, Open Enventory can be used in German, English, French, Spanish, Italian, and Portuguese. Additional language files and modifications can be created with little effort.

• Sources:

Why would you want to use Open Enventory?

Please see the following presentation for a brief intro into Open Enventory, why you would want to use it and some of its functions:

• Click the link below to download the presentation:

Screenshots

Demo page

Contribution

This guide will be updated periodically.

If you have some documents or guide or videos and want to contribute and do not want to make the page yourself, please feel free to send it over to KV using the contact page:

Ideas, guides, comments are welcomed.

For the original Open Enventory:

Create a database

1. Go to your Open Enventory Login page on your web browser

2. On the Login page, fill in the following info: - Database: name for the new database - Username: root - Password: your mysql root password

3. Open Enventory will then create a new empty database

Connect databases

This allows for users in one database to see chemicals in another database and/or vice versa.

1. Go to your Open Enventory Login page on your web browser

2. Log into your database of interest using root as Username and your mysql root password as Password

3. Go to Settings on Top menus:

4. On the left side menu, choose Manage databases :

5. There are two types of connections:

• 2-way connections: users in one database can see/search chemicals in another database AND vice versa

• 1-way connection: users in one database can see/search chemicals in another database BUT NOT vice versa)

choose the desired connection and follow the instruction below.

Done! You can now search chemicals in other database(s) in the search page

For chemicals from existing database

Step-by-step instruction here

For chemicals from suppliers

Step 1: change to supplier search and search chemical

Step 2: choose Create new molecule based on data

Step 3: add more info: amount, person responsible, etc.

This clip below is show the process of adding a new chemical from suppliers:

Add a single chemical from scratch

Step 1: choose New

Step 2: use CAS number (if exists) to pull info from suppliers

Step 3: add more info

Step 1:

Login into OE and extract the list of chemical containers that need to be move. You don't need all of the details for each container, just: - CAS number - Names - Chemical container barcode: most important thing as this is the lookup point

Step 2

Open and add a new column with the name of the new location for each container. New LOCATION NAME MUST BE EXACT or OE would create a new storage location.

Step 3

Login into OE with an admin account if you have not done so in step 1

Step 4

Go to Settings > Import and Edit via text file

Step 5

If you want to change all chemical container into the same new storage, simply typing the EXACT STORAGE NAME in the input instead of choosing the column for the storage_name

Step-wise guide

1. Prepare the Excel file, an example might look similar as below. A few important things:

   • Columns that are highlight in blue are important info.

   • You don't need all of the info that show in this example.

   • Empty cells are OK.

   • The amount need to be “xx Y” with xx is the amount and Y is the unit.

   • For unit, it is best to have all in lower case, for example, “ML” should be either “ml” or “mL”. It is best to convert all unit to lower case (use lower() command in excel).

   • Double check the CAS number column, Open Enventory uses CAS number as primary source to look up chemical info from the internet.

   • See step 9 for more info about each column in the excel sheet.

2. Login into your database using root user or any users with admin permission

3. Go to Settings

4. Go to Import Tab-separated text file (or Import in KV's version)

5. Choose Browse… and choose the exported text file.

6. Click green check mark

7. Screens as below should show up, you can scroll down to see if they show up correctly (info appear correctly in each row)

8. Choose Column X for each content. If you choose None, that column will not be imported. You can also add a default value for each column that will apply for ALL items being imported.

9. Click green check mark. Screen similar as below should show. Note: line X:… will tell you which lines in the text file is being imported.

10. Depend of how many chemical containers are imported, this might take a long time, so please be patient.

Demo video

Details

The idea is each group should have one ‘user’ account designated for external users (users from outside of the group, guests). When someone outside of the group comes and borrows chemical containers, this account barcode should be scanned and following by the container barcode. This specific account (made by turning on a specific permission setting External borrow in Predefined permission) is set so that during checking out of a chemical container by this account, a pop-up window would open and ask for the specific info (borrower’s name, group, contact info, and the group member assisting during checkout). This info is then saved into the history entry of that exact checkout event.

Guide

Step 1: Create a new user, ‘guests_<database_name>’ for example.

• While creating this user, choose the following ‘External borrow’ in the ‘Predefined permissions’

Step 2: Use this new account barcode in the OE barcode terminal. During checkout, a popup window like this should open to ask for more info:

The provided info will be appended to the history entry for that particular checking out event. Example:

For traditional LAMP stack

This guide is for Linux environment with Apache2, PHP and MariaDB (MySQL) (LAMP) stack:

Save or Backup Database(s)

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

For users info:

1. In Terminal window, type the following command:

2. Type in password for the user above and then Enter (nothing will show when you are typing the password)

Restore

Step 1: transfer both files (database and users, if you want to keep the same users and passwords) to the new system/server

Step 3 (optional):

Step 4: use ssh and terminal, login as terminal root and run:

Step 5: go to OE on web browser and login with mysql root account.

Step 6: go to Settings and then click on Recreate users. That should reactivate all users and their passwords.

For XAMPP users

While the general steps are similar, the exact paths to the executable command are different for Mac OS and Windows using XAMPP, please follow the corresponding guides below:

Step 1: Choose Settings

Step 2: Choose Users

Step 5: change desired info or permission

Create a storage location

Step 1: After logging into Open Enventory, click on Settings on the top bar navigation.

Step 2: click on Storages on the Left.

Create a list of storage

Modify a single storage's information, permissions

Modify info for a list of storage

Merge two storage locations

Delete a storage

User Permissions

Create a New User

Create multiple users

Change user password

Modify user's permissions, information

Delete a user

Inactivate a user

Change user password using an admin account

Step 1: Choose Settings

Step 2: Choose Users

Step 5: type in the new password and repeat

Change one's own password

First, open up Open Enventory in any web browsers. Your screen should look as follows.

After logging in you should see the general window for searching chemicals.

Click on the “Settings” tab top and center of the page (outlined with a red box below).

This will change the left side bar. At the top of the left side bar you should see “Change password” Click here (the button is outlined with a red box below).

Your screen should have two boxes as shown below. Fill in both boxes press the “Change password” button right next to the text fields and your password has been changed.

In Inventory Mode

Borrow a chemical

Step 1: search for the chemical of interest (see Search chemicals section)

Step 2: click Borrow button

Return a chemical

Step 1: go to Borrowed chemicals

Step 2: click Return button

In Terminal Mode

Borrow a chemical for users of that database

Step 1: scan your own barcode

Step 2: scan chemical barcode

Borrow a chemical for guest users

Step 1: Follow the following guide to create a guest account

Return a chemical

Action: scan borrowed chemical barcode

A brief tutorial of creating a new laboratory notebook and creating a reaction entry.

1. Create new notebook.

   If you do not see the + button ask your admin to give you administrator journal privileges.

2. Generate reaction from scratch.

3. Generate reaction by copying existing reaction.

4. Add a new empty notebook page

These are not hard-setting but might be adapted to specific usage. All pages in this section are for guiding purpose.

SETTING FOR BARCODE TERMINAL USER

1. To have a barcode terminal client (stand-alone computer), create a user with permission as below and log in into OE using this user.

To create a new user, please see the following link:

Go to an active Terminal on your Open Enventory page. It should look similar to the following page.

Moving one single chemical

1. Scan user barcode (will say you are logged in)

2. Scan chemical container (will now show chemical information)

3. Scan new location barcode (will now say “updated and location field will show new location”)

Moving multiple containers to the same location

1. Check the 2 boxes “Stock-keeping mode” and “Set storage for all following containers” (the “Set storage for all following containers” will show up after you check “Stock-keeping mode”). Note: you can check by mouse or use the “Barcode for stock-keeping mode” list from Settings menu.

2. In typical terminal window of database of interest. Login by scanning user barcode

3. Scan the 1st chemical containers

4. Scan the new location. The window will show the new location and say the 1st container “… updated”

5. Scan the 2nd containers, 3rd container, 4th container, etc.

6. When Done, uncheck “Set storage for all following containers”

All of the guides in this section require you to be in an active Terminal. Please contact your site admin for specific details on how to access a terminal page on Open Enventory.

A terminal site should look similar to this:

This is for this scanner:

1. For OE original barcode, add Prefix ESC

2. For existing barcode, add Prefix: [l_shift_on]+[Home]+[l_shift_off]

Step 2: Copy/extract Open Enventory files into C://XAMPP/htdocs/ . Write down the folder name because this will be the web address. In this case the folder is named ‘enventory’

Step 3: Start XAMPP and start Apache and MySQL

Step 5: Change php.ini content by clicking on Config button on the same row with Apache on XAMPP panel to match the following:

• error_reporting = E_ALL & ~E_WARNING & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED (otherwise some warnings being displayed will break the functionality)

• safe_mode=Off

• magic_quotes_gpc=Off

• upload_max_filesize=64M # (or higher)

• post_max_size=64M # (or higher)

If still warnings appear in the generated HTML code (breaking open enventory), you should set:

• display_errors=Off # (especially on PHP 7+)

Step 6: Change my.cnf (or my.ini) content by clicking on Config button on the same row with MySQL on XAMPP panel to match the following under [mysqld] section:

Step 7: Restart both Apache and MySQL service by clicking on Stop and then Start for each service.

• "Database": <database-name> "database-name" can be whatever you choose but should not have blank space and unique; I suggest short name is better as well.

• "Username": root (It has to be the exact word "root" for this set up first)

• "Password": the root password that we set in the step above

• Then click on "Inventory".

Step 9: If successful, it will bring up something similar to this page:

This guide is created for new unmanaged VPS from A2hosting.com

• OS: CentOS 7

• Ram: 1GB

• Hard drive: 20 GB

• Bandwidth: 2 TB

INITIAL SETUP

1. Login into A2 account, service to find the following info:

• IP or hostname (if the domain name has been set up/registered)

• Ssh username

• SSH password

• SSH port

2. Connect to server using ssh. If you are on a Mac, use Terminal and type in the following command. If you are on a Window, you can use CMD (if CMD does not work, google “connecting to ssh on Window” to find the program

Select “Yes” if asked to continue connecting

3. To secure your ssh and your server, set up new user and deactivate root login via SSH by following steps 2, 3, and 5 in this tutorial:

Install sudo by:

5. Following this link to set up LAMP stack:

with:

In step 4: choose PHP7.2

Follow this tutorial from steps 1-5, make sure instruction for specific steps as indicated above

6. Install the following packages to prepare for Open enventory

9. The OE website should load like screenshot below

10. If successful, this page will load:

11. Modify .htaccess file

Modify the content of the file as in this screenshot. Important: these setting is for the server with configuration as indicated in the top of this tutorial

Save the file (hit Esc, type “:wq” and then Enter) and restart httpd service

12. Modify mysqld config:

Right after [mysqld], add:

Save the file (hit Esc, type “:wq” and then Enter) and restart httpd and mariadb services

• Quad Core 1.2GHz Broadcom BCM2837 64bit CPU

• 1GB RAM

Initial Setup

2. Once flashed insert micro SD into the RPi 3B and boot the device with monitor connected. If install completed you should now see the Raspbian desktop as shown below.

3. The first thing that should be done is to update the OS

• open a terminal window by clicking on terminal icon on top bar or by pressing ctl+alt+t

• run the following commands

• Complete the steps to set up the Apache server, install PHP, and install MYSQL mysql is outdated trying the command in the above link will give you an error use the following instead to setup the mariadb and mysql (NOTE: Wordpress does not need to be installed)

5. Install the following packages to prepare for Open enventory

6. Create MYSQL root pwd by entering the following command in terminal the default password is blank. We will subsequently set it up so sudo is not required so that we can login online to create our databases for oe. When asked for a password after command 4, use the password we just set up for mysql_secure_installation.

To find the ip address of your raspberry pi on your local network run the following command. The result will yield many things we only care about the ip address number directly following inet

going to servers address in a browser on a device on the local network will look like the following

9. If successful after clicking inventory you should see the following page

10. Modify .htaccess file

Modify the content of the file as in this screenshot. Important: these settings are for the server with configuration as indicated in the top of this tutorial

Save the file by typing ctrl+o then hitting the enter key then exit by typing ctrl+x. You will then have to tell the raspberry pi to use the settings in the .htaccess file. Navigate to 000-default.conf and add add the Directory lines so that the file is identical to the image below.

Then restart the apache server

13. Start the ngrok server

the output should look as follows (note if you close the terminal window the session will be closed

by navigating to the link provided by your output in a web browser with the directory oe for this example it would be http://2057a4d3.ngrok.io/oe should get you to the open enventory login page from any device on any network.

Installation

2. Copy/extract Open Enventory into Applications/XAMPP/htdocs/ . Write down the folder name because this will be the web address. In this case the folder is named ‘open_enventory’

3. Open Applications/XAMPP manager. Start MySQL and Apache.

4. Change mysql root password by following these steps:

1. Ensure that the MySQL/MariaDB server is running. (see step 3)

2. Open a new terminal.

3. Use the mysqladmin command-line utility to alter the MySQL/MariaDB password, using the following syntax: mysqladmin --user=root password "newpassword"

4. The mysqladmin command-line utility is located within the bin/ subdirectory of the XAMPP installation directory, typically /Applications/XAMPP.

6. Or, if a password has already been previously set and you’d like to change it to a new one, you can use the following syntax: mysqladmin --user=root --password=oldpassword password "newpassword"

7. For example, to change the root password from 12345 to gue55me, you would execute: /Applications/XAMPP/bin/mysqladmin --user=root --password=12345 password "gue55me"

5. Test that your password change has been accepted, by attempting to connect to the MySQL/MariaDB server using the mysqlcommand-line client in the same directory. For example, you could use the command below to connect to the server and return the results of a calculation: (make sure you use your own password instead of “gue55me”)

• In Database: put in the name for your database server

• Log in with username ‘root’ and set password Notice the error messages

9. Under Warning above notice the place of the temp folder (in this case /var/folders/87/…)

10. Go to that folder containing the openenv.log (in this case: T with the path as following in Finder (file maybe hidden): /var/folders/87/b98_ym4x1r91ldysmq8r29kc0000gn/T/

11. Set permission for everyone to Read&Write for this folder (e.g. “T”)

12. Look-up file php.ini. Should be in Applications/XAMPP/xamppfiles/etc/

And change to: error_reporting=E_ALL & ~E_NOTICE

13. Restart Apache and SQL in XAMPP manager

CHANGE TIME ZONE

1. Edit php.ini with TextEdit: Path: /Applications/XAMPP/xamppfiles/etc/php.ini Change: date.timezone=Europe/Berlin to: date.timezone=America/Chicago

2. Restart server

CHANGE LOGIN DEFAULT DATABASE TO NONE

1. Edit lib_global_funcs.php with TextEdit: Path: /Applications/XAMPP/xamppfiles/htdocs/open_enventory/ Change: if ($db_name=="") { $db_name="storage";

   to: if ($db_name=="") { $db_name="";

2. Restart server

SAVE/ BACK-UP and RESTORE DATABASE

See this page:

CHANGING TIMEOUT FOR WRONG PASSWORD

Edit file lib_global_setting.php: from

to

ADDING MORE OPTIONS FOR # OF RESULTS PER PAGE & DEFAULT CURRENCY TO USD

Edit file lib_global_setting.php

from

to

ADDING MORE MEMORY IN ORDER TO EXPORT (IN OE) TO EXCEL OF LARGE AMOUNT OF CHEMICALS

1. edit filephp.ini (in Linux, /etc/php.ini)

with xxx = desired memory in megabytes

2. edit file.htaccess (in the folder install oe)

with xxx = desired memory in megabytes

SETTING FOR BARCODE SCANNER

1. For OE original barcode, add Prefix ESC

TO FIX ISSUE WITH IMPORT TEXT-SEPARATED TEXT FILE NOT LOADING

• Chance is you miss php-mbstring and php-gd packages

• Install these packaged and restart apache (httpd in CentOS 7) should fix this issue

• In CentOS 7, you can install the two above packages by running :

• Restart your server (in Centos 7):

TO KEEP THE CURSOR ALWAYS IN THE FIRST TEXTBOX IN BARCODE TERMINAL FUNCTION

1. Go to OE folder, change …/lib/barcode_terminal.js, line 174-185, content:

From

To:

CHANGE LOGIN DEFAULT DATABASE TO NONE

1. Edit lib_global_funcs.php

Change:

to:

2. Restart server

TO AUTO FOCUS ON THE DATABASE TEXT BOX ON THE LOGIN PAGE

1. Edit lib_global_funcs.php by adding autofocus after id=\"db_name\". Around line ~1180

Original:

to:

UPGRADING OE

1. Backup current OE files

3. Unzip the file

4. Copy the content of the folder into your current [OE_folder] and overwrite the old files

5. Copy the “List of files that have been modified to specific need (different than the original files of oe)” from the backed up OE to the new [OE_folder]

6. Restart httpd and mariadb (or mysqld) services

7. Go to web browser and access OE url

8. Login as root into a database of interest and perform upgrade if necessary (you will have to do this again for all of the databases if major upgrade is required

MULTIPLE LABEL STYLES

2. Name these files as chemical_storage1.label, chemical_storage2.label, etc.

3. Save these files in the same location: /oe/forms/dymo/

4. Edit file edit.js (in /oe/lib/)

From:

To:

Pic:

Original file:

Modified file:

2. Restart the apache service: in CentOS7 use this in the command line:

HAVING THE “PRINT DYMO LABEL” BUTTON ALWAYS SHOW UP REGARDLESS OF DYMO PLUGINS INTALLED

1. Edit file lib_edit.php (in /oe/)

From:

Pic:

To (remove style=\"display:none\")

2. Restart the http/apache service

CHANGING LOCATION OF MULTIPLE CONTAINERS

3. Choose the Database in which the containers belong to:

For example in this case, I choose Database storage

4. Create new storage location in OE as usual, see:

5. Identify the storage_id of the new and old locations by choosing Table storage in Database “xyz”.

Another way of doing this is to use “Console” function in phpMyAdmin

Type in:

You should see similar to:

With the screen above, you should be able to see the storage_name and the corresponding storage_id

The next few steps is demonstration for changing all of the chemical in “315-R (Personal)” (storage_id=1) into “305-test” (storage_id=9)

In the Console:

6. To check how many chemical, type:

7. To change, type:

Done

CHANGING INFO OF MULTIPLE CHEMICALS

Install phpMyAdmin (See above)

To return all chemicals borrow by somebody (borrowed_by_person_id)

1. Look up person_id by going into [Database]/person

2. Show chemical borrow by that person (i.e. a person with person_id=8)

3. Return by:

To change the owner info of a chemical (owner_person_id)

1. Look up person_id by going into [Database]/person

2. Show chemical owned by that person (i.e. a person with person_id=7)

3. Change, in this case, to no owner (NULL)

Find person_id using Console command line in PHPMyAdmin

In the following example, show person_id of user with username=julia

Find storage ID using Console Command line in PHPMyAdmin

1. First choose the desired database on the tree folder from the left

2. Use:

Explain:

• LIKE 'FH-2206%' : show the storage_id of the storage location with name starting with FH-2206

• LIKE '%FH-2206%' would mean find anything with storage name containing FH-2206

• LIKE 'FH-2206%' would mean find anything with storage name ending with FH-2206

Show chemicals with multiple criteria

Explain: the above command is used to

• Select all of the chemical in the database that match: owner_person_id=15 AND with storage_id is NOT 18

Change Responsible person for chemicals with multiple criteria

Explain: see above for explanation of the criteria.

Show storage_name and storage_id using username

Explain:

GROUP BY storage_name : is added so that duplicates are only shown once

Update responsible person for chemicals in many location (storage)

Explain: the command above change responsible people to no one for all of the chemicals being owned by person_id=13 AND in all of the storage_id listed

Search for barcodes that are duplicated

The below command will search for all the barcode that is assigned to more than 1 container (chemical_storage). This search ignores disposed container as well as container without barcodes (chemical_storage_disabled IS NULL AND chemical_storage_barcode != "")

Update/Get extra info for containers with duplicated barcode (continued from “Search for barcodes that are duplicated”)

To select info from the list of duplicated barcodes. In this case, created by a specific user and created after some specific timestamp

REMOVE ALL CONTAINERS (BOTTLES) OF CHEMICALS

This will remove all of the containers, including info about manufactures, order date, barcode, owner, location, etc. However, info about molecule (MW, structures, safety, etc) and database related such as users will be reserved.

This is particularly helpful when you need to update a large amount of chemicals via text import.

1. Login into your server via ssh

2. Login into myswl using root account or user account with administrator priviledge for the database that you want to change. Replace “root” with the username if desired

3. Show database and then choose the database of interest. test is the name of the database in this example

4. Remove the chemical container by running the following command

5. (Optional): Run the command below if you want to reset the id counter (chemical_storage_id as well as autogenerated barcode)

6. Log out of mysql, restart httpd and mariadb services

7. Go back to OE website and import new tab-separated text file

SETTINGS FOR OE TO USE WITH EXISTING CONTAINER BARCODES

If you have existing barcodes for your chemical containers, use the following settings

1. Login into OE with the database that you want to modify, using an admin account, (or root account, or an account with sufficient permission)

2. Navigate to Settings/Global Settings/Inventory

Important: when switching to this setting, you have to generate your own storage(locations) barcodes as well as user barcodes. See below for way to automatically generate location and user barcodes.

TO FIX EXISTING BARCODE IMPORTED ISSUE: Removing trailing “\n” in existing barcodes

Issue: if the existing barcode column is the last column in the tab-separated text file, when imported into OE, the “\n” will also be imported as part of the barcode. This lead to terminal window does not work with existing barcode. Use the following methods to remove this trailing “\n” character

1. Login into your server via ssh

2. Login into mysql using root account or user account with administrator priviledge for the database that you want to change. Replace “root” with the username if desired

3. Show database and then choose the database of interest. “test” is the name of the database in this example:

4. Remove the chemical container by running the following command

5. Log out of mysql, restart httpd and mariadb services

TO AUTOMATICALLY ADD STORAGE BARCODE WHEN USING “USE EXISTING BARCODES”

Issue: when switching to using pre-existing barcode instead of using OE-generated barcodes, the system won’t work well if no existing barcode for storages and users are added. Use the code below in mysql to automatically add barcodes for storage using OE-generated code.

While these barcodes look the same, they have to be generated in order for terminal to work

1. Login into your server via ssh

2. Login into mysql using root account or user account with administrator privilege for the database that you want to change. Replace “root” with the username if desired

3. Show database and then choose the database of interest. “test” is the name of the database in this example:

4. You can check if the new generated barcode first by running this in phpMyAdmin (easier to see result). See explanation below for the complicated function. Compared the “new_barcode” with the autogenerated barcode to make sure the function work well.

Explain: default in OE, the barcode for storage is “92xxxxxy” with the last digit “y” (digit number 8 on 1-based) is the check sum of the first 7 digits. The “xxxxx” labeled in red is the “storage_id” left padded with “0” (number zero).

For example: if “storage_id”=1 => “storage_barcode”=92000018

1. Exit mysql:

TO AUTOMATICALLY ADD PERSON BARCODE WHEN USING “USE EXISTING BARCODES”

Issue: when switching to using pre-existing barcode instead of using OE-generated barcodes, the system won’t work well if no existing barcode for storages and users are added. Use the code below in mysql to automatically add barcodes for USER using OE-generated barcode.

While these barcodes look the same, they have to be generated in order for terminal to work

1. Login into your server via ssh

2. Login into mysql using root account or user account with administrator privilege for the database that you want to change. Replace “root” with the username if desired

3. Show database and then choose the database of interest. “test” is the name of the database in this example:

4. You can check if the new generated barcode first by running this in phpMyAdmin (easier to see result). See explanation below for the complicated function. Compared the “new_barcode” with the autogenerated barcode to make sure the function work well.

Explain: default in OE, the barcode for PERSON is “91xxxxxy” with the last digit “y” (digit number 8 on 1-based) is the check sum of the first 7 digits. The “xxxxx” labeled in red is the “person_id” left padded with “0” (number zero).

For example: if “person_id”=1 => “person_barcode”=91000019

6. Exit mysql:

SETTING TO ALLOW SEEING LOCATION OF CHEMICAL OUTSIDE YOUR OWN GROUP

In the default setting of OE, the exact location of a chemical belonging to a different group is masked. This setting will help you to allow some specific person or everyone to be able to see locations of chemicals outside of your own groups

1. Login into the server via ssh and then convert to root user or use sudo command. The following is using sudo command

2. It is a good idea to save an original copy of this file first. Modify the file lib_output.php inside open enventory folder. In this example, the open enventory folder is named “oe”. The specific location of your folder might change. Change the path accordingly.

3. Change the content of the file as follow. Note, this is on line 2584 so google to search for some term in vi; otherwise you will need to scroll a lot.

Original content:

To let EVERYONE see the locations:

To let a specific user to see the location, change the content as follow. In this example, the person name is “special_person”

UPLOADING EXISTING LOCATION BARCODES

Below guide is referenced from:

Prepare the excel file with one column as storage with the same name as those found in OE. Export to csv file, csv file does not have to have the same column but it is best to have the first 2 columns is the storage name and barcode

Use mysql command line. ( if you use phpMyAdmin console, see section below)

1. Login into mysql as root user using:

2. Show all databases and choose the correct database to modify (in this example “test” is the name of the database):

3. Create a temporary table (in this example temp_storage is the name of the database, storage and barcode are the 2 headlines of the 2 columns):

4. Load csv data file into temp_storage table (in this example test is the name of the database; “ignore 1 lines” because the csv file head 1 line headline):

5. Update storage table

6. Remove the temporary table

If you want to use phpMyAdmin,

1. Copy csv files into /var/lib/mysql

2. Open phpMyAdmin console.

3. Choose the right databases

4. Run ALL of these commands AT ONCE (Ctrl-Enter to run command(s) in phpMyAdmin console)

Goal: Setting for adding prefix Ctrl-A. This is to remove all pre-existing data inside an input field

Reference: https://www.honeywellaidc.com/en/-/media/en/files-public/technical-publications/multi-product/00-02544.pdf

Instruction: scan every single barcode on the first column in this order from top to bottom

Explanation:

• 1st Configurable Prefix = 175 : Left Crtl + 1 character

• 2nd Configurable Prefix = 065 : Letter “A”

• Scanner will transmit <left CRTL Make> “A” <Left CRTL Break>

This program is designed specifically for Open Enventory to fix issue with molecule missing sds (could not be extracted through "Read data from supplier") This programs does:

2020-09-26

• Add: option for borrowing of chemicals by users from outside of the group (guests)

  • Details: The idea is each group should have one ‘user’ account designated for external users (users from outside of the group, guests). When someone outside of the group comes and borrows a chemical containers, this account barcode should be scanned and following by the container barcode. This specific account (made by turning on a specific permission setting External borrow in Predefined permission) is set so that during checking out of a chemical container by this account, a pop-up window would open and ask for the specific info (borrower’s name, group, contact info, and the group member assisting during checkout). This info is then saved into the history entry of that exact checkout event.

  • To use this function:

    1. Create a new user, some thing like ‘guests_<database_name>’ for example.

    2. While creating this user, choose the following ‘External borrow’ in the ‘Predefined permissions’

3. Use this new account in the OE barcode terminal. During checkout, a popup window like this should open to ask for more info:

2020-09-24

• Add: Select2 javascript library to give users a customizable select box with support for searching, filtering. This change is for Inventory mode.

2020-09-10

• Change: allow username to be up to 32 character

2020-08-18

• Add yyyy-mm-dd date format for import chemicals from Excel file

• Change to import function to take user input name as the default name of the new molecule (if the molecule does not yet exist)

2020-08-03

• Merge with Felix v2020-07-27. Noticeable changes include:

  • 2020-07-27:

    • date format localized, depending on language of user

    • added missing suppliers, Sigma and Oakwood fixed

    • minor fixes

  • 2020-07-13:

    • and to upload lab notebook entries including spectra - much better than just supporting info

    • minor fixes

2020-06-19

• Add ability to sort by 'order_date' in 'chemical_storage' table

2020-06-06

• Add ChemDraw JS into drawing tools for both Inventory and ELN. To use it:

  • Turning on ChemDraw as your drawing tools by going into Settings > Personal settings > Molecule editing (for your own preference) or Settings > Global settings > Molecule editing (for your whole database setting) and change Structure drawing program and Structure drawing program for reactions.

2020-05-17

• Add function to extract currency when it is in front of the amount

• Merge with Felix Rudolphi Official Release of OE-2020-05-03

• Fix TCI scraping due to its new website

• Fix: buttons size in Lab Notebook side

• Fix: Ketcher window size in Inventory and ELN sides

• Fix: bug preventing showing of "Print DYMO label" button (Reported by Gaël De Leener, PhD, Université libre de Bruxelles, Belgium. Thanks, Gaël!)

• Fix "Reset" button in rxnSearch in ELN

• Fix wrong Tooltip for some "Reset" buttons

• Fix some English translation

• Fix PHP7 Warning: count()

• The following features are from Felix Rudolphi:

  • Fix: Ketcher 1.0 bug

  • Add: ChemDoodleWeb drawing 9.1.0 for both Inventory and ELN

  • Add: Ketcher 2.0 beta for both Inventory and ELN

2020-04-24

• Fix: typo cause PHP Warning "Illegal String offset"

• Fix: error in "Import and Edit" chemical containers where it rejects containers without CAS and name even if barcode existed.

2020-03-12:

• Fix for search by container barcode for customized barcode starts with '2'. Why: some users has reported in the case of customized chemical barcodes, for example '10001' or '11345' or '22069'. When searching using barcodes, all of those starts with '1' works fine but those starts with '2' don't return the correct result. This is because the automatically generated barcode by OE starts with '2' and in the code, all search query for barcode starts with '2' will be modified

• Add "Import and Edit" and "Import Only" options in the Settings menu for admin roles.

  • "Import and Edit" option is similar to previous version of Import in which it allows admin users to import: chemical containers ("packages"), storages list, user list, and supplier offers. For chemical containers ("packages"), this function will check if the database has the chemicals based on provided barcode. If the barcode is not found, it will add new container. If the barcode is found, it will change the provided info for that container.

  • "Import Only": only allow importing of chemical containers AND it will NOT check for existing container.

  • Right now, this function will only turned on for MIT and Baylor University.

  • To add your own institution, you need:

    1. Creating lib_customization.your-school-name.php with your-school-name is short or abbreviation of your school name. Use lib_customization.mit.php for an example

    2. Add the following line inside lib_customization.your-school-name.php after $default_g_settings["order_system"]="fundp";:
    3. Modify lib_global_settings.php by:

       • Change this: define("customization",""); // Customization to use: f.e.: ".sample" for use of "lib_customization.sample.php", and "" for "lib_customization.php"

       • To: define("customization",".your-school-name"); // Customization to use: f.e.: ".sample" for use of "lib_customization.sample.php", and "" for "lib_customization.php". Notice there is a period (.) in front of "your-school-name".

    4. Modify sidenav.php by:

       • Right before this line: showSideLink(array("url" => "import_edit.php","text" => s("import_edit_tab_sep"), "target" => "mainpage", ));. On this line: if (in_array($g_settings["customization"], array("baylor", "mit"), true)) { add "your-school-name" (the same as "your-school-name" set in lib_customization.your-school-name.php) right at the end of the array list of institutions. For example:

2020-02-01:

• Add support for importing/deleting from Excel files (both .xlsx and xls).

• Add support for importing/deleting from csv (comma-separated text) files. Previously, only tab-separated text files are supported

2019-11-22:

• Changed default criterion to "contains" instead of "is similar to" in Structure search

• Made sidenav width resizeable for user that use Bootstrap4

• Made sidenav width automatically expand in Structure search

• Change format message in Terminal Mode to be more visible

2019-09-30:

Added:

• Storage barcode and person barcode columns in their respective setting pages

• Option to shorten the criteria list in Simple search in Inventory

  (developed for Baylor University)

• Placeholder for input type date in edit mode with yyyy-mm-dd

• Function to delete multiple containers via import text

• User guides section

• Added 'liters' and 'liter' to the list of recognizing units when importing text file

Upgraded:

• Updated ChemDoodle to ChemDoodleWeb Component v8.0.0

• Upgraded Ketcher to v1.1-beta

Fixed:

• Let the cursor default to be in 'Database' input field on login page,

  fix for Firefox

• Fixed bug where date are deleted in edit mode

2019-07-29:

• Fixed Sigma-Aldrich cannot be accessed from A2 Hosting

• Fixed changed location inside normal OE window does not record in History text

• Added storage barcode for export functions

• Added user barcode for export functions

• Added show column for storage

• Added show column for user

• Fixed minor issue with "Disposed chemicals" list does not show correct view

• Applied changes from official OE version 2019-07-24

2019-07-17:

• Added date style to yyyy-mm-dd hh:mm:ss when display in OE so there is no confusion in date style

• Added a new login page with mobile responsive

• Modified sidenav, topnav to use Bootstrap4

• Added option for admin user to turn Bootstrap 4 option on/off in global_settings

2019-07-03:

• fixed bug in Terminal mode: barcodeTerminalAsync.php and lib_language_en.php

  • while doing inventory for a container (inventory mode or "Set storage for all following containers"), if you scan a non-existing barcode, the location will be removed. When a non-existent barcode is scanned, an error pop-up window appears.

• modified History log text to add storage_name; also added History log text

  • when changing storage in edit mode (lib_db_manip.php, lib_db_manip_edit.php)

2019-06-11:

• import.php, lib_import.php: added importing function for locations and users using tab-separated text file

• lib_import.php: fix for importing chemical_storage_barcode bug.

  • When import tab-dilimited text file of chemical containers, if the barcode column is the last column, it will add white space or \n character, making the barcodes inaccurate. The fix will trim all the white space \t\n on the right side of the input column

• topnav.php, style.css.php, lib_global_funcs.php, lib_sidenav_funcs.php sidenav.php: edited some fonts, styles

2019-06-04:

• lib_language_en.php, sidenav.php, barcode_autogeneration.php:

  • Creating option for admin user to auto generate all location and user barcodes while using "Existing barcodes" functions

2019-05-25:

• lib_db_manip.php: edit logging text to reflect chemical containers when

  being moved from one location to another

2019-05-23:

• multiple files: Fixed functions for php7 warning

• Fixed "Set storage for all following containers" in Terminal

• Added barcode Type 128 generation for user using existing barcode

• import.php, lib_import.php: Fixed added order_date and open_date in Import tab-separated text file function

CHANGE TIME ZONE

1. Edit php.ini file (/Applications/XAMPP/xamppfiles/etc/php.ini if you use XAMPP in MacOS)

Change:

to:

2. Restart server

SETTING UP HTTPS (SECURED HTTP) FOR OE (RECOMMENDED)

This is optional but recommend for any site that requires user login

This specifically apply to:

• CentOS 7

2. Create /etc/httpd/conf.d/oe.conf (same name as the folder contain Open Enventory, in this case /var/www/html/oe)

In Terminal, type:

Add the content below:

2. Restart apache service, in Terminal:

SETTING UP CRONTAB TO AUTOMATICALLY BACKUP YOUR DATABASE

This is optional but recommend for auto back up the OE database as well as the user’s info and password

This specifically apply to: CentOS 7 (Apache version: Apache/2.4.6 (CentOS) OpenSSL/1.0.1e-fips PHP/5.5.25 PHP version: 5.5.25 MySQL server version: 5.5.41-MariaDB)

2. Go into Terminal in your server and log in as root user (type: “su” and then password at the command line

Note: you can do the rest of the command lines below with prefix “sudo your-command-here” if you don’t want to log in as root

3. At the command line, type:

4. When the file is open, insert the following. Note: everything is red can be changed to match specific details. -$(date +\%Y\%m\%d): is used to set automatic date

5. More example of crontab can be read here:

OPTIMIZING CENTOS 7 PERFORMANCE

IP BLOCKING FOR SECURITY ENHANCEMENT ON CENTOS 7 SERVER

This guide to use access_log to find out some high frequent access ip and then check them manually and form a block ip if necessary

Find IP address with the most access

2. Specifically, ssh into the server and then log in as root user

3. Use following command:

change “-20” to “-xx” with “xx” is the number of result. This is sort from most to least access request

Setting up rule to block ip network.

Finding info about the network list

2. Specifically using this command (after ssh into the server and then log in as root)

To list the IP sets known to firewalld in the permanent environment, use the following command as root:

3. To get more information about the IP set, use the following command as root: (in the example below, “test” is the name of ipsets you got from the above command

Or:

To see the extended entries list of the IP set, use the following command as root:

To block IP from a whole country

1. Follow this guide:

FREEING DISK SPACE IN CENTOS 7

See these references:

ADVANCE FILE AND FOLDER PERMISSION/OWNERSHIP SETTING IN CENTOS 7

See the following resources:

Requirements

Overview

"Import and Edit" option is similar to previous version of Import in which it allows admin users to import: chemical containers ("packages"), storages list, user list, and supplier offers. For chemical containers ("packages"), this function will check if the database has the chemicals based on provided barcode. If the barcode is not found, it will add new container. If the barcode is found, it will change the provided info for that container.

"Import Only": only allow importing of chemical containers AND it will NOT check for existing container.

Right now, this function will only turned on for MIT and Baylor University.

Instruction to turn on this function

• To add your own institution, you need:

  1. Creating lib_customization.your-school-name.php with your-school-name is short or abbreviation of your school name. Use lib_customization.mit.php for an example

  2. Add the following line inside lib_customization.your-school-name.php after $default_g_settings["order_system"]="fundp";:

     Copy

      /* Khoi: add customization identifier so that codes specific for your-school-name will be execute. Only change if you know what you are doing */
      $default_g_settings["customization"]="your-school-name";

  3. Modify lib_global_settings.php by:

     • Change this: define("customization",""); // Customization to use: f.e.: ".sample" for use of "lib_customization.sample.php", and "" for "lib_customization.php"

     • To: define("customization",".your-school-name"); // Customization to use: f.e.: ".sample" for use of "lib_customization.sample.php", and "" for "lib_customization.php". Notice there is a period (.) in front of "your-school-name".

  4. Modify sidenav.php by:

     • Right before this line: showSideLink(array("url" => "import_edit.php","text" => s("import_edit_tab_sep"), "target" => "mainpage", ));. On this line: if (in_array($g_settings["customization"], array("baylor", "mit"), true)) { add "your-school-name" (the same as "your-school-name" set in lib_customization.your-school-name.php) right at the end of the array list of institutions. For example:

       Copy

       if (in_array($g_settings["customization"], array("baylor", "mit", "your-school-name", ), true)) {

This program is designed specifically for Open Enventory to fix issue with molecules missing structures (could not be extracted through "Read data from supplier")

Overview

You can quickly have a container (virtual machine) up and running Centos 7 and LAMP stack by using this Docker Image, see link below to download the docker image and quick instruction. If you have more questions, please feel free to contact KV.

Details

In order to use this image, you need to:

1. Install Docker Community Edition (CE):

2. Go to this link and get the download link

OR download the docker image:

docker pull khoivan88/centos7-lamp_for_oe

3. Follow the instruction in the Docker hub link above to get the container running

4. Download Open Enventory and put the content inside html folder inside the project folder created by following the instruction of the Docker image.

While the quick user backup as this one-line commandmysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql might work when you have same version for backup and restore SQL. In most cases, you want to implement something similar to the following bash script