ITrack/Pro/DSI Integration - Revision history

Ghagemoser: /* The Python Script */

2023-11-20T20:47:20Z

The Python Script

← Older revision		Revision as of 15:47, 20 November 2023
Line 38:		Line 38:
	conn = mysql.connector.connect(		conn = mysql.connector.connect(
	host=<hostname>,		host=<hostname>,
	database=<database?,		database=<database>,
	user=<user>,		user=<user>,
	password=<password>,		password=<password>,
Line 62:		Line 62:
	curs.execute(query)		curs.execute(query)
	#Set up csv write location and parameters		#Set up csv write location and parameters
	with open('~~results~~.csv', 'w', newline='') as csvfile:		with open('output.csv', 'w', newline='') as csvfile:
	filewriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)		filewriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
	#Add column headers		#Add column headers

Ghagemoser: /* The Python Script */

2023-11-17T15:34:52Z

The Python Script

← Older revision		Revision as of 10:34, 17 November 2023
Line 37:		Line 37:
	#Enter connection details		#Enter connection details
	conn = mysql.connector.connect(		conn = mysql.connector.connect(
	host=~~'hosteddb.isoftdata.com'~~,		host=<hostname>,
	database=~~'427662'~~,		database=<database?,
	user=~~'lund_user'~~,		user=<user>,
	password=~~'3huSusepha'~~,		password=<password>,
	port=~~"3306"~~)		port=<port>)

	#Confirm that connection is successful		#Confirm that connection is successful

Ghagemoser: /* The Python Script */

2023-11-16T21:41:49Z

The Python Script

← Older revision		Revision as of 16:41, 16 November 2023
Line 24:		Line 24:
	===The Python Script===		===The Python Script===
	New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.		New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
	The ~~select~~ portion of the old sql script is used in the query run by the python script		*The SELECT portion of the old sql script is used in the query run by the python script

	<source lang="python">		<source lang="python">

Ghagemoser: /* The Python Script */

2023-11-16T21:16:01Z

The Python Script

← Older revision		Revision as of 16:16, 16 November 2023
Line 24:		Line 24:
	===The Python Script===		===The Python Script===
	New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.		New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
	#The select portion of the old sql script is used in the query run by the python script		The select portion of the old sql script is used in the query run by the python script

	<source lang="python">		<source lang="python">

Ghagemoser: /* The Python Script */

2023-11-16T21:15:29Z

The Python Script

← Older revision		Revision as of 16:15, 16 November 2023
Line 24:		Line 24:
	===The Python Script===		===The Python Script===
	New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.		New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
	The ~~Python script runs the~~ select portion of ~~this~~ script.		#The select portion of the old sql script is used in the query run by the python script

	<source lang="python">		<source lang="python">

Ghagemoser: /* The batch file */

2023-11-16T21:13:27Z

The batch file

← Older revision		Revision as of 16:13, 16 November 2023
Line 22:		Line 22:
	#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.		#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~~=== -s		===The Python Script===
	New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.		New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
	The Python script runs the select portion of this script.		The Python script runs the select portion of this script.

	<source lang="~~sql~~">		<source lang="python">
	SELECT partuse.part AS parttype,		import mysql.connector
	inventory.partnum AS SKU,		from mysql.connector import Error
	inventory.tagnum AS tagnumber,		import csv
	inventory.typenum,
	inventory.stocknum,
	inventory.vinnum AS vin,		def connect():
	inventory.make,		conn = None
	inventory.model,		try:
	inventory.year,		#Enter connection details
	inventory.bodystyle,		conn = mysql.connector.connect(
	inventory.location,		host='hosteddb.isoftdata.com',
	inventory.description,		database='427662',
	inventory.core AS coreprice,		user='lund_user',
	inventory.interchangenum,		password='3huSusepha',
	inventory.status,		port="3306")
	inventory.suggestedprice AS retailprice,
	inventory.bottomprice AS wholesaleprice,		#Confirm that connection is successful
	inventory.replenish,		if conn.is_connected():
	inventory.deplete,		log = open("log.txt", "a")
	inventory.quantity,		log.write("\n")
	inventory.dateentered,		log.write("Connected to MySQL database")
	inventory.label1,		log.close()
	inventory.data1,		#Add to logs
	inventory.label2,		except Error as e:
	inventory.data2,		log = open("log.txt", "a")
	inventory.label3,		log.write("\n")
	inventory.data3,		log.write(e)
	inventory.label4,		log.close()
	inventory.data4,		finally:
	inventory.cost,		if conn is not None and conn.is_connected():
	inventory.pmanufacturer AS partmanufacturer,		curs = conn.cursor()
	inventory.pmodel AS partmodel,		query = '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, replace(inventory.description, "\r\n", "") as 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, replace(inventory.notes, "\r\n", "") as 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");'
	inventory.minquantity,		count = 0
	inventory.maxquantity,		curs.execute(query)
	inventory.taxable,		#Set up csv write location and parameters
	inventory.oemnum,		with open('results.csv', 'w', newline='') as csvfile:
	inventory.condition,		filewriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
	inventory.serialnum,		#Add column headers
	inventory.side,		filewriter.writerow(['parttype', 'SKU', 'tagnumber', 'typenum', 'stocknum', 'vin', 'make', 'mdoel', 'year', 'bodystyle', 'location', 'description', 'coreprice', 'interchangenum', 'status', 'retailprice', 'wholesaleprice', 'replenish', 'deplete', 'quantity', 'dateentered', 'label1', 'data1', 'label2', 'data2', 'label3', 'data3', 'label4', 'data4', 'cost', 'partmanufacturer', 'partmodel', 'minquantity','maxquantity', 'taxable', 'oemnum', 'condition', 'serialnum', 'side', 'category', 'listprice', 'notes', 'parentpartnum', 'weight', 'trackingnumber'])
	inventory.category,
	inventory.listprice,		#loop through each row and add append the data to the csv
	inventory.notes,		for (parttype, SKU, tagnumber, typenum, stocknum, vin, make, mdoel, year, bodystyle, location, description, coreprice, interchangenum, status, retailprice, wholesaleprice, replenish, deplete, quantity, dateentered, label1, data1, label2, data2, label3, data3, label4, data4, cost, partmanufacturer, partmodel, minquantity, maxquantity, taxable, oemnum, condition, serialnum, side, category, listprice, notes, parentpartnum, weight, trackingnumber) in curs:
	inventory.parentpartnum,		filewriter.writerow([parttype, SKU, tagnumber, typenum, stocknum, vin, make, mdoel, year, bodystyle, location, description, coreprice, interchangenum, status, retailprice, wholesaleprice, replenish, deplete, quantity, dateentered, label1, data1, label2, data2, label3, data3, label4, data4, cost, partmanufacturer, partmodel, minquantity, maxquantity, taxable, oemnum, condition, serialnum, side, category, listprice, notes, parentpartnum, weight, trackingnumber])
	inventory.weight,
	invmaster.trackingnum AS trackingnumber		count = count + 1
	FROM inventory		log = open("log.txt", "a")
	LEFT JOIN partuse ON inventory.typenum = partuse.typenum		log.write("\n")
	LEFT JOIN invmaster ON inventory.stocknum = invmaster.stocknum		log.write(str(count)+ ' rows were added to results.csv')
	LEFT JOIN locations ON inventory.partnum = locations.partnum		log.close()
	WHERE inventory.status IN('A', 'H')
	~~INTO OUTFILE~~ '~~<PATH>\\output~~.csv'		conn.close()
	~~FIELDS~~
	~~TERMINATED BY~~ ','
	~~ENCLOSED BY~~ '"'		if __name__ == '__main__':
	~~ESCAPED BY~~ '\\'		connect()
	~~LINES~~
	~~TERMINATED BY~~ '\n';
	</source>		</source>

	'''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.		'''Note:'''

	===Rock and Dirt Export===		===Rock and Dirt Export===

Ghagemoser: /* The sql script */

2023-11-16T21:11:34Z

The sql script

← Older revision		Revision as of 16:11, 16 November 2023
Line 22:		Line 22:
	#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.		#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===		===The sql script=== -s
	New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.		New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
	The Python script runs the select portion of this script.		The Python script runs the select portion of this script.

Ghagemoser: /* The sql script */

2023-11-16T21:10:59Z

The sql script

← Older revision		Revision as of 16:10, 16 November 2023
Line 23:		Line 23:

	===The sql script===		===The sql script===
			New exporter doesn't use a sql script because it is blocked by the FILE permission as well as the secure file priv flag in the mysql config.
			The Python script runs the select portion of this script.

	<source lang="sql">		<source lang="sql">
	SELECT partuse.part AS parttype,		SELECT partuse.part AS parttype,

Ghagemoser: /* Exporting from ITrack Pro to DSI */

2023-11-16T21:09:21Z

Exporting from ITrack Pro to DSI

← Older revision		Revision as of 16:09, 16 November 2023
Line 4:		Line 4:
	===How it works===		===How it works===
	A batch file is ran(Probably want it to happen via a scheduled task) that does the following:		A batch file is ran(Probably want it to happen via a scheduled task) that does the following:
	#~~Connects~~ to the ~~users itrackpro database server~~		#Navigates to the DSI export directory
	#Runs a ~~sql scripts which gets available~~ and ~~on hold inventory and writes it out~~ to a csv ~~file~~		#Runs a Python script(The script connects to the database and runs a query to get the data, then loads that data into a csv)
	#Uploads the file to a directory on DSI's FTP server using [ftp://ftp.ncftp.com/ncftp/binaries/Setup%20NcFTP%203.2.5.msi ncftp]		#Uploads the csv file to a directory on DSI's FTP server using [ftp://ftp.ncftp.com/ncftp/binaries/Setup%20NcFTP%203.2.5.msi ncftp]
	#Deletes the local copy of the csv file		#Deletes the local copy of the csv file

	===The batch file===		===The batch file===
	<source lang="dos">		<source lang="dos">
	"<path to ~~mysql~~ directory>~~\bin\mysql.exe~~" ~~-u <MySQL_Username> --password=<MySQL_Password> itrackpro < dsi_export~~.~~sql~~		"<path to dsi export directory>"
			python DSI_Exporter.py
	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"		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"		DEL "<local server DSI Export directory>\output.csv"
Line 17:		Line 18:

	#Make sure the line "DEL output.txt" is pointed to the output file in the first step.		#Make sure the line "DEL output.txt" is pointed to the output file in the first step.
	#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.
	~~#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.~~
	#Make a scheduled task that runs the batch file.		#Make a scheduled task that runs the batch file.
	~~#Make sure to use // (double backslashes) in your path names in the .sql script, so they escape properly.~~
	#If you are given an ftp address in the form of <nowiki>ftp://</nowiki><ftp.something.com, remove the leading <nowiki>ftp://</nowiki>, as it's unnecessary and will confuse the exporter.		#If you are given an ftp address in the form of <nowiki>ftp://</nowiki><ftp.something.com, remove the leading <nowiki>ftp://</nowiki>, as it's unnecessary and will confuse the exporter.
	#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.		#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.

Codeman: Adding some additional info about the DSI integration

2018-06-04T14:15:45Z

Adding some additional info about the DSI integration

← Older revision		Revision as of 09:15, 4 June 2018
Line 164:		Line 164:
	DELIMITER ;		DELIMITER ;
	</source>		</source>

			=== 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.

	[[Category:Integrations]]		[[Category:Integrations]]