• Author / Uploaded
• chemwolit

Citation preview



Guide: Setup uniCenta with MySQL database and import Categories, Taxes and Products using CSV files Lot of this is personal preference, but hope it will be useful to some users.

UniCenta, MySQL Server and Database Setup: (1). Install Java, Visual C++ 2010 and .NET Framework 4 Client Java (JRE): http://www.oracle.com/technetwork/java/javase/downloads/index.html Visual C++: http://www.microsoft.com/en-au/download/details.aspx?id=5555 .NET Framework: http://www.microsoft.com/enus/download/details.aspx?id=24872 (2). Install MySQL Community Server to "C:\POS\MySQL\" http://www.mysql.com/downloads/mysql/ Use Custom and only change Location Launch the MySQL Instance Configuration Wizard Standard Configuration Install As Windows Service "MySQL" and Launch * Modify Security Settings: Enter own password for root and Enable root access from remote machines (3). Install MySQL Workbench to "C:\POS\MySQL_Workbench\" http://www.mysql.com/downloads/workbench/ * Complete install (4). MySQL Workbench starts: o o o

Double-click "Local instance MySQL" and enter password Right-click existing database and "Drop Schema..." Create yourdatabase by selecting the "Create a new schema..." (3rd icon along), enter your database name and apply

(5). Install uniCenta to "C:\POS\uniCenta_oPOS\" (6). Extract "mysql-connector-java-5.1.22-bin.jar" into "C:\POS\uniCenta_oPOS\lib\" http://www.mysql.com/downloads/connector/j/ (7). Go to "C:\POS\uniCenta_oPOS\" and run "configure.bat"; Driver library: C:\POS\uniCenta_oPOS\mysql-connector-java-5.1.22-bin.jar Driver class: com.mysql.jdbc.Driver URL: jdbc:mysql://localhost:3306/yourdatabase User: root

Password: yourpass + Modify other settings to your requirements, Save, close and start uniCenta. Select ‘Yes’ to creating (filling) the new database *Note: If error with JAVA (Windows cannot find ‘javaw’: Edit the system Environment Variables -> Add to the Path variable; 64bit Windows: ;C:\Program Files (x86)\Java\jre7\bin 32bit Windows: ;C:\Program Files\Java\jre7\bin (Optional): Install iReport (iReport version should match "jasperreports-#.#.#.jav" in "uniCenta_oPOS\lib\") to "C:\POS\iReport\" Install Programmer’s Notepad

CSV IMPORT EXAMPLES

Importing CSV files: What is a CSV file? Read http://en.wikipedia.org/wiki/Comma-separated_values In what order would I need to import? If you want to alter taxes, categories and products via csv; 4. 5. 6. 7.

o o

o

Tax Categories Taxes Categories Products (*) Note: that if you only want to import products, you can just check current tax and category ID's and use those. The CSV may NOT contain a top line header For large (1000+ lines/rows) import: In MySQL Workbench disable the 1000 row limit; On the menu bar visit Edit -> Preferences. Jump to the SQL Queries tab. In the Query Results section at the bottom untick the Limit Rows option These examples use GST (10% Australian GST rate) and two categories with two levels of sub categories each. They are just examples and you will want to customise all to your own requirements.

(1). Tax Categories (taxcategories) Import: 1A. Prepare your CSV (notepad, excel or other): GST,GST

1B. Import: load data local infile 'C:/temp/taxcategories.csv' into table taxcategories fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME) -> Query -> Execute current statement (2). Taxes (taxes) Import: 2A. Prepare your CSV (notepad, excel or other): GST,GST,GST,NULL,NULL,0.1,,NULL 2B. Import: load data local infile 'C:/temp/taxes.csv' into table taxes fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME,CATEGORY,CUSTCATEGORY,PARENTID,RATE,RATECAS CADE,RATEORDER) -> Query -> Execute current statement (3). Categories (categories) Import: 3A. Prepare your CSV (notepad, excel or other): Category001,Category001,NULL,NULL Sub001-001,Sub001-001,Category001,NULL SubSub001-001-001,SubSub001-001-001,Sub001-001,NULL SubSub002-001-001,SubSub002-001-001,Sub001-001,NULL Sub002-001,Sub002-001,Category001,NULL SubSub001-002-001,SubSub001-002-001,Sub002-001,NULL SubSub002-002-001,SubSub002-002-001,Sub002-001,NULL Category002,Category002,NULL,NULL Sub001-002,Sub001-002,Category002,NULL SubSub001-001-002,SubSub001-001-002,Sub001-002,NULL SubSub002-001-002,SubSub002-001-002,Sub001-002,NULL Sub002-002,Sub002-002,Category002,NULL SubSub001-002-002,SubSub001-002-002,Sub002-002,NULL SubSub002-002-002,SubSub002-002-002,Sub002-002,NULL 3B. Import: load data local infile 'C:/temp/categories.csv' into table categories fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME,PARENTID,IMAGE) -> Query -> Execute current statement (4). Products (products) Import: * This is just an example. Barcode-000001 is not an EAN13 barcode and reports

wouldn't generate a barcode. 123456789012 is a valid option for EAN13 barcode. 4A. Prepare your CSV (notepad, excel or other): ID-000001,Reference-000001,Barcode-000001,EAN13,Name000001,1.15,2.35,Category001,GST,NULL,NULL,NULL,NULL,,,,,,,Properti es,ButtonText-000001 ID-000002,Reference-000002,Barcode-000002,EAN13,Name000002,2.15,4.35,Sub001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000002 ID-000003,Reference-000003,Barcode-000003,EAN13,Name000003,3.15,6.35,SubSub001-001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000003 ID-000004,Reference-000004,Barcode-000004,EAN13,Name000004,4.15,8.35,SubSub002-001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000004 ID-000005,Reference-000005,Barcode-000005,EAN13,Name000005,5.15,10.35,Sub002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000005 ID-000006,Reference-000006,Barcode-000006,EAN13,Name000006,6.15,12.35,SubSub001-002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000006 ID-000007,Reference-000007,Barcode-000007,EAN13,Name000007,7.15,14.35,SubSub002-002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000007 ID-000008,Reference-000008,Barcode-000008,EAN13,Name000008,8.15,16.35,Category002,GST,NULL,NULL,NULL,NULL,,,,,,,Propert ies,ButtonText-000008 ID-000009,Reference-000009,Barcode-000009,EAN13,Name000009,9.15,18.35,Sub001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000009 ID-000010,Reference-000010,Barcode-000010,EAN13,Name000010,10.15,20.35,SubSub001-001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000010 ID-000011,Reference-000011,Barcode-000011,EAN13,Name000011,11.15,22.35,SubSub002-001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000011 ID-000012,Reference-000012,Barcode-000012,EAN13,Name000012,12.15,24.35,Sub002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000012 ID-000013,Reference-000013,Barcode-000013,EAN13,Name-

000013,13.15,26.35,SubSub001-002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000013 ID-000014,Reference-000014,Barcode-000014,EAN13,Name000014,14.15,28.35,SubSub002-002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000014 -OR- for those that use Variable Price Products by john l (https://sourceforge.net/p/unicentaopos/discussion/1126901/thread/539551a5/) ID-000001,Reference-000001,Barcode-000001,EAN13,Name000001,1.15,2.35,Category001,GST,NULL,NULL,NULL,NULL,,,,,,,Properti es,ButtonText-000001,0 ID-000002,Reference-000002,Barcode-000002,EAN13,Name000002,2.15,4.35,Sub001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000002,0 ID-000003,Reference-000003,Barcode-000003,EAN13,Name000003,3.15,6.35,SubSub001-001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000003,0 ID-000004,Reference-000004,Barcode-000004,EAN13,Name000004,4.15,8.35,SubSub002-001001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000004,0 ID-000005,Reference-000005,Barcode-000005,EAN13,Name000005,5.15,10.35,Sub002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000005,0 ID-000006,Reference-000006,Barcode-000006,EAN13,Name000006,6.15,12.35,SubSub001-002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000006,0 ID-000007,Reference-000007,Barcode-000007,EAN13,Name000007,7.15,14.35,SubSub002-002001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000007,0 ID-000008,Reference-000008,Barcode-000008,EAN13,Name000008,8.15,16.35,Category002,GST,NULL,NULL,NULL,NULL,,,,,,,Propert ies,ButtonText-000008,0 ID-000009,Reference-000009,Barcode-000009,EAN13,Name000009,9.15,18.35,Sub001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000009,0 ID-000010,Reference-000010,Barcode-000010,EAN13,Name000010,10.15,20.35,SubSub001-001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000010,0 ID-000011,Reference-000011,Barcode-000011,EAN13,Name-

000011,11.15,22.35,SubSub002-001002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000011,0 ID-000012,Reference-000012,Barcode-000012,EAN13,Name000012,12.15,24.35,Sub002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000012,0 ID-000013,Reference-000013,Barcode-000013,EAN13,Name000013,13.15,26.35,SubSub001-002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000013,0 ID-000014,Reference-000014,Barcode-000014,EAN13,Name000014,14.15,28.35,SubSub002-002002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,ButtonText000014,0 4B. Import: load data local infile 'C:/temp/products.csv' into table products fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,REFERENCE,CODE,CODETYPE,NAME,PRICEBUY,PRICESELL,CA TEGORY,TAXCAT,ATTRIBUTESET_ID,STOCKCOST,STOCKVOLUME ,IMAGE,ISCOM,ISSCALE,ISKITCHEN,PRINTKB,SENDSTATUS,ISSER VICE,ATTRIBUTES,DISPLAY) -OR- for Variable Price Product tables... load data local infile 'C:/temp/products.csv' into table products fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,REFERENCE,CODE,CODETYPE,NAME,PRICEBUY,PRICESELL,CA TEGORY,TAXCAT,ATTRIBUTESET_ID,STOCKCOST,STOCKVOLUME ,IMAGE,ISCOM,ISSCALE,ISKITCHEN,PRINTKB,SENDSTATUS,ISSER VICE,ATTRIBUTES,DISPLAY,isvprice) -> Query -> Execute current statement 5-12-12 (updated for v3.02) 19-01-13 (added variable price product details)

Last edit: Andrew 2013-01-19

Link o

 uniCenta 2012-11-26

Andrew Thanks for doing that. Very useful for lots of people. Regards Jack

Link o

 Andrew 2013-02-23

I've been looking at improving my CSV import capability. Following john l's 'CSV Import' work at http://sourceforge.net/p/unicentaopos/discussion/1126901/thread/e3b8777f/. Found HeidiSQL MySQL client to be helpful in importing products. Update here method to import only columns you wish into product table: CSV template.csv (you can leave the top line header when using the following MySQL script): ID, REFERENCE, CODE, NAME, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, DISPLAY ID-000001,Reference-000001,Barcode-000001,Name000001,1.15,2.35,000,000,ButtonText-000001 ID-000002,Reference-000002,Barcode-000002,Name000002,2.15,4.35,000,000,ButtonText-000002

mySQL script, don't forget to change CSV name, path and DatabaseName: LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\CSV template.csv' REPLACE INTO TABLE `DatabaseName`.`products` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `REFERENCE`, `CODE`, `NAME`, `PRICEBUY`, `PRICESELL`, `CATEGORY`, `TAXCAT`, `DISPLAY`);

Obviously we can recycle and use other columns such as NAME for ID and DISPLAY. Can be achieved easily using Excel copy and paste cells. Furthermore: A. Tax Categories (Australian GST 10% example) ID, NAME GST,GST

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\taxcat template.csv' REPLACE INTO TABLE `DatabaseName`.`taxcategories` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`);

B. Taxes ID, NAME, CATEGORY, RATE GST,GST,GST,0.1

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\taxes template.csv' REPLACE INTO TABLE `DatabaseName`.`taxes` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`, `CATEGORY`, `RATE`);

C. Categories ID, NAME, PARENTID Category001,Category001,NULL Sub001-001,Sub001-001,Category001 Sub002-001,Sub002-001,Category001 Category002,Category002,NULL Sub001-002,Sub001-002,Category002 Sub002-002,Sub002-002,Category002

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\cat template.csv' REPLACE INTO TABLE `DatabaseName`.`categories` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`, `PARENTID`);

D. Products ID, REFERENCE, CODE, NAME, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, DISPLAY ID-000001,Reference-000001,Barcode-000001,Name000001,1.15,2.35,Sub001-001,GST,ButtonText-000001 ID-000002,Reference-000002,Barcode-000002,Name000002,2.15,4.35,Sub001-002,GST,ButtonText-000002

-

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\CSV template.csv' REPLACE INTO TABLE `DatabaseName`.`products` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `REFERENCE`, `CODE`, `NAME`, `PRICEBUY`, `PRICESELL`, `CATEGORY`, `TAXCAT`, `DISPLAY`);