Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Zetta to Powergold Asset Import
"""
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)
@roomix2001

This comment has been minimized.

Copy link

@roomix2001 roomix2001 commented Jul 15, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment