OE Settings

CHANGING TIMEOUT FOR WRONG PASSWORD
Edit file lib_global_setting.php:
    from
1
define("ban_duration",1800);
2
define("login_max_retries",4);
Copied!
    to
1
define("ban_duration",300);
2
define("login_max_retries",10);
Copied!
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
1
$allowed_per_page=array(10,25,50,100,-1);
2
$defaultCurrency="EUR";
Copied!
    to
1
$allowed_per_page=array(10,25,50,100,500,1000,-1);
2
$defaultCurrency="USD";
Copied!
​
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)
1
memory_limit = xxxM
Copied!
with xxx = desired memory in megabytes
​
2. edit file.htaccess (in the folder install oe)
1
memory_limit = xxxM
Copied!
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
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 :
1
sudo yum install php-gd
2
sudo yum install php-mbstring
Copied!
Restart your server (in Centos 7):
1
sudo systemctl restart httpd
Copied!
​
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
lib/barcode_terminal.js
1
...
2
FocusInput(“xxxxxxx”);
3
...
Copied!
To:
1
...
2
FocusInput(“barcode”);
3
...
Copied!
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
1.
Edit lib_global_funcs.php
Change:
1
if ($db_name=="") {
2
    $db_name="storage";
Copied!
to:
1
if ($db_name=="") {
2
    $db_name="";
Copied!
    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:
1
<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
2
<table id=\"login\" class=\"noborder blind\">
3
    <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
4
    <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>
5
    <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
6
    <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"
Copied!
to:
1
<br><form id=\"login\" name=\"login\" method=\"post\" action=\"index.php?".getSelfRef(array("~script~","table"))."\">
2
<table id=\"login\" class=\"noborder blind\">
3
   <tr><td colspan=\"2\">".s("please_logon_to1")."</td></tr>
4
   <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>
5
   <tr><td colspan=\"2\">".s("please_logon_to2")."</td></tr>
6
   <tr><td>".s("db_user").":</td><td><input type=\"text\" name=\"user\" id=\"user\"
Copied!
​
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
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)
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:
1
function printDymoLabel() {
2
    var url="editWin.php?mode=print_label&table="+table; window.open(url,Number(new Date()),"height=450,width=300,scrollbars=yes");
3
}
Copied!
To:
1
function printDymoLabel() {
2
    // First label:
3
    var url="editWin.php?mode=print_label&table=chemical_storage1";
4
    var window1=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
5
    setTimeout(function(){ window1.close() }, 30000); // this will automatically close this pop-up window in 30s;
6
    
7
    // Second label:
8
    var url="editWin.php?mode=print_label&table=chemical_storage2";    
9
    var window2=window.open(url,Number(new Date()),"height=300,width=200,scrollbars=yes");
10
    window2.moveby(400,0); // this will move this window to the right, same vertical height so that it does not overlap the previous window;
11
    setTimeout(function(){ window2.close() }, 30000); // this will automatically close this pop-up window in 30s;
12
}
Copied!
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:
1
sudo systemclt restart httpd.service
Copied!
​
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.
1.
Edit file lib_edit.php (in /oe/)
From:
1
case "dymo":
2
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." style=\"display:none\" onClick=\"….
Copied!
Pic:
To (remove style=\"display:none\")
1
case "dymo":
2
    $retval.="<a id=\"dymo\" href=\"javascript:void(0);\" class=\"imgButtonSm\" title=".fixStr(s("print_dymo_label"))." onClick=\"…
Copied!
    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​
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
Screen shot 2015-07-16 at 7
    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:
1
SELECT * FROM storage
Copied!
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:
1
SELECT * FROM chemical_storage WHERE storage_id=1
Copied!
Note: hit Ctrl+Enter to execute command in Console 
    7. To change, type:
1
UPDATE chemical_storage SET storage_id=9 WHERE storage_id=1
Copied!
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)
1
SELECT * FROM chemical_storage WHERE borrowed_by_person_id=8
Copied!
    3. Return by:
1
UPDATE chemical_storage SET borrowed_by_person_id=NULL WHERE borrowed_by_person_id=8
Copied!
​
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)
1
SELECT * FROM chemical_storage WHERE owner_person_id=7
Copied!
   3. Change, in this case, to no owner (NULL)
1
UPDATE chemical_storage SET owner_person_id=NULL WHERE owner_person_id=7
Copied!
​
Find person_id using Console command line in PHPMyAdmin
In the following example, show person_id of user with username=julia
1
SELECT person_id FROM person WHERE username='julia'
Copied!
​
Find storage ID using Console Command line in PHPMyAdmin
1.
First choose the desired database on the tree folder from the left
2.
Use:
1
SELECT storage_id FROM storage WHERE storage_name LIKE 'FH-2206%'
Copied!
 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
1
SELECT * FROM chemical_storage 
2
WHERE owner_person_id=15 AND storage_id NOT LIKE 18
Copied!
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
1
UPDATE chemical_storage 
2
SET owner_person_id='NULL' 
3
WHERE owner_person_id=15 AND storage_id NOT LIKE 18
Copied!
 Explain: see above for explanation of the criteria.
​
Show storage_name and storage_id using username
1
SELECT storage_name,storage_id 
2
FROM storage 
3
WHERE storage_id IN
4
    (SELECT storage_id 
5
     FROM chemical_storage 
6
     WHERE owner_person_id in
7
         (SELECT person_id FROM person WHERE username='mingzhao')
8
     )
9
GROUP BY storage_name
Copied!
 Explain:
 GROUP BY storage_name : is added so that duplicates are only shown once
​
Update responsible person for chemicals in many location (storage)
1
UPDATE chemical_storage 
2
SET owner_person_id=null 
3
WHERE owner_person_id=13 AND storage_id IN (19,7,5,4,12,8)
Copied!
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 != "")
1
SELECT chemical_storage_barcode
2
FROM chemical_storage
3
WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
4
GROUP BY chemical_storage_barcode
5
HAVING Count(*) > 1;
Copied!
​
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
1
SELECT molecule_id, 
2
        chemical_storage_id, 
3
        chemical_storage_created_when, 
4
        chemical_storage_created_by,
5
        chemical_storage_barcode
6
FROM chemical_storage
7
WHERE chemical_storage_barcode IN 
8
    (SELECT chemical_storage_barcode
9
     FROM chemical_storage
10
     WHERE chemical_storage_disabled IS NULL AND chemical_storage_barcode != ""
11
     GROUP BY chemical_storage_barcode
12
     HAVING Count(*) > 1
13
    ) AND
14
    chemical_storage_created_by = "user1" AND 
15
    chemical_storage_created_when > '2019-08-16 18:00:00'
16
ORDER BY chemical_storage_barcode;
Copied!
​
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.
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
1
mysql –u root -p
Copied!
    3. Show database and then choose the database of interest. test is the name of the database in this example
1
show databases;
2
use test;
Copied!
    4. Remove the chemical container by running the following command
1
truncate table chemical_storage;
2
truncate table cache;
3
truncate table change_notify;
Copied!
    5. (Optional): Run the command below if you want to reset the id counter (chemical_storage_id as well as autogenerated barcode)
1
alter table chemical_storage auto_increment=1
Copied!
    6. Log out of mysql, restart httpd and mariadb services
1
exit
Copied!
1
systemctl restart httpd mariadb
Copied!
    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
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
1
mysql –u root -p
Copied!
    3. Show database and then choose the database of interest. “test” is the name of the database in this example:
1
show databases;
2
use test;
Copied!
    4. Remove the chemical container by running the following command
1
UPDATE chemical_storage 
2
SET chemical_storage_barcode=trim(trailing cast("\n" as binary) 
3
    FROM chemical_storage_barcode);
Copied!
    5. Log out of mysql, restart httpd and mariadb services
1
exit
Copied!
1
systemctl restart httpd mariadb
Copied!
​
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
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
1
mysql –u root -p
Copied!
    3. Show database and then choose the database of interest. “test” is the name of the database in this example:
1
show databases;
2
use test;
Copied!
    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.
1
select storage_name, storage_id,
2
    concat(concat(92, lpad(storage_id, 5, 0)),
3
        (10 - ((9*3 + 2 +
4
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
5
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
6
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
7
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
8
            substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
9
             ) % 10)
10
         ) % 10) 
11
     as new_barcode from storage;
Copied!
    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
1
update storage
2
set storage_barcode=cast(
3
    concat(concat(92, lpad(storage_id, 5, 0)),
4
                (10 -
5
                  ((9*3 + 2 +
6
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 3, 1)*3 +
7
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 4, 1)*1 +
8
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 5, 1)*3 +
9
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 6, 1)*1 +
10
                    substring(cast(concat(92, lpad(storage_id, 5, 0)) as char), 7, 1)*3
11
                   ) % 10
12
                 )
13
           ) % 10)
14
   as binary)
15
where storage_barcode is NULL or storage_barcode='';
Copied!
1.
Exit mysql:
1
exit;
Copied!
​
TO AUTOMATICALLY ADD PERSON 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 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
1
mysql –u root -p
Copied!
    3. Show database and then choose the database of interest. “test” is the name of the database in this example:
1
show databases;
2
use test;
Copied!
    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.
1
select username, person_id,
2
        concat(concat(91, lpad(person_id, 5, 0)),
3
                (10 - 
4
                 ((9*3 + 1 +
5
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
6
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
7
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
8
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
9
                  substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
10
                   ) % 10)
11
                ) % 10
12
               )
13
as new_barcode from person;
Copied!
    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
1
update person 
2
set person_barcode=cast(
3
                      concat(concat(91, lpad(person_id, 5, 0)),
4
                               (10 - 
5
                                 ((9*3 + 1 + 
6
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 3, 1)*3 +
7
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 4, 1)*1 +
8
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 5, 1)*3 +
9
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 6, 1)*1 +
10
                                   substring(cast(concat(91, lpad(person_id, 5, 0)) as char), 7, 1)*3
11
                                   ) % 10)
12
                               ) % 10) as binary
13
                       )
14
where person_barcode is NULL or person_barcode='';
Copied!
    6. Exit mysql:
1
exit;
Copied!
​
 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.
1
vi /var/www/html/oe/lib_output.php
Copied!
    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
1
case "storage":
2
    if ($row["db_id"]==-1
3
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
4
    || $db_user == ROOT) {
5
        $retval=joinifnotempty(
6
            array($row["storage_name"],
7
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
8
            )
9
        );
10
    }
11
    else {
12
        $retval=$row["show_db_beauty_name"];
13
    }
Copied!
To let EVERYONE see the locations:
lib_output.php
1
 case "storage":
2
     if ($row["db_id"]==-1
3
     || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
4
     || $db_user == ROOT) {
5
         $retval=joinifnotempty(
6
             array($row["storage_name"],
7
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
8
             )
9
         );
10
     }
11
     else {
12
    // $retval=$row["show_db_beauty_name"]; // Uncomment this and comment out the rest of the block to return to default behavior
13
         $retval=joinifnotempty(
14
             array($row["storage_name"],
15
             ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
16
             )
17
         );
18
     }
Copied!
​
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
1
case "storage":
2
    if ($row["db_id"]==-1
3
    || $g_settings["order_system"]=="fundp" // fundp wants all to see the exact location
4
    || $db_user == ROOT
5
    || $db_user == "special_person") { //this is to add some person to be able to the see the location
6
        $retval=joinifnotempty(
7
            array($row["storage_name"],
8
            ifnotempty(s("compartment_short")."&nbsp;",$row["compartment"]) // Fach X
9
            )
10
        );
11
    }
12
    else {
13
        $retval=$row["show_db_beauty_name"];
14
    }
15
    break;
Copied!
​
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:
1
mysql -u root -p
Copied!
    2. Show all databases and choose the correct database to modify (in this example “test” is the name of the database):
1
show databases;
2
use test;
Copied!
    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):
1
CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));
Copied!
    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):
1
LOAD DATA LOCAL INFILE '/path-to-file/test.csv' 
2
INTO TABLE temp_storage
3
    FIELDS TERMINATED BY ','
4
    LINES TERMINATED BY '\n'
5
    IGNORE 1 LINES;
Copied!
    5. Update storage table
1
UPDATE storage
2
    INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
3
SET storage.storage_barcode=temp_storage.barcode;
Copied!
   6. Remove the temporary table
1
DROP TEMPORARY TABLE temp_storage;
Copied!
​
If you want to use phpMyAdmin,
1.
Copy csv files into /var/lib/mysql
2.
Open phpMyAdmin console.
3.
Choose the right databases
1
use test;
Copied!
    4. Run ALL of these commands AT ONCE (Ctrl-Enter to run command(s) in phpMyAdmin console)
1
CREATE TEMPORARY TABLE temp_storage (storage text, barcode varbinary(20));
2
​
3
LOAD DATA INFILE '/var/lib/mysql/test.csv' INTO TABLE temp_storage
4
FIELDS TERMINATED BY ','
5
LINES TERMINATED BY '\n'
6
IGNORE 1 LINES;
7
​
8
UPDATE storage
9
INNER JOIN temp_storage on temp_storage.storage=storage.storage_name
10
SET storage.storage_barcode=temp_storage.barcode;
11
​
12
DROP TEMPORARY TABLE temp_storage;
Copied!
​