1 of 40

KV.2019-12-01

Overviews

Why would you want to use Open Enventory?

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.

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

Link to online presentation.
Click the link below to download the presentation:

Demo page

Official demo site for: - Chemical inventory - Electronic Lab notebook (ELN)

Contribution

This guide will be updated periodically.

If you want to contribute, you can create an account to generate or edit this guide. Please follow this link.

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:

Contact

You can contact KV at this email: knv88@pm.me

Ideas, guides, comments are welcomed.

User Guides

Chemical Inventory

Database

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.

Note: in the default setting, OE only allows user in one database to see if other databases have chemical(s) but not revealing the chemical's location.

If you want to let users in one database to be able to see location of chemicals in another database, after connecting the databases, please follow the instruction in this link.

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.

Step 1: Choose as databases that you want to connect (hold Ctrl while choosing). You can choose more than 2. Click select all if you want to connect all databases

Step 2: Click the button Connect selected databases

Step 3: Click the green check mark ✅

Using the rubric table, the databases with name in the bold format and listed horizontally in the first row of the table will be able to see/search chemical in database(s) (list vertically in the first column) that has checked check box

For example: In the picture below, users in romo database will be able to see/search chemicals in romo_test database BUT users in romo_test database will NOT be able to see/search any chemicals in romo database.

Step 1: using the info above, make the connection of interest by checking the check boxes

Step 2: Click the green check mark ✅

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

Database Backup and Restore

This is procedure to backup Open Enventory database and restore it later on the same server or migrating to a different server

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:

mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

If you want to compress the SQL backup files, you can use this command instead:

mysqldump -u [user] -p [database] | xz > [Path]/[backup_filename].sql.xz

| xz was added for compressing SQL file to .xz file
Make sure that you have .xz at the end of the file as above.

.xz files seem to have the best compression vs bzip2 and gzip for SQL files. You can see more info here and here:

For example usage of xz, see this link.

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:

mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended)
[Path] and [backup_filename] : any path and filename

While this approach might work well in case of same MySQL or MariaDB version, if you migrate or upgrade SQL, you want to follow the instruction here for better users backup:

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 2: create the database from OE web login if you have not done so.

Step 3 (optional):

If you have compressed the SQL file as instruction above, you will need to decompress the file first.

This is for compressed file with .xz extension:

unxz -vk [/path/backup-database].sql.xz

-v : verbose, give more info
k : to keep the compressed file, this will generated the uncompressed file .sql

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

mysql -u root -p [database-name] < [/path/backup-database].sql    #for database restore
mysql -u root -p mysql < [/path/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/backup-database] and [/path/backup-user]: any path and filename

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

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

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

Again, if you have migrate from MariaDB < 10.4 to MariaDB 10.4+, it might not work and we have to change the command in step 3 above. The reason is because the mysql.user table is deprecated in MariaDB 10.4. You can read more about it here.

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:

Save or Backup Database

For chemicals and lab journal databases:

1. In Terminal window, type the following command:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

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:

/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

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 2: create the database from OE web login if you have not done so.

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

/Applications/XAMPP/xamppfiles/bin/mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
/Applications/XAMPP/xamppfiles/bin/mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

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

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

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

Save or Backup Database

For chemicals and lab journal databases:

1. Open a Window Powershell window, type the following command:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info.

[user]: in most cases, use root (recommended).
[database]: use the name of the database that you want to backup
[Path] and [backup_filename] : any path and filename

In the command above, this part is for the default XAMPP set up. If some error happens, you should find the executable file inside XAMPP folder in your system. You can try to follow this link.

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:

c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql

Replace [italic_text] (including the square bracket) with specific info:

[user]: in most cases, use root (recommended).
[Path] and [user_backup_filename] : any path and filename

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 2: create the database from OE web login if you have not done so.

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

c:\Programs\XAMPP\mysql\bin\mysql -u root -p [database-name] < [/path/to/backup-database].sql    #for database restore
c:\Programs\XAMPP\mysql\bin\mysql -u root -p mysql < [/path/to/backup-user].sql    #for users restore, this might not work for mariabd 10.4+

Replace [italic_text] (including the square bracket) with specific info:

[database-name]: use the name of the database that you want to backup
[/path/to/backup-database]: any path and filename

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

If the mysql root user in the old database has password different than the current mysql root user, after step 3 above, the root user password will be changed to the old password.

If you don't know the old mysql root password, follow this tutorial to reset mysql root password.

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

Best Practice for SQL Users Backup

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

Backup Users

Step 1: On your server, in terminal, prepare the following bash file using vi (or vim or any text-editor of choice)

vim mariadb_user_backup.sh

Step 2: Paste in the content of the file as follow:

mariadb_user_backup.sh

#!/bin/sh

# This bash script is used to save users and permission from mysql database.
# Ref: https://stackoverflow.com/a/57878326
# explanation: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/ 

HOSTNAME="localhost"
logininfo="-u root -pPASSWORD"
outfile="/path/oe_backup-user-$(date +\%Y\%m\%d).sql"

# The line below will save all users except 'root', empty name and specified ones
mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root' AND user != '' AND user not like 'auto%'" mysql | \
while read uh
do
   echo "SHOW GRANTS FOR ${uh};"
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}

PASSWORD : change to your root password or the password of the user that you use for the backup process. If you don't use root then change root on line 8 to your user
path : use your specified path to the output file
oe_backup-user-$(date +\%Y\%m\%d).sql : the output file will have the current date concatenated at the end of the file
This script above also not save oldroot user info or empty users. You can change it by modify the code on line 12

Step 3: make sure you (or the user that run the mariadb_user_backup.sh file has the sufficient permission (i.e. chown) and make the script file executable:

chmod u+x mariadb_user_backup.sh

Step 4: Execute the script file to obtain the user backup file

./mariadb_user_backup.sh

Restore Users

Similar to the guide here.

If your new SQL server is newer than your old one, after importing both database(s) and users, you can run the following command in terminal to upgrade MySQL:

mysql_upgrade    # Upgrade MySQL data
systemctl restart mariadb   # Restart mariadb

Chemicals

Search Chemicals

Searching Chemicals in Existing Database(s)

Make sure “Chemical Search” mode is on

Step 1: choose what type of search: e.g “names, cas, supplier #”

Step 2: choose options: e.g. all words, contain (~ similar to), exact

Step 3: type is your search query, this can be name, CAS, supplier #, best with CAS and name

Step 4: choose database(s): choose one, or several (holding Ctrl while Left-click, or All databases (default)

Step 5: click Search

Video demonstration:

Searching Chemicals in Supplier Mode

Note: when Suppliers search mode is on (see add chemical via suppliers), Open Enventory search includes chemicals from the local database(s) first

Search Chemicals using Structure

The default drawing program in Sciformation Vectormol. You can choose a different program by going to: Settings/Settings/Molecule editing/ and change “Structure Drawing Program” to “ChemDoodle (Javascript)” (recommended).

Step 1: choose search with Structures

Step 2: draw structure

Step 3: choose options: contain, similar to, exact, etc.

Step 4: click search

If it appears that the search is not accurate in ChemDraw plugin, you have to active the ChemDraw plugin: a) Right click on the background of the chemdraw drawing window b) Help/Activate my ChemDraw Plugin c) Input your activation info the same (from email) as what you did for installation of Chemdraw

Add a single chemical

Add Storage location if not exists: see this section

For chemicals from existing database

TODO

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:

For custom chemicals or chemical that cannot be found from suppliers list, use Method below.

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

Import a list of chemicals

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.
- ~~Location~~ ~~column: remove all commas ( , )~~ ~~if there is any. Dashes ( - ) are ok~~. Update (2020-02-01): for KV's version of OE, if you updated to version after 2020-02-01, this has been fixed.

Update (2020-02-01): for KV's version of OE, if you updated to version after 2020-02-01, steps 2 and 3 below are not necessary any more. KV's version OE (on and after 2020-02-01) now accepts Excel files (*.xlsx and *.xls), comma-separated files (csv) tab-separated files (*.txt). You can find the guide for newer version here.

2. Copy all of the cells in this file (Ctrl-C for Windows, Cmd-C for Mac) and paste (Ctrl-V for Windows, Cmd-V for Mac) them into Notepad (Windows) or TextEdit (Mac)

3. Save this new file as your-file-name.txt. The “.txt” part is important.

Alternatively, instead of step 2 or 3, you can do the following. (Note: in some cases with some chemicals names, Excel has shown to add quotation marks (“”) around the name when exporting to text file. That is the reason why step 2&3 above is preferred.

Save As… the file using Window Formatted Text (for Mac OS)

For Windows, Office 2016 of Office 365, use “Text (Tab delimited) (*.txt)” option:

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

5. Go to Settings

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

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

Note: in Lines to skip, put 1 if you have a header rows, if not, choose 0

8. Click green check mark

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

10. 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.

Notes:

CAS number: most important info, OE will use this to look up other info (structures, MW, safety data, etc) from this cas #
Name: not too important
Empirical formula: do not import this since OE will use this to find the closest chemicals if CAS# is not present or wrong.
Amount: = size of the bottle + unit (format should be #+space+unit, for example: 5 g, 125 ml, 1 kg, etc)
Still available: the amount that left in the bottles (OK if not imported)
Locations: important. Note: Each location will have its own barcode
Compartments: = sub-location. Note: each compartment does NOT have its own barcode
Barcode: if you already have an existing barcode system, add it here.
Suppliers: = suppliers
Date open: could be in European style (dd.mm.yyyy) or American style (mm/dd/yy, etc.)

11. 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.

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

Edit Chemicals

Step 1: Search chemical in the database:

Search Chemicals

Step 2: Click on the structure

Step 3: Depends on the type of info you want to edit, choose the corresponding tab below:

Click on ‘Go to molecule’ button to edit structure and structural info (MW, Formula, etc.)

To change the lab related (person responsible, amount, etc.) info, double click on any where around these info

Borrow and Return Chemicals

In Inventory Mode

Borrow a chemical

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

Search Chemicals

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

❗Computer that connected to the scanner must be ON ❗ Barcode terminal window must be ON and ACTIVE, if not log in with terminal username and pass or contact your local admin ❗ Active barcode terminal look like below:

Step 1: scan your own barcode

Step 2: scan chemical barcode

Watch the terminal window to make sure the info is transmitted/recorded.

Return a chemical

❗ Computer that connected to the scanner must be ON !!! Barcode terminal window must be ON and ACTIVE

Action: scan borrowed chemical barcode

Note: you don’t need to scan the your own barcode beforehand. The scan on a borrowed chemical will return the chemical.

Note: if the chemical is free and nobody has logged in, scanning the barcode of that chemical will bring up that chemical’s info in the system.

Delete a chemical container

TO DO: to be constructed

Delete multiple chemical containers

This function only available in KV's version.

TO DO: to be constructed

Users

User Permissions

Create a user

Do NOT create a user with username starting with "auto". Any user with this kind of username will be deleted when linking databases.

Change user password

Change user password using an admin account

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.

Create a list of users

Modify a user's information, permissions

Delete a user

Inactivate a user

Storages/Locations

Create a storage location

The user must have Storage Administrator permission in order to create new storage

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

Step 2: click on Storages on the Left.

Step 3: click on the ➕ button

Step 4: after adding the info, click the checkmark ( ✅ ) button

Create a list of storage

TO DO: to be constructed

Modify a single storage's information, permissions

TO DO: to be constructed

Modify info for a list of storage

TO DO: to be constructed

Merge two storage locations

TODO: add line-by-line instruction

Delete a storage

TO DO: to be constructed

Terminal Mode

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:

If you are an admin and need to set up a Terminal, please see the following page:

Set Up a Terminal User

SETTING FOR BARCODE TERMINAL USER

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

You can save link to this user as a bookmark for easy access, but less secure since the password is embedded in the address as below:

http://address/index.php?db_name=databasename&user=username&password=password&desired_action=login&lang=en&loginTarget=barcode_terminal

The following should be changed to your own setting: - adress: your Open Enventory web address - databasename - username - password

Keep in mind that the password for Terminal user will be exposed in your bookmark link above

Borrow and Return Chemicals

See Borrow and Return Chemicals in Terminal Mode

Change Chemical Location

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

Moving one single chemical

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

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”

Get Information on a Chemical

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

Without any user logging in, scanning a chemical container barcode will bring up that chemical info.

Dispose of Chemicals

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

Scan user barcode (will say you are logged in)
Scan chemical container (will now show chemical information)
Scan barcode for “Delete” (will say “data set will be deleted in 3 seconds, then deleted)

Notes: If you manually change data (not using the scanner) you will have to click “update” after changing the data manually (mouse and keyboard).

TODO

To be added to this tutorial

• How to add chemical that is already in the database

• How to add MSDS sheet

• How to delete chemicals (3 methods: - normal log-in - barcode scanner - Mass deletion (admin account only)

• How to print chemical barcode

• How to change location (and other info) of many chemicals using barcode terminal

Electronic Lab Notebook

Sorry we do not have any content for this yet. If you have any guides you would like to share, please contact KV using the following link:

Contact

Installation

Raspbian on Raspberry pi 3B

How to install open enventory on Raspbian

This guide is to create a working Open enventory running on OS Raspbian on a Raspberry pi 3B (see https://www.raspberrypi.org/products/raspberry-pi-3-model-b/ for information on the RPi 3B)

Quad Core 1.2GHz Broadcom BCM2837 64bit CPU
1GB RAM
Using SanDisk Ultra 64GB UHS-I/Class 10 Micro SDXC Memory Card as boot and home drive

Initial Setup

Flash a fresh copy of Raspbian onto the micro SD card. (A guide for completing this task can be found here https://www.raspberrypi.org/documentation/installation/installing-images/.

Raspbian image found at https://www.raspberrypi.org/downloads/raspbian/ We used "Raspbian Buster with desktop and recommended software" zip file
use zip file with balenaetcher to flash to micro SD

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

sudo apt-get update
sudo apt-get upgrade

4. After the update has completed we will create a LAMP server on the pi. This was completed following this guide (https://projects.raspberrypi.org/en/projects/lamp-web-server-with-wordpress)

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)

 sudo apt-get install mariadb-server-10.0

5. Install the following packages to prepare for Open enventory

sudo apt-get install -y php-mysql php-gd php-mbstring php-pear ghostscript imagemagick
sudo service apache2 restart

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.

sudo mysql_secure_installation 
sudo service apache2 restart
sudo service mariadb restart
sudo mysql -u root -p
MariaDB [(none)]> USE mysql;
MariaDB [mysql]> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> exit;
sudo service mariadb restart

7. Download Open Enventory image and set up on apache server (exact link to download can be found at https://sourceforge.net/projects/enventory/files/?source=navbar) (NOTE: in terminal commands below, the open_enventory_2019-07-24.zipshould be replaced with the version you wish to use) - Once downloaded unzip and copy to the right location which we will need to make.

cd ~/Downloads/
wget https://sourceforge.net/projects/enventory/files/open_enventory_2019-07-24.zip
unzip open_enventory_2019-07-24.zip
sudo mkdir /var/www/html/oe
sudo cp -r ~/Downloads/open_enventory_2019-07-24/. /var/www/html/oe

8. Now go to the http://xx.xx.xx.xx/oe

With xx.xx.xx.xx: is the server’s ip address

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

ifconfig

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

Database: to create a new database, type the desired database’s name
Username: root
Password: use the mariadb password that was set up in step 6 of this tutorial

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

10. Modify .htaccess file

sudo nano /var/www/html/oe/.htaccess

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.

sudo nano /etc/apache2/sites-available/000-default.conf

Then restart the apache server

sudo service apache2 restart

You are done with the initial set up here. Open Enventory is ready to be used at this time.

11. Note this can only be accessed on the local server if you wish to access the OE database on any network you can set up ngrok. If you sign up you can use the given link indefinitely, and using https://freedns.afraid.org/ you can make a DNS to access the database easily.

12. Install ngrok (see https://thisdavej.com/how-to-host-a-raspberry-pi-web-server-on-the-internet-with-ngrok/ for more details)

sudo npm install --unsafe-perm -g ngrok

13. Start the ngrok server

ngrok http 80

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.

CentOS 7

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

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

ssh -p xxxx yy@ zz.zz.zz.zz

: xxxx: port number yy: username zz.zz.zz.zz: ip or hostname

Select “Yes” if asked to continue connecting

If there is a problem, follow this link: https://www.a2hosting.com/kb/getting-started-guide/accessing-your-account/using-ssh-secure-shell

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:

https://www.digitalocean.com/community/tutorials/initial-server-setup-with-centos-7

Install sudo by:

yum –y install sudo

4. Optional: Setting up time by following this link: https://www.tecmint.com/set-time-timezone-and-synchronize-time-using-timedatectl-command/

5. Following this link to set up LAMP stack:

In step 3, replace this part:

with:

yum –y install firewalld
systemctl start firewalld
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --permanent --zone=public --add-port=7822/tcp
firewall-cmd –reload
systemctl enable firewalld

In step 4: choose PHP7.2

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

https://www.howtoforge.com/tutorial/centos-lamp-server-apache-mysql-php/

6. Install the following packages to prepare for Open enventory

yum -y install php-mysql php-gd php-mbstring php-pear zlib-devel ghostscript ImageMagick libreoffice
systemctl restart httpd

7. Create a “download” folder; download OE (get the exact link from website: https://sourceforge.net/projects/enventory/files/?source=navbar ); unzip OE and copy to the right location:

mkdir download
cd download
wget https://sourceforge.net/projects/enventory/files/open_enventory_2018-02-21.zip
unzip open_enventory_2018-02-21.zip
cp -rp open_enventory_2018-02-21/. /var/www/html/oe

8. Go to http://xx.xx.xx.xx/oe

With xx.xx.xx.xx: is the server’s ip address

9. The OE website should load like screenshot below

Database: choose the desired database’s name
Username: root
Password: use the mariadb password that was set up in step 5 of this tutorial

10. If successful, this page will load:

11. Modify .htaccess file

vi /var/www/html/oe/.htaccess

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

systemctl restart httpd

12. Modify mysqld config:

vi /etc/my.cnf.d/server.cnf

Right after [mysqld], add:

sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 
max_allowed_packet = 64M # (or higher) 
innodb_buffer_pool_size = 1024M # to about 50% of the available memory

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

systemctl restart httpd mariadb

You are done with the initial set up here. Open enventory is ready to be used at this time.

Mac OSX

Installation for Mac using XAMPP

Installation

Install XAMPP: https://www.apachefriends.org/index.html
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:

Ensure that the MySQL/MariaDB server is running. (see step 3)
Open a new terminal.
Use the mysqladmin command-line utility to alter the MySQL/MariaDB password, using the following syntax: mysqladmin --user=root password "newpassword"
The mysqladmin command-line utility is located within the bin/ subdirectory of the XAMPP installation directory, typically /Applications/XAMPP.
For example, to change the MySQL/MariaDB root password from its default empty value to the password gue55me, you would execute:/Applications/XAMPP/bin/mysqladmin --user=root password "gue55me"
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"
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”)

/Applications/XAMPP/bin/mysql --user=root --password=gue55me -e "SELECT 1+1"

Steps 6 & 7 are for phpmyadmin set up. They are optional. You don’t need to do this if you just want to set up Open Enventory

6. Change the permission for Applications/XAMPP/xamppfiles/phpmyadmin so that you/your_account have the read and write access, through right click, Get Info (or Cmd-I)

7. Use TextEdit to edit file config.inc.php. Locate: $cfg['Servers'][$i]['auth_type'] = 'config'; and change to: $cfg['Servers'][$i]['auth_type'] = 'cookie';

8. Using a web browser, go to: http://localhost/open_enventory/

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

14. Restart the web browser and go the server website at: http://localhost/open_enventory/ - In Database: put in the name for your database server - Log in with username ‘root’ and set password

CHANGE TIME ZONE

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

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:

Database Backup and Restore

Windows

Install this XAMPP https://www.apachefriends.org/download.html
Copy/extract Open Enventory into C://XAMPP/htdocs/ . Write down the folder name because this will be the web address. In this case the folder is named ‘enventory’
Stated Appache and MySQL

4. Firefox browser type

http://localhost/xampp/

brings up this page

Click security that brings this page

Click the link that brings this page

Put password (for root), check http then close

5. Go to http://localhost/enventory/

It will show error message first then find

“php.ini” text file in “XAMP folder” and Change line

“error_reporting=XXXXXXXXXXXXX” to “error_reporting=E_ALL & ~E_NOTICE”

6. Restart Apache and SQL in XAMPP manager then go to

http://localhost/enventory/

with root login and password, go to settings and create a new user, log out.

then go to http://localhost/enventory/ again

it will bring this page and inventory.

7. If you are locked from login due to incorrect entries of username and password (for 30 min),

Find the notepad file namely “openenv.log” which you can find in a similar path given below

“C:\Users\sr\AppData\Local\Temp\ openenv.log

Open, delete the content, save and restart XAMP and Firefox (http://localhost/enventory/)

Advanced Settings

OE Settings

CHANGING TIMEOUT FOR WRONG PASSWORD

Edit file lib_global_setting.php: from

define("ban_duration",1800);
define("login_max_retries",4);

define("ban_duration",300);
define("login_max_retries",10);

with:

300 (seconds = 5 min) is the time that user is banned from logging in
10: is the number of retries

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

Edit file lib_global_setting.php

from

$allowed_per_page=array(10,25,50,100,-1);
$defaultCurrency="EUR";

$allowed_per_page=array(10,25,50,100,500,1000,-1);
$defaultCurrency="USD";

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

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

memory_limit = xxxM

with xxx = desired memory in megabytes

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

memory_limit = xxxM

with xxx = desired memory in megabytes

SETTING FOR BARCODE SCANNER

For OE original barcode, add Prefix ESC
For existing barcode, (specifically for Tao Tronic scanner: https://smile.amazon.com/TaoTronics-Wireless-Cordless-Handheld-Barcode/dp/B00E0G2M6U/ref=sr_1_4?ie=UTF8&qid=1482003832&sr=8-4&keywords=taotronic+scanner ) add Prefix: [l_shift_on]+[Home]+[l_shift_off]

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

If you find a blank page after clicking the check mark ✅:

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 :

sudo yum install php-gd
sudo yum install php-mbstring

Restart your server (in Centos 7):

sudo systemctl restart httpd

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

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

From

lib/barcode_terminal.js

...
FocusInput(“xxxxxxx”);
...

To:

...
FocusInput(“barcode”);
...

Because this is on javascript file on the client browser, it is best to close the browser and restart the browser on the client/terminal computer

Edit lib_global_funcs.php

Change:

if ($db_name=="") {
    $db_name="storage";

to:

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

2. Restart server

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

Original:

<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
<table id=\"login\" class=\"noborder blind\">
    <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
    <tr><td>".s("database")."</td><td><input type=\"text\" name=\"db_name\" id=\"db_name\" value=".fixStr(strip_tags($db_name),true)." size=\"16\"></td></tr>
    <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
    <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"

to:

<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
<table id=\"login\" class=\"noborder blind\">
   <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
   <tr><td>".s("database")."</td><td><input type=\"text\" name=\"db_name\" id=\"db_name\" autofocus value=".fixStr(strip_tags($db_name),true)." size=\"16\"></td></tr>
   <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
   <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"

UPGRADING OE

Backup current OE files
Download the appropriate files from: https://sourceforge.net/projects/enventory/files/?source=navbar
Unzip the file
Copy the content of the folder into your current [OE_folder] and overwrite the old files
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]
Restart httpd and mariadb (or mysqld) services
Go to web browser and access OE url
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

This will create n number of window for different styles of label when click on this Print Dymo Label button (). Also, this code will close these pop-up windows automatically after 30 seconds (30000 miliseconds)

Create the label by re-design the original file chemical_storage.label (in /oe/forms/dymo/). You can open and edit ".label" files by using Dymo software: https://www.dymo.com/en-US/online-support/dymo-user-guides
Name these files as chemical_storage1.label, chemical_storage2.label, etc.
Save these files in the same location: /oe/forms/dymo/
Edit file edit.js (in /oe/lib/)

From:

function printDymoLabel() {
    var url="editWin.php?mode=print_label&table="+table; window.open(url,Number(new Date()),"height=450,width=300,scrollbars=yes");
}

To:

function printDymoLabel() {
    // First label:
    var url="editWin.php?mode=print_label&table=chemical_storage1";
    var window1=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
    setTimeout(function(){ window1.close() }, 30000); // this will automatically close this pop-up window in 30s;
    
    // Second label:
    var url="editWin.php?mode=print_label&table=chemical_storage2";    
    var window2=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
    window2.moveby(400,0); // this will move this window to the right, same vertical height so that it does not overlap the previous window;
    setTimeout(function(){ window2.close() }, 30000); // this will automatically close this pop-up window in 30s;
}

The code block above will show 2 labels in the popup window. You can add more label by copy the block for "Second label", make sure to change: - variable name for the window (window2 in the example above) - moveby specifications

Pic:

Original file:

Modified file:

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

sudo systemclt restart httpd.service

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

This will make this button () always show up even if the dymo plugin for browser is not detected.

Edit file lib_edit.php (in /oe/)

From:

case "dymo":
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." style=\"display:none\" onClick=\"….

Pic:

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

case "dymo":
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." onClick=\"…

2. Restart the http/apache service

CHANGING LOCATION OF MULTIPLE CONTAINERS

Install phpMyAdmin by following the instruction from: https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-with-apache-on-a-centos-7-server

Note: if you want remote access then follow step 4 in this tutorial: http://www.krizna.com/centos/install-phpmyadmin-centos-7/

2. Log in into phpMyAdmin using Web browser: http://your.ip.address/phpMyAdmin/ (or https://) with root user

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:

Storages/Locations

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

Note: In this example, accidentally, the name of the database (“storage”) is the same as the name of the table. However, the database name can be anything but the table name is always “storage”

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

Type in:

SELECT * FROM storage

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:

SELECT * FROM chemical_storage WHERE storage_id=1

Note: hit Ctrl+Enter to execute command in Console

7. To change, type:

UPDATE chemical_storage SET storage_id=9 WHERE storage_id=1

Done

CHANGING INFO OF MULTIPLE CHEMICALS

Install phpMyAdmin (See above)

To return all chemicals borrow by somebody (borrowed_by_person_id)

Look up person_id by going into [Database]/person
Show chemical borrow by that person (i.e. a person with person_id=8)

SELECT * FROM chemical_storage WHERE borrowed_by_person_id=8

3. Return by:

UPDATE chemical_storage SET borrowed_by_person_id=NULL WHERE borrowed_by_person_id=8

To change the owner info of a chemical (owner_person_id)

Look up person_id by going into [Database]/person
Show chemical owned by that person (i.e. a person with person_id=7)

SELECT * FROM chemical_storage WHERE owner_person_id=7

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

UPDATE chemical_storage SET owner_person_id=NULL WHERE owner_person_id=7

Find person_id using Console command line in PHPMyAdmin

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

SELECT person_id FROM person WHERE username='julia'

Find storage ID using Console Command line in PHPMyAdmin

First choose the desired database on the tree folder from the left
Use:

SELECT storage_id FROM storage WHERE storage_name LIKE 'FH-2206%'

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

SELECT * FROM chemical_storage 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18

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

UPDATE chemical_storage 
SET owner_person_id='NULL' 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18

Explain: see above for explanation of the criteria.

Show storage_name and storage_id using username

SELECT storage_name,storage_id 
FROM storage 
WHERE storage_id IN
    (SELECT storage_id 
     FROM chemical_storage 
     WHERE owner_person_id in
         (SELECT person_id FROM person WHERE username='mingzhao')
     )
GROUP BY storage_name

Explain:

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

Update responsible person for chemicals in many location (storage)

UPDATE chemical_storage 
SET owner_person_id=null 
WHERE owner_person_id=13 AND storage_id IN (19,7,5,4,12,8)

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 != "")

SELECT chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
GROUP BY chemical_storage_barcode
HAVING Count(*) > 1;

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

SELECT molecule_id, 
        chemical_storage_id, 
        chemical_storage_created_when, 
        chemical_storage_created_by,
        chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_barcode IN 
    (SELECT chemical_storage_barcode
     FROM chemical_storage
     WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
     GROUP BY chemical_storage_barcode
     HAVING Count(*) > 1
    ) AND
    chemical_storage_created_by = "user1" AND 
    chemical_storage_created_when > '2019-08-16 18:00:00'
ORDER BY chemical_storage_barcode;

REMOVE ALL CONTAINERS (BOTTLES) OF CHEMICALS

WARNING!!! BE VERY CAREFUL BEFORE DOING THIS!

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.

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

4. Remove the chemical container by running the following command

truncate table chemical_storage;
truncate table cache;
truncate table change_notify;

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

alter table chemical_storage auto_increment=1

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

exit

systemctl restart httpd mariadb

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

Update (Oct 11, 2019): new function to delete multiple containers based on barcode has been added into Settings section. Only users with admin permission would see this option.

SETTINGS FOR OE TO USE WITH EXISTING CONTAINER BARCODES

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

Login into OE with the database that you want to modify, using an admin account, (or root account, or an account with sufficient permission)
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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

4. Remove the chemical container by running the following command

UPDATE chemical_storage 
SET chemical_storage_barcode=trim(trailing cast("\n" as binary) 
    FROM chemical_storage_barcode);

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

exit

systemctl restart httpd mariadb

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

Update (as of 2019-08-01, for Khoi’s modified version): functions “Autogenerate barcode” has been added into Settings for Admin roles. Users with admin permission can login into OE web interface, go to Settings/Autogenerate barcode and click the corresponding button to autogenerate storage and/or user 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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

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.

select storage_name, storage_id,
    concat(concat(92, lpad(storage_id, 5, 0)),
        (10 - ((9*3 + 2 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
             ) % 10)
         ) % 10) 
     as new_barcode from storage;

5. Update the “storage” table, by setting “storage_barcode” field. “storage_barcode” field is a binary so cast() was used. Note, this whole function inside cast() is to convert “storage_id” field to EAN8 barcode. See here for more info on how to make EAN8 barcode: http://www.barcodeisland.com/ean8.phtml

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

update storage
set storage_barcode=cast(
    concat(concat(92, lpad(storage_id, 5, 0)),
                (10 -
                  ((9*3 + 2 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
                   ) % 10
                 )
           ) % 10)
   as binary)
where storage_barcode is NULL or storage_barcode='';

Exit mysql:

exit;

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

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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

select username, person_id,
        concat(concat(91, lpad(person_id, 5, 0)),
                (10 - 
                 ((9*3 + 1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
                   ) % 10)
                ) % 10
               )
as new_barcode from person;

5. Update the “storage” table, by setting storage_barcode field. storage_barcode field is a binary so cast() was used. Note, this whole function inside cast() is to convert storage_id field to EAN8 barcode. See here for more info on how to make EAN8 barcode: http://www.barcodeisland.com/ean8.phtml

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

update person 
set person_barcode=cast(
                      concat(concat(91, lpad(person_id, 5, 0)),
                               (10 - 
                                 ((9*3 + 1 + 
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
                                   ) % 10)
                               ) % 10) as binary
                       )
where person_barcode is NULL or person_barcode='';

6. Exit mysql:

exit;

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

Login into the server via ssh and then convert to root user or use sudo command. The following is using sudo command
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.

vi /var/www/html/oe/lib_output.php

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:

lib_output.php

case "storage":
    if ($row["db_id"]==-1
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
    || $db_user == ROOT) {
        $retval=joinifnotempty(
            array($row["storage_name"],
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }

To let EVERYONE see the locations:

lib_output.php

 case "storage":
     if ($row["db_id"]==-1
     || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
     || $db_user == ROOT) {
         $retval=joinifnotempty(
             array($row["storage_name"],
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
             )
         );
     }
     else {
    // $retval=$row["show_db_beauty_name"]; // Uncomment this and comment out the rest of the block to return to default behavior
         $retval=joinifnotempty(
             array($row["storage_name"],
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
             )
         );
     }

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

lib_output.php

case "storage":
    if ($row["db_id"]==-1
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
    || $db_user == ROOT
    || $db_user == "special_person") { //this is to add some person to be able to the see the location
        $retval=joinifnotempty(
            array($row["storage_name"],
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }
    break;

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)

mysql -u root -p

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

show databases;
use test;

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):

CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));

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):

LOAD DATA LOCAL INFILE '/path-to-file/test.csv' 
INTO TABLE temp_storage
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

5. Update storage table

UPDATE storage
    INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
SET storage.storage_barcode=temp_storage.barcode;

6. Remove the temporary table

DROP TEMPORARY TABLE temp_storage;

If you want to use phpMyAdmin,

Copy csv files into /var/lib/mysql
Open phpMyAdmin console.
Choose the right databases

use test;

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

CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));

LOAD DATA INFILE '/var/lib/mysql/test.csv' INTO TABLE temp_storage
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

UPDATE storage
INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
SET storage.storage_barcode=temp_storage.barcode;

DROP TEMPORARY TABLE temp_storage;

Hosting Server Related

CHANGE TIME ZONE

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

Change:

date.timezone=Europe/Berlin

to:

date.timezone=America/Chicago

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

Follow the instruction in this website to install and enable ssl: http://wiki.centos.org/HowTos/Https#head-35299da4f7078eeba5f5f62b0222acc8c5f2db5f

Ignore Step 3 in the tutorial above

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:

sudo vi /etc/httpd/conf.d/oe.conf

Add the content below:

NameVirtualHost *:80
<VirtualHost *:80>
    ServerName your_url.net
    DocumentRoot /var/www/html/oe
    Redirect permanent / https://your_url.net
</VirtualHost>

NameVirtualHost *:443
<VirtualHost *:443>
    SSLEngine on
    SSLCertificateFile /etc/pki/tls/certs/ca.crt
    SSLCertificateKeyFile /etc/pki/tls/private/ca.key
    <Directory /var/www/html/oe>
        AllowOverride All
    </Directory>
    DocumentRoot /var/www/html/oe
    ServerName your_url.net
</VirtualHost>

All of these below info (highlighted in red can be change to match specific ip address as well as document location:

ServerName your_url.net
DocumentRoot /var/www/html/oe
Redirect permanent / https://romo.ddns.net/

2. Restart apache service, in Terminal:

sudo systemctl restart httpd.service

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)

Set up sshpass to automatically pass the password. Note: this is not ultrasafe but it is simple. Check out this link: http://www.server-world.info/en/note?os=CentOS_7&p=ssh&f=7 (only need to follow how to install, step 1)
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:

crontab –e

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

#This is to back up the Romo's OE database into home/khoi/OEbackup, every day at 00:05 am
5 0 * * * mysqldump -u root –pyour-password romo > /home/khoi/OEbackup/romo-backup-`date +"%Y%m%d"`.sql

#This is to back up the user table in mysql in order to save users' password, every day at 00:10 am
10 0 * * * mysqldump -u root -pyour-password mysql user > /home/khoi/OEbackup/romo-backup-user`date +"%Y%m%d"`.sql

#This is to copy the back up file to External harddrive, every day at 00:15 am
15 0 * * * sshpass -p your-password rsync -ave ssh --ignore-existing /home/khoi/OEbackup/ daniel@romomacpro.ddns.net:/Users/daniel/Documents/OEbackup/

#This is to delete the file every month
0 0 * * */4 rm -r /home/khoi/OEbackup/romo-backup-*.sql

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

Follow this: https://stackoverflow.com/questions/18682308/sort-uniq-ip-address-in-from-apache-log , first answer
Specifically, ssh into the server and then log in as root user
Use following command:

cat /var/log/httpd/access_log | awk '{print $1}' | sort -n | uniq -c | sort -nr | head -20

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

4. Google any ip lookup website and check these IPs(e.g: https://whatismyipaddress.com/ip-lookup). If find any suspicious ones, you can add a rule to block those ip network address by the next step

Setting up rule to block ip network.

Follow this guide: https://www.getpagespeed.com/server-setup/security/centos-7-ban-bad-ips-and-networks-with-firewalld

Finding info about the network list

Follow this guide: https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/security_guide/sec-setting_and_controlling_ip_sets_using_firewalld
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:

firewall-cmd --permanent --get-ipsets

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

firewall-cmd --permanent --info-ipset=test

Or:

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

firewall-cmd --permanent --ipset=test --get-entries

To block IP from a whole country

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:

Good and comprehensive: http://www.vanemery.com/Linux/ACL/linux-acl.html#default
Simple preservation of parent folder group owernship for all files newly created: https://serverfault.com/a/361666
https://unix.stackexchange.com/questions/115631/getting-new-files-to-inherit-group-permissions-on-linux
https://askubuntu.com/questions/642236/how-to-make-the-newly-created-files-inherit-the-directorys-permissions

Resources for Setting Up New Server

SETTING UP NEW SERVER ON A2 HOSTING

This is for CentOS 7 on A2server

A2 hosting was chosen because of: https://hostingfacts.com/hosting-reviews/

1. Setting up LAMP stack

2. Accessing your account:

3. Back-up data (document from A2), also should see Khoi’s document on OE:

https://www.a2hosting.com/kb/getting-started-guide/backing-up-your-data/backups-on-dedicated-servers-and-vps
https://www.experts-exchange.com/questions/28429417/CentOS-How-to-Back-Up-and-Restore-Full-Server.html
To make a script to delete files older than X days when there is less than XGB left, see this:
- https://stackoverflow.com/questions/8110530/check-free-disk-space-for-current-partition-in-bash/37167246#37167246
To monitor uptime for the server, use: https://uptimerobot.com/
Rclone is used to automatically backup file toGoogle Drive account:
- Install: https://rclone.org/install/
- Configuration for Google Drive: https://rclone.org/drive/ or https://linoxide.com/file-system/configure-rclone-linux-sync-cloud/
- Usage: https://rclone.org/docs/
- For Rclone filtering: https://rclone.org/filtering/
To back up the whole VPS, follow this:
- https://www.serverpronto.com/kb/page.php?id=Backing+Up+and+Restore+Your+Server
- This is more options: http://tamxuanla.blogspot.com/2015/10/how-to-backup-full-centos-server_22.html
- Modified script as this:

tar cvpzf /backups/backup-$(date +\%Y\%m\%d).tgz --exclude=/proc --exclude=/lost+found --exclude=/backups --exclude=/dev --exclude=/sys --exclude=/boot/grub --exclude=/etc/fstab --exclude=/etc/sysconfig/network-scripts/ --exclude=/etc/udev/rules.d/70-persistent-net.rules --exclude=/home/some_other_folders

4. Install EPEL repository on CentOS 7:

5. Change timezone on Linux shell:

6. Set default text Editor:

7. Obtaining free SSL certificate (from Let’sEncrypt):

First: Make sure you have the your_site.com.conf file inside /etc/httpd/conf.d. Following the following website: https://www.rosehosting.com/blog/apache-virtual-hosts-on-centos/ ; For more elaborate see: https://devops.profitbricks.com/tutorials/how-to-set-up-name-based-virtual-hosting-vhosts-with-apache-web-server-on-centos-7-1/
Optional, here is another way the step above can be set up (Note: this website set up a bit different than the one above, specifically, the above website use /etc/httpd/conf.d/ instead of /etc/httpd/sites-enabled): https://www.digitalocean.com/community/tutorials/how-to-set-up-apache-virtual-hosts-on-centos-7
danielromogroup.com and other sites on this server has been set up using /etc/httpd/conf.d/site.com.conf
This is a good installation for Certbot Let’sEncrypt (ignore the multiple certificate setting): https://certbot.eff.org/#centosrhel7-apache
Using Certbot (Intro): https://certbot.eff.org/#centosrhel7-other
Doc: https://certbot.eff.org/docs/intro.html
Rate limit for Let’sEncrypt: https://community.letsencrypt.org/t/rate-limits-for-lets-encrypt/6769
To test your SSL for your site:
- https://www.ssllabs.com/ssltest/
- https://www.digicert.com/help/
To read more about SSL: https://yoast.com/dev-blog/move-website-https-ssl/
To strengthen your SSL connection:
- Info:
  - https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
  - https://yoast.com/dev-blog/move-website-https-ssl/
- Remove SSLv3 SSLv2: https://www.digitalocean.com/community/tutorials/how-to-protect-your-server-against-the-poodle-sslv3-vulnerability
- HSTS Strict-Transport-Security:
- OCSP Stapling: help with all SSL security connection a little faster:

8. Setting up owner of /var/www/html folders, to easy update website:

9. Creating groups, users; Assigning passwords:

https://www.digitalocean.com/community/tutorials/how-to-add-and-delete-users-on-a-centos-7-server
Assinging password and require immediate pass change after log in: https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Security_Guide/chap-Hardening_Your_System_with_Tools_and_Services.html#sec-Password_Security
Add Linux users to a group: http://www.cyberciti.biz/faq/howto-linux-add-user-to-group/
- http://www.howtogeek.com/50787/add-a-user-to-a-group-or-second-group-on-linux/
- http://www.tecmint.com/add-users-in-linux/

10. Force using https access using .htaccess:

11. Configure PHP to read .htaccess file on a CentOS 7 server

We need to modify the httpd.conf file:

vi /etc/httpd/conf/httpd.conf

In this file: after <Directory "/var/www/html">, modify AllowOverride None to AllowOverride Options.

Save the file (hit Esc, type :wq and then Enter)

Restart httpd service:

systemctl restart httpd

15. Installing phpMyAdmin

16. Working with tar files

17. Setting up multiple websites with multiple domains on the same server

On current A2 hosting, the server config can be found in /etc/httpd/conf/httpd.conf
https://devops.profitbricks.com/tutorials/how-to-set-up-name-based-virtual-hosting-vhosts-with-apache-web-server-on-centos-7-1/
Setting up multiple Let’sEncrypt certificates for multiple hosts on the same server: https://www.digitalocean.com/community/tutorials/how-to-set-up-let-s-encrypt-certificates-for-multiple-apache-virtual-hosts-on-ubuntu-14-04 (this is for Ubuntu)
- For CentOS, generate two #.conf files in /etc/httpd/conf.d/. For example: /etc/httpd/conf.d/site1.com.conf and /etc/httpd/conf.d/site2.com.conf. Each conf file is the apache set up for the site. Example of the content of site2.com.conf:

<VirtualHost XX.XX.XX.XX:80>
    DocumentRoot /var/www/html/site2.com
    ServerName server.site2.com
    ServerAlias site2.com www.site2.com
</VirtualHost>

- After that use “/usr/local/sbin/certbot --apache -d example.com -d www.example.com” . The content of site2.com.conf file will be automatically changed. Here is example of the content of site2.com.conf after running the “certbot-auto…”

<VirtualHost XX.XX.XX.XX:80>
    DocumentRoot /var/www/html/site2.com
    ServerName server.site2.com
    ServerAlias site2.com www.site2.com
</VirtualHost>
<IfModule mod_ssl.c>
    <VirtualHost XX.XX.XX.XX:443>
        DocumentRoot /var/www/html/site2.com
        ServerName server.site2.com
        ServerAlias site2.com www.site2.com
        SSLCertificateFile /etc/letsencrypt/live/site2.com/cert.pem
        SSLCertificateKeyFile /etc/letsencrypt/live/site2.com/privkey.pem
        Include /etc/letsencrypt/options-ssl-apache.conf
        SSLCertificateChainFile /etc/letsencrypt/live/site2.com/chain.pem
    </VirtualHost>
</IfModule>

Read here for more info about a set up a conf file for SSL certificate: https://www.linode.com/docs/security/ssl/ssl-certificates-with-apache-2-on-centos

18. Working with WordPress

Initial Install: https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-on-centos-7
- From A2: https://www.a2hosting.com/kb/installable-applications/manual-installations/installing-wordpress-manually
- WP security, from A2 hosting: https://www.a2hosting.com/kb/security/application-security/wordpress-security
- UpdraftPlus Plugin is used to back up WordPress. To restore Wordpress
  - Create a fresh install of wordpress as in the link above
  - Install Updraftplus plugin
  - Load the backup files
  - Restore
  - If there is issue after restore, check here first. A common issue is the rewrite link issue (Wordpress Permanet link), follow the solution in “Using ‘Pretty’ permalinks” in this link: https://codex.wordpress.org/Using_Permalinks
- Optimizing WP, from A2 hosting: https://www.a2hosting.com/kb/installable-applications/optimization-and-configuration/wordpress2/optimizing-wordpress-with-the-a2-optimized-plugin;
  - https://www.a2hosting.com/kb/installable-applications/optimization-and-configuration/wordpress2/optimizing-wordpress-with-w3-total-cache-and-gtmetrix
Multiple sites (using WordPress) installation: https://www.digitalocean.com/community/tutorials/how-to-set-up-multiple-wordpress-sites-using-multisite
Moving Wordpress site(s): https://codex.wordpress.org/Moving_WordPress
Uninstall Wordpress: https://www.tipsandtricks-hq.com/how-to-uninstall-and-reinstall-wordpress-245
To use 1-click update on Wordpress, you need
- Wordpress folder (in this case /var/www/html/baylorcpritlab.com) to be owned by apache:apache with 755 permission
- To secure Wordpress, all folders inside the baylorcpritlab.com should have 755 persmission and all files should have 644 permission
Wordpress login trouble: https://codex.wordpress.org/Login_Trouble

19. Setting up Booked Scheduler

See here for official guide: http://www.bookedscheduler.com/help
Note: when change the info in the config.php file, make sure to change the:
1. Install password
2. User: to ‘root’
3. Password to ‘root_passowrd’
Also see: https://www.bookedscheduler.com/images/community-contrib/CentOS_booked_v3.pdf

Other Supporting Programs for OE

Find Missing SDS

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:

DETAILS

This programs does:

1. Connect into mysql database and find molecule in 'molecule' table of specific database and find those molecule with missing sds

2. Try to download sds files into a folder in /var/lib/mysql/missing_sds

3. Update those sql entries with new downloaded sds files

REQUIREMENTS

root access to the server hosting Open Enventory
Python 3+
This file is made for Linux environment, you should be able
to used it on other OS with changing the location of the "download_path"

USAGE

After cloning this repo onto the Open Enventory server:

Change into directory of the new file:
```
cd find_missing_sds-public
```

(Optional): create virtual environment for python to install dependency:

# you can change "find_missing_sds_venv" to another name too
python3 -m venv find_missing_sds_venv   # Create virtual environment
source find_missing_sds_venv/bin/activate    # Activate the virtual environment

Install python dependencies:
```
pip install -r requirements.txt
```
Run the program:
```
# Replace "find_missing_sds_v5" with latest version if neccessary
python3 find_missing_sds_v5/find_missing_sds.py
```
- Answer questions for:
  - confirming running under root
  - mySQL root password (typing password will not be shown on screen)
  - the name of the database you want to update (twice to confirm)

VERSIONS

Version 5:

Incorporated result from Fluorochem
Fixing bug with existing default_safety_sheet_url and default_safety_sheet_mime
by setting them to NULL

Version 4:

Testing using cheminfo.org/webservices by extracting catalog number from fluorochem

Version 3:

Refactor extracting url download into its own method
Add extracting url download from chemicalsafety.com

Version 2:

Add asking if user is root and password
Add asking what database to be modified
Switch to extracting data from https://www.fishersci.com because Chemexper
has limited requests

Find Missing Structure

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

DETAILS

This programs does:

Connect into mysql database and find molecule in 'molecule' table
of specific database and find those molecule with missing structure (smiles)
folder "missing_mol_files" needs to be created inside /var/lib/mysql
with 'mysql' as ownner (chown mysql:mysql)
Try to download mol files from various sources into a folder in
/var/lib/mysql/missing_mol_files
Update those sql entries with new downloaded mol_files

REQUIREMENTS

root access to the server hosting Open Enventory
Python 3+
This file is made for Linux environment, you should be able
to used it on other OS with changing the location of the "download_path"

USAGE

After cloning this repo onto the Open Enventory server:

Change into directory of the new file:
```
cd update_sql_mol
```

(Optional): create virtual environment for python to install dependency:

# you can change "update_sql_mol_venv" to other name too
python3 -m venv update_sql_mol_venv   # Create virtual environment
source update_sql_mol_venv/bin/activate    # Activate the virtual environment

Install python dependencies:
```
pip install -r requirements.txt
```
Run the program:
```
python3 update_sql_mol_v6/update_sql_mol.py    # Replace "update_sql_mol_v6" with latest version
```
- Answer questions for:
  - confirming running under root
  - mySQL root password (typing password will not be shown on screen)
  - the name of the database you want to update (twice to confirm)
  - url path for your Open Enventory server (including 'http/https' and no trailing '/')

VERSIONS

Version 4:

Using simple download bar with bar=wget.bar_thermometer in wget.download
Added handling database connection error (wrong password, wrong database)
Added confirmation for database

Version 5

Change to https (from http) for ChemicalBook site

Version 6 (2020-01-18):

Remove this extra step of going into Open Enventory manual by the user using a web browser and doing Batch Processing.
Why: for versions 5 and before:
after running this program, YOU STILL NEED TO LOGIN INTO OPEN ENVENTORY ON A WEB BROWSER AS ROOT, GO TO "SETTINGS/BATCH PROCESSING". CHOOSE DATABASE THAT YOU WANT TO UPDATE AND THEN CHOOSE "MOLECULE", "EMPIRICAL FORMULA", "MW", "DEG. OF UNSAT." , "STRUCTURE", "SMILES" AND "FINGERPRINT" AND THEN SUBMIT TO UPDATE THE SQL QUERY. ONLY AFTER THIS, THE STRUCTURE WILL SHOW UP

Docker Build

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.

Change Logs

Versions

Below is info for changes that are made for OE in KV's github: https://github.com/khoivan88/open_enventory-modified_for_US

2020-01-20:

Add support for importing 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
Change 'User Guides' to direct to gitbook (https://open-enventory.gitbook.io/)

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

OE Settings

CHANGING TIMEOUT FOR WRONG PASSWORD

Edit file lib_global_setting.php: from

define("ban_duration",1800);
define("login_max_retries",4);

define("ban_duration",300);
define("login_max_retries",10);

with:

300 (seconds = 5 min) is the time that user is banned from logging in
10: is the number of retries

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

Edit file lib_global_setting.php

from

$allowed_per_page=array(10,25,50,100,-1);
$defaultCurrency="EUR";

$allowed_per_page=array(10,25,50,100,500,1000,-1);
$defaultCurrency="USD";

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

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

memory_limit = xxxM

with xxx = desired memory in megabytes

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

memory_limit = xxxM

with xxx = desired memory in megabytes

SETTING FOR BARCODE SCANNER

For OE original barcode, add Prefix ESC
For existing barcode, (specifically for Tao Tronic scanner: https://smile.amazon.com/TaoTronics-Wireless-Cordless-Handheld-Barcode/dp/B00E0G2M6U/ref=sr_1_4?ie=UTF8&qid=1482003832&sr=8-4&keywords=taotronic+scanner ) add Prefix: [l_shift_on]+[Home]+[l_shift_off]

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

If you find a blank page after clicking the check mark ✅:

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 :

sudo yum install php-gd
sudo yum install php-mbstring

Restart your server (in Centos 7):

sudo systemctl restart httpd

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

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

From

lib/barcode_terminal.js

...
FocusInput(“xxxxxxx”);
...

To:

...
FocusInput(“barcode”);
...

Because this is on javascript file on the client browser, it is best to close the browser and restart the browser on the client/terminal computer

Edit lib_global_funcs.php

Change:

if ($db_name=="") {
    $db_name="storage";

to:

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

2. Restart server

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

Original:

<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
<table id=\"login\" class=\"noborder blind\">
    <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
    <tr><td>".s("database")."</td><td><input type=\"text\" name=\"db_name\" id=\"db_name\" value=".fixStr(strip_tags($db_name),true)." size=\"16\"></td></tr>
    <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
    <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"

to:

<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
<table id=\"login\" class=\"noborder blind\">
   <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
   <tr><td>".s("database")."</td><td><input type=\"text\" name=\"db_name\" id=\"db_name\" autofocus value=".fixStr(strip_tags($db_name),true)." size=\"16\"></td></tr>
   <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
   <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"

UPGRADING OE

Backup current OE files
Download the appropriate files from: https://sourceforge.net/projects/enventory/files/?source=navbar
Unzip the file
Copy the content of the folder into your current [OE_folder] and overwrite the old files
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]
Restart httpd and mariadb (or mysqld) services
Go to web browser and access OE url
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

Create the label by re-design the original file chemical_storage.label (in /oe/forms/dymo/). You can open and edit ".label" files by using Dymo software: https://www.dymo.com/en-US/online-support/dymo-user-guides
Name these files as chemical_storage1.label, chemical_storage2.label, etc.
Save these files in the same location: /oe/forms/dymo/
Edit file edit.js (in /oe/lib/)

From:

function printDymoLabel() {
    var url="editWin.php?mode=print_label&table="+table; window.open(url,Number(new Date()),"height=450,width=300,scrollbars=yes");
}

To:

function printDymoLabel() {
    // First label:
    var url="editWin.php?mode=print_label&table=chemical_storage1";
    var window1=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
    setTimeout(function(){ window1.close() }, 30000); // this will automatically close this pop-up window in 30s;
    
    // Second label:
    var url="editWin.php?mode=print_label&table=chemical_storage2";    
    var window2=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
    window2.moveby(400,0); // this will move this window to the right, same vertical height so that it does not overlap the previous window;
    setTimeout(function(){ window2.close() }, 30000); // this will automatically close this pop-up window in 30s;
}

Pic:

Original file:

Modified file:

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

sudo systemclt restart httpd.service

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

This will make this button () always show up even if the dymo plugin for browser is not detected.

Edit file lib_edit.php (in /oe/)

From:

case "dymo":
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." style=\"display:none\" onClick=\"….

Pic:

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

case "dymo":
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." onClick=\"…

2. Restart the http/apache service

CHANGING LOCATION OF MULTIPLE CONTAINERS

Install phpMyAdmin by following the instruction from: https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-with-apache-on-a-centos-7-server

Note: if you want remote access then follow step 4 in this tutorial: http://www.krizna.com/centos/install-phpmyadmin-centos-7/

2. Log in into phpMyAdmin using Web browser: http://your.ip.address/phpMyAdmin/ (or https://) with root user

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:

Storages/Locations

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:

SELECT * FROM storage

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:

SELECT * FROM chemical_storage WHERE storage_id=1

Note: hit Ctrl+Enter to execute command in Console

7. To change, type:

UPDATE chemical_storage SET storage_id=9 WHERE storage_id=1

Done

CHANGING INFO OF MULTIPLE CHEMICALS

Install phpMyAdmin (See above)

To return all chemicals borrow by somebody (borrowed_by_person_id)

Look up person_id by going into [Database]/person
Show chemical borrow by that person (i.e. a person with person_id=8)

SELECT * FROM chemical_storage WHERE borrowed_by_person_id=8

3. Return by:

UPDATE chemical_storage SET borrowed_by_person_id=NULL WHERE borrowed_by_person_id=8

To change the owner info of a chemical (owner_person_id)

Look up person_id by going into [Database]/person
Show chemical owned by that person (i.e. a person with person_id=7)

SELECT * FROM chemical_storage WHERE owner_person_id=7

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

UPDATE chemical_storage SET owner_person_id=NULL WHERE owner_person_id=7

Find person_id using Console command line in PHPMyAdmin

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

SELECT person_id FROM person WHERE username='julia'

Find storage ID using Console Command line in PHPMyAdmin

First choose the desired database on the tree folder from the left
Use:

SELECT storage_id FROM storage WHERE storage_name LIKE 'FH-2206%'

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

SELECT * FROM chemical_storage 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18

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

UPDATE chemical_storage 
SET owner_person_id='NULL' 
WHERE owner_person_id=15 AND storage_id NOT LIKE 18

Explain: see above for explanation of the criteria.

Show storage_name and storage_id using username

SELECT storage_name,storage_id 
FROM storage 
WHERE storage_id IN
    (SELECT storage_id 
     FROM chemical_storage 
     WHERE owner_person_id in
         (SELECT person_id FROM person WHERE username='mingzhao')
     )
GROUP BY storage_name

Explain:

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

Update responsible person for chemicals in many location (storage)

UPDATE chemical_storage 
SET owner_person_id=null 
WHERE owner_person_id=13 AND storage_id IN (19,7,5,4,12,8)

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

SELECT chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
GROUP BY chemical_storage_barcode
HAVING Count(*) > 1;

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

SELECT molecule_id, 
        chemical_storage_id, 
        chemical_storage_created_when, 
        chemical_storage_created_by,
        chemical_storage_barcode
FROM chemical_storage
WHERE chemical_storage_barcode IN 
    (SELECT chemical_storage_barcode
     FROM chemical_storage
     WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
     GROUP BY chemical_storage_barcode
     HAVING Count(*) > 1
    ) AND
    chemical_storage_created_by = "user1" AND 
    chemical_storage_created_when > '2019-08-16 18:00:00'
ORDER BY chemical_storage_barcode;

REMOVE ALL CONTAINERS (BOTTLES) OF CHEMICALS

WARNING!!! BE VERY CAREFUL BEFORE DOING THIS!

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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

4. Remove the chemical container by running the following command

truncate table chemical_storage;
truncate table cache;
truncate table change_notify;

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

alter table chemical_storage auto_increment=1

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

exit

systemctl restart httpd mariadb

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

Update (Oct 11, 2019): new function to delete multiple containers based on barcode has been added into Settings section. Only users with admin permission would see this option.

SETTINGS FOR OE TO USE WITH EXISTING CONTAINER BARCODES

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

Login into OE with the database that you want to modify, using an admin account, (or root account, or an account with sufficient permission)
Navigate to Settings/Global Settings/Inventory

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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

4. Remove the chemical container by running the following command

UPDATE chemical_storage 
SET chemical_storage_barcode=trim(trailing cast("\n" as binary) 
    FROM chemical_storage_barcode);

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

exit

systemctl restart httpd mariadb

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

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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

select storage_name, storage_id,
    concat(concat(92, lpad(storage_id, 5, 0)),
        (10 - ((9*3 + 2 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
             ) % 10)
         ) % 10) 
     as new_barcode from storage;

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

update storage
set storage_barcode=cast(
    concat(concat(92, lpad(storage_id, 5, 0)),
                (10 -
                  ((9*3 + 2 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
                   ) % 10
                 )
           ) % 10)
   as binary)
where storage_barcode is NULL or storage_barcode='';

Exit mysql:

exit;

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

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

Login into your server via ssh
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

mysql –u root -p

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

show databases;
use test;

select username, person_id,
        concat(concat(91, lpad(person_id, 5, 0)),
                (10 - 
                 ((9*3 + 1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
                   ) % 10)
                ) % 10
               )
as new_barcode from person;

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

update person 
set person_barcode=cast(
                      concat(concat(91, lpad(person_id, 5, 0)),
                               (10 - 
                                 ((9*3 + 1 + 
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
                                   ) % 10)
                               ) % 10) as binary
                       )
where person_barcode is NULL or person_barcode='';

6. Exit mysql:

exit;

SETTING TO ALLOW SEEING LOCATION OF CHEMICAL OUTSIDE YOUR OWN GROUP

Login into the server via ssh and then convert to root user or use sudo command. The following is using sudo command
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.

vi /var/www/html/oe/lib_output.php

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:

lib_output.php

case "storage":
    if ($row["db_id"]==-1
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
    || $db_user == ROOT) {
        $retval=joinifnotempty(
            array($row["storage_name"],
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }

To let EVERYONE see the locations:

lib_output.php

 case "storage":
     if ($row["db_id"]==-1
     || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
     || $db_user == ROOT) {
         $retval=joinifnotempty(
             array($row["storage_name"],
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
             )
         );
     }
     else {
    // $retval=$row["show_db_beauty_name"]; // Uncomment this and comment out the rest of the block to return to default behavior
         $retval=joinifnotempty(
             array($row["storage_name"],
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
             )
         );
     }

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

lib_output.php

case "storage":
    if ($row["db_id"]==-1
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
    || $db_user == ROOT
    || $db_user == "special_person") { //this is to add some person to be able to the see the location
        $retval=joinifnotempty(
            array($row["storage_name"],
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }
    break;

UPLOADING EXISTING LOCATION BARCODES

Below guide is referenced from:

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

mysql -u root -p

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

show databases;
use test;

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):

CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));

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):

LOAD DATA LOCAL INFILE '/path-to-file/test.csv' 
INTO TABLE temp_storage
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

5. Update storage table

UPDATE storage
    INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
SET storage.storage_barcode=temp_storage.barcode;

6. Remove the temporary table

DROP TEMPORARY TABLE temp_storage;

If you want to use phpMyAdmin,

Copy csv files into /var/lib/mysql
Open phpMyAdmin console.
Choose the right databases

use test;

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

CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));

LOAD DATA INFILE '/var/lib/mysql/test.csv' INTO TABLE temp_storage
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

UPDATE storage
INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
SET storage.storage_barcode=temp_storage.barcode;

DROP TEMPORARY TABLE temp_storage;