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
edit file
php.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
...
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
CHANGE LOGIN DEFAULT DATABASE TO NONE
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
Edit
lib_global_funcs.php
by addingautofocus
afterid=\"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-guidesName 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
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
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]/personShow 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]/personShow 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 thestorage_id
of the storage location with name starting with FH-2206LIKE '%FH-2206%'
would mean find anything with storage name containing FH-2206LIKE '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
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”
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”
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
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 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:
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:
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)
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,
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;
Last updated
Was this helpful?