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.
5. Make sure to use // (double backslashes) in your path names in the .sql script, so they escape properly.
6. If you are given an ftp address in the form of ftp://<ftp.something.com, remove the leading ftp://, as it's unnecessary and will confuse the exporter.
7. In the event the FTP site does not use a specific directory for uploading, substitute a blank string ("") for <path on remote ftp server to put the file>. Otherwise the exporter will get confused.

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.

Rock and Dirt Export

This script and batch file can work for exporting to other FTP sites, for example Rock & Dirt. The processes is functionally the same. You can find the pre-configured files for Rock and Dirt at svn\ITrackPro\Utilities\RockAndDirt. You will still need to create accounts and fill in the scripts with the system-appropriate paths.

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

Note: In order for the importer to work correctly, the function f_adjust_inventory MUST be present in their database or the importer will not correctly adjust inventory when a file is processed. If a customer using the DSI import does not have the function in their database, log in to the database under their normal mysql user and run the following query:

DELIMITER $$

CREATE FUNCTION `f_adjust_inventory`(in_partnum INT UNSIGNED, in_store SMALLINT UNSIGNED, in_quantity_change INT) RETURNS INT(11)
BEGIN
	DECLARE nNewQuantity INT;
	DECLARE cNewStatus CHAR(1);
	DECLARE nOldQuantity INT;
	DECLARE cOldStatus CHAR(1);
	DECLARE bReplenish ENUM('False','True');
	SELECT `quantity`, `status`, `replenish`
	INTO nOldQuantity, cOldStatus, bReplenish
	FROM inventory
	WHERE `partnum` = in_partnum AND `store` = in_store
	LOCK IN SHARE MODE;
	SET nNewQuantity = nOldQuantity + in_quantity_change;
	
	IF nOldQuantity <= 0 AND cOldStatus = 'S' AND nNewQuantity > 0 THEN
		SET cNewStatus = 'A';
	ELSEIF nNewQuantity <= 0 THEN
		SET cNewStatus = 'S';
	END IF;
	
	UPDATE `inventory`
	SET `quantity` = nNewQuantity, `status` = IFNULL(cNewStatus, `status`), `datemodified` = NOW()
	WHERE `partnum` = in_partnum AND `store` = in_store;
	
	RETURN nNewQuantity;
	
END $$
DELIMITER ;

Additional Info

• The way that the DSI Integration knows what part is referenced between the two systems, is that inventory.notes in ITrack will contain the part ID for DSI. If this information is missing, or if additional information is added to inventory.notes, then any sales in DSI will not be successfully updated in ITrack.
• Currently, the DSI system does not send over XML documents if it can not find any ITrack parts that were sold. It was requested that Ben Cramer change this so that DSI sends over XML files even if they are empty.
• Because the process automatically deletes the files once they are used, it is difficult to keep logs on what has happened. Archiving has been set up in the DSI Integration so that we now keep a log of all XML files that we have received.