OE Settings

CHANGING TIMEOUT FOR WRONG PASSWORD

Edit file lib_global_setting.php: from

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

to

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

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";

to

$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

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

1. For OE original barcode, add Prefix ESC

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

• 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

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

From

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

To:

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

CHANGE LOGIN DEFAULT DATABASE TO NONE

1. Edit lib_global_funcs.php

Change:

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

to:

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

2. Restart server

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

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

Original:

<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

1. Backup current OE files

2. Download the appropriate files from: https://sourceforge.net/projects/enventory/files/?source=navbar

3. Unzip the file

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

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

6. Restart httpd and mariadb (or mysqld) services

7. Go to web browser and access OE url

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

MULTIPLE LABEL STYLES

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

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

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

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

From:

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

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

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

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:

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

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)

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

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

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)

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

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

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

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

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

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

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

1. Login into your server via ssh

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

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

SETTINGS FOR OE TO USE WITH EXISTING CONTAINER BARCODES

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

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

2. Navigate to Settings/Global Settings/Inventory

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

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

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

1. Login into your server via ssh

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

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”

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

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

1. Login into your server via ssh

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

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='';

1. Exit mysql:

exit;

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

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

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

1. Login into your server via ssh

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

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

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

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

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:

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")." ",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }

To let EVERYONE see the locations:

 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")." ",$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")." ",$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”

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")." ",$row["compartment"]) // Fach X
            )
        );
    }
    else {
        $retval=$row["show_db_beauty_name"];
    }
    break;

UPLOADING EXISTING LOCATION BARCODES

Below guide is referenced from:

• https://stackoverflow.com/questions/21495600/import-csv-to-update-rows-in-table

• https://dba.stackexchange.com/questions/11811/mysql-csv-update-not-insert-into-existing-table

• https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile

• https://stackoverflow.com/questions/4215231/load-data-infile-error-code-13

• https://stackoverflow.com/questions/31892607/load-file-into-table-0-rows-affected

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

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

1. Login into mysql as root user using:

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,

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

2. Open phpMyAdmin console.

3. 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;