Zetta to Powergold Asset Import

ZettaPowergoldAssetImport.py

"""
This script is designed to export all music from a specific Zetta category to a text file, ready for Powergold to import.
Written by Anthony Eden (https://mediarealm.com.au/)
"""

import os
import sys
import json
import pypyodbc 
import io
import time
import datetime

# Setup Zetta SQL DB Connection:
SQLConnectionString = 'DSN=ZettaDb;'
dbConn = pypyodbc.connect(SQLConnectionString)
cur = dbConn.cursor()

def listCategoriesByName(catName):
	categoriesQuery = '''
	SELECT CategoryID, StationID, AssetTypeID
	FROM [ZettaDB].[dbo].[Category]
	WHERE Category.Name = ?
	'''
	
	cur.execute(categoriesQuery, [str(catName)])
	cats = []
	
	for d in cur:
		cats.append({
			"CategoryID": d[0],
			"StationID": d[1],
			"AssetTypeID": d[2]
		})
	
	return cats

def listAssetsInCategory(categoryId):
	assetsQuery = '''
	SELECT
		Asset.AssetID,
		Asset.Title,
		Resource.Length,
		Asset.ThirdPartyID,
		AutoGen_StationSpecificDataReadonlyReferences.CalculatedPlayLength,
		(SELECT TOP 1 ArtistID FROM AssetToArtist WHERE AssetToArtist.AssetID = Asset.AssetID ORDER BY Sequence ASC) AS ArtistID_1,
		AutoGen_StationSpecificDataReadonlyReferences.IntroMaxDuration,
		Asset.AssetTypeID,
		AutoGen_StationSpecificDataReadonlyReferences.SeguePoint
		
	FROM
		Asset,
		Resource,
		AssetToResource,
		StationSpecificData,
		AutoGen_StationSpecificDataReadonlyReferences
	
	WHERE Asset.AssetID = AssetToResource.AssetID
	AND AssetToResource.isPrimaryResource = 1
	AND AssetToResource.ResourceID = Resource.ResourceID
	AND StationSpecificData.AssetId = Asset.AssetID
	AND AutoGen_StationSpecificDataReadonlyReferences.StationSpecificDataID = StationSpecificData.StationSpecificDataId
	
	
	AND StationSpecificData.CategoryID = ?
	
	ORDER BY Asset.AssetID DESC
	'''
	
	cur.execute(assetsQuery, [int(categoryId)])
	assets = []

	rows = cur.fetchall()
	for d in rows:
		try:
			artistId = int(d[5])
			artistName = getArtistName(artistId)
		except:
			artistId = 0
			artistName = ""
		
		if d[6] == None:
			introTime = 0
		else:
			introTime = d[6]

		if d[3] == None:
			thirdPartyId = ""
		else:
			thirdPartyId = d[3]
		
		# We can't always use the segue time, as it's not always set
		if d[8] is not None:
			# Use the duration as the duration
			calculatedPlayLength = d[8]
		elif d[4] is not None:
			# Use the Segue Point as the duration
			calculatedPlayLength = d[4]
		elif d[2] is not None:
			calculatedPlayLength = d[2]
		else:
			calculatedPlayLength = 0
		
		assets.append({
			"AssetID": d[0],
			"Title": d[1],
			"CalculatedPlayLength": calculatedPlayLength,
			"ThirdPartyID": thirdPartyId,
			"ArtistName": artistName,
			"IntroTime": introTime,
			"AssetTypeASCIICON": assetTypeIdToASCIICONFormat(d[7])
		})
	
	return assets

def getAssetArtists(assetId):
	assetArtistQuery = '''SELECT
		Artist.ArtistID,
		Artist.Name
	FROM Artist, AssetToArtist
	WHERE Artist.ArtistID = AssetToArtist.ArtistID
	AND AssetToArtist.AssetID = ?'''
	
	cur.execute(assetArtistQuery, [int(assetId)])
	artists = []
	
	for d in cur:
		artists.append({
			"ArtistID": d[0],
			"ArtistName": d[1]
		})
	
	return artists

def getArtistName(artistId):
	artistNameQuery = '''SELECT
		Artist.Name
	FROM Artist
	WHERE ArtistID = ?'''
	
	cur.execute(artistNameQuery, [int(artistId)])
	
	for d in cur:
		return d[0]
	
	return ""

def assetTypeIdToASCIICONFormat(assetId):
	if assetId == 1:
		return "SONG"
	elif assetId == 2:
		return "SPOT"
	elif assetId == 3:
		return "LINK"
	else:
		return "    "

def writePowergoldTextFile(filename, assets):
	f = open(filename, "w")
	
	for x in assets:
		line = x["AssetTypeASCIICON"][:4].ljust(4)
		line += x["Title"][:255].ljust(255)
		line += x["ArtistName"][:255].ljust(255)
		line += x["ThirdPartyID"][:8].ljust(8)
		line += str("%.0f" % round(x["CalculatedPlayLength"] * 1000, 0)).ljust(20)
		line += str(x["IntroTime"]).ljust(20)
		
		if x["AssetTypeASCIICON"][:4] == "SONG":
			# Stretch/squeeze marker
			line += "#"
		else:
			line += " "
			
		line += "\n"
		f.write(line)
	
	f.close()

if __name__ == "__main__":
	
	while True:
		try:
			allAssets = []
			for x in listCategoriesByName("Powergold Import"):
				allAssets.extend( listAssetsInCategory(x['CategoryID']) )
			
			writePowergoldTextFile("C:\\SCRIPTS\\ZettaImport.txt", allAssets)

			print allAssets
			
			time.sleep(20)
		except Exception, e:
			print "Exception!!!", e
			time.sleep(120)

Greetings a question and excuse my ignorance, I am just opting the zetta but I do not have the gselector but if I use the powergold, how would the integration between it with this script, how would it be implemented. is it perhaps a paid application