ITrack/Pro/DSI Integration

Exporting from ITrack Pro to DSI

Batch file and sql script in SVN: ITrack\ITrackPro\Utilities\DSIExport\

How it works

A batch file is ran(Probably want it to happen via a scheduled task) that does the following:

1. Connects to the users itrackpro database server
2. Runs a sql scripts which gets available and on hold inventory and writes it out to a csv file
3. Uploads the file to a directory on DSI's FTP server using ncftp
4. Deletes the local copy of the csv file

The batch file

"<path to mysql directory>\bin\mysql.exe" -u <MySQL_Username> --password=<MySQL_Password> itrackpro < dsi_export.sql
ncftpput -u <FTP_Server_Username> -p <FTP_Server_Password> <FTP_Server_Name_Or_IP> <path on remote ftp server to put the file> "<local DSI Export directory>\output.csv"
DEL "<local server DSI Export directory>\output.csv"

1. Make sure the line "DEL output.txt" is pointed to the output file in the first step.
2. In the second line replace <user> with the username and <password> with the password used to access the database. Since this is stored in plaintext it'll be worthwhile to create a MySQL user account only SELECT permissions on the specific database and global FILE permissions.
3. If mysql is not in the system's PATH, the second line of the bat file will have to be changed to point to the mysql executable using an absolute path.
4. Make a scheduled task that runs the batch file.

The sql script

SELECT partuse.part AS parttype, 
inventory.partnum AS SKU, 
inventory.tagnum AS tagnumber,
inventory.typenum, 
inventory.stocknum, 
inventory.vinnum AS vin, 
inventory.make, 
inventory.model, 
inventory.year, 
inventory.bodystyle, 
inventory.location, 
inventory.description, 
inventory.core AS coreprice, 
inventory.interchangenum, 
inventory.status, 
inventory.suggestedprice AS retailprice, 
inventory.bottomprice AS wholesaleprice, 
inventory.replenish, 
inventory.deplete, 
inventory.quantity, 
inventory.dateentered, 
inventory.label1, 
inventory.data1, 
inventory.label2, 
inventory.data2, 
inventory.label3, 
inventory.data3, 
inventory.label4, 
inventory.data4, 
inventory.cost, 
inventory.pmanufacturer AS partmanufacturer, 
inventory.pmodel AS partmodel, 
inventory.minquantity, 
inventory.maxquantity, 
inventory.taxable, 
inventory.oemnum, 
inventory.condition, 
inventory.serialnum, 
inventory.side, 
inventory.category, 
inventory.listprice, 
inventory.notes, 
inventory.parentpartnum, 
inventory.weight, 
invmaster.trackingnum AS trackingnumber 
FROM inventory
LEFT JOIN partuse ON inventory.typenum = partuse.typenum
LEFT JOIN invmaster ON inventory.stocknum = invmaster.stocknum
LEFT JOIN locations ON inventory.partnum = locations.partnum
WHERE inventory.status IN('A', 'H')
INTO OUTFILE '<PATH>\\output.csv'
FIELDS
TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES
TERMINATED BY '\n';

Note: Be sure to replace <PATH> in the line "INTO OUTFILE '<PATH>\\output.csv'" with where the output file needs to go. Note that this path is relative to mysql not the bat file so you may want to use an absolute path. The filename can also be changed if wished.

Importing from DSI to ITrack Pro

Batch file and DSI Import application in SVN: ITrack\ITrackPro\Utilities\DSIImport\

How it works

A batch file is ran via a scheduled task that does the following:

1. Using a command line FTP client, connects to a DSI provided FTP server
   1. Downloads all .xml and .XML files in a certain directory on the FTP server and puts them in the DSI Import application installation directory
2. Runs the ISoft-built DSI Import application. (You will most likely need a dev to compile you the most recent version. Also this application requires libmysql.dll)
   1. Connects to a MySQL server and ITrack Pro database using database connection info that must be supplied in a host.ini file(this can be copied from the customer's ITrack Pro client software installation).
3. When the application is ran it scans its own directory for any & all .xml file(s)
4. It then attempts to import all of the information(invoices, invoice line items, customers & it depletes quantity of inventory records) from all of the xml files
5. Upon successful import it then deletes the .xml files from the DSI Import application directory

The batch file

ncftpget -DD -u <FTP_Username> -p <FTP_Password> <server name or IP address> "<Local directory of DSI Import Application>" <path on server where the xml file will be>/*.xml
ncftpget -DD -u <FTP_Username> -p <FTP_Password> <server name or IP address> "<Local directory of DSI Import Application>" <path on server where the xml file will be>/*.XML
"<Local directory with DSI Import Application>\DSIImport.exe"
exit

Note: <path on server where the xml file will be> is relative to the root. In many cases this is something like /ISOFT-IN/

The DSI Import application

The DSI Importer currently

1. Connects to an ITrack Pro database using information from a host.ini file in its directory
2. Opens all .xml files in its directory
3. For each one of those files, it:
   1. Reads in invoices based on the specs we were given by DSI long ago
   2. For each one of the invoices that it reads in, it:
      1. Creates a customer if the DSI customer on that invoice hasn't been seen before
      2. Creates a new invoice using the f_create_invoice database function
      3. For every lineitem on that imported invoice, it
         1. Depletes quantity from inventory by looking for a part with a tag number matching the part number that they typed in to DSI for that lineitem
         2. Creates a new lineitem using the f_create_lineitem function
      4. Creates a new lineitem on that invoice (using f_create_lineitem) to store the tax amount
   3. Deletes the file
4. Exits returning 0 if everything went fine, non-zero if some error happened