#For logging
from Spotfire.Dxp.Application.Visuals import HtmlTextArea
from datetime import datetime
from Spotfire.Dxp.Data.Import import DatabaseDataSource, DatabaseDataSourceSettings 
import time
from Spotfire.Dxp.Application.Visuals import *
from Spotfire.Dxp.Application.Visuals.Miniatures import GraphicalTable
from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers

DatabaseName = "xxx"
DataSourceSettings = "Server=localhost\sql2016;Database=" + DatabaseName + ";Trusted_Connection=True" 



for page in Document.Pages:
	if page.Title <> "0_Utils" :
		for visual in page.Visuals:
			visualization = visual.As[Visualization]()
			if visualization != None:
				print " "
				print " "
				#print 'Page: ' + page.Title + '; Visual: ' + visual.Title

			
				v1_desc = ""
				v1 = visual.As[VisualContent]()
				v1_desc = v1.Data.WhereClauseExpression
				viz_xaxis_expression = ""
				viz_yaxis_expression =""
				viz_measure_expression = ""

				if visualization.TypeId == VisualTypeIdentifiers.GraphicalTable:
					viz_type = "Graphical Table"		
					viz = visual.As[GraphicalTable]()
					
					#v1_desc = v1.Data.WhereClauseExpression
					#print '	DataLimit: ' + v1_desc
					#viz_xaxis_expression = v1.ColumnAxis.Expression
					viz_yaxis_expression = v1.RowAxis.Expression
					for col in viz.Columns:
						if col.Visualization.TypeId == VisualTypeIdentifiers.CalculatedValueMiniatureVisualization:
							vExp = col.Visualization.ValueAxis.Expression
						elif col.Visualization.TypeId == VisualTypeIdentifiers.IconMiniatureVisualization:
							 vExp = col.Visualization.IconAxis.Expression
						elif col.Visualization.TypeId == VisualTypeIdentifiers.SparklineMiniatureVisualization:
							 vExp = col.Visualization.YAxis.Expression
						elif col.Visualization.TypeId == VisualTypeIdentifiers.BulletGraphMiniatureVisualization:
							 vExp = col.Visualization.ValueAxis.Expression
						
						sqlCommand = "insert into page_objects(page_title, visual_type, visual_title, data_limit, XAxis_expression, YAxis_expression) values ('" + page.Title + "','" + viz_type + "','" + visual.Title  + "','" + v1_desc + "','" + vExp  +  "','" + viz_yaxis_expression +  "')"
						dbsettings = DatabaseDataSourceSettings("System.Data.SqlClient", DataSourceSettings , sqlCommand) 
						ds = DatabaseDataSource(dbsettings)
						newDataTable = Document.Data.Tables.Add("temp",ds)
						Document.Data.Tables.Remove(newDataTable)


				elif visualization.TypeId == VisualTypeIdentifiers.CrossTable: 
					viz_type = "Cross Table"
					viz_xaxis_expression = v1.ColumnAxis.Expression
					viz_yaxis_expression = v1.RowAxis.Expression
					viz_measure_expression = v1.MeasureAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.Table: 	
					viz_type = "Table"
					#viz_expression = v1.XAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.BarChart: 	
					viz_type = "BarChart"
					viz_xaxis_expression = v1.XAxis.Expression
					viz_yaxis_expression = v1.YAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.LineChart: 	
					viz_type = "LineChart"
					viz_xaxis_expression = v1.XAxis.Expression
					viz_yaxis_expression = v1.YAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.PieChart: 	
					viz_type = "PieChart"
					#viz_expression = v1.XAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.ScatterPlot: 	
					viz_type = "ScatterPlot"
					viz_xaxis_expression = v1.XAxis.Expression
					viz_yaxis_expression = v1.YAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.CombinationChart:
					viz_type = "CombinationChart"
					viz_xaxis_expression = v1.XAxis.Expression
					viz_yaxis_expression = v1.YAxis.Expression
					#viz_measure_expression = v1.MeasureAxis.Expression
				elif visualization.TypeId == VisualTypeIdentifiers.TextArea:
					#need to iterate through all CalculatedValue controls...not having so much luck here....
					viz_type = "textArea"
					
					

				if visualization.TypeId <> VisualTypeIdentifiers.GraphicalTable:
					sqlCommand = "insert into page_objects(page_title, visual_type, visual_title, data_limit, XAxis_expression, YAxis_expression, measure_expression) values ('" + page.Title + "','" + viz_type + "','" + visual.Title  + "','" + v1_desc + "','" + viz_xaxis_expression  +  "','" + viz_yaxis_expression + "','" + viz_measure_expression + "')"
					dbsettings = DatabaseDataSourceSettings("System.Data.SqlClient", DataSourceSettings , sqlCommand) 
					ds = DatabaseDataSource(dbsettings)
					newDataTable = Document.Data.Tables.Add("temp",ds)
					Document.Data.Tables.Remove(newDataTable)

				#print "Page: " + page.Title + "; Visual: " + visual.Title + "; VisualType: " + viz_type + "; XAxisExpression: " + viz_xaxis_expression

			
#			if visualization.TypeId == VisualTypeIdentifiers.GraphicalTable:
#				graphicalTable = visual.As[Visualization]()
#				if graphicalTable != None:
#					for column in graphicalTable.Columns:
#						if not column.Visualization.IsConfigurationValid():
#							appendLogMessage('Page:  ' + page.Title + '  | GraphTable Visual:  ' + visual.Title + '  | Column:  ' + column.Title)
#							

#identify graphical tables for which script above does not work
#for page in Document.Pages:
#	for visual in page.Visuals:
#		visualization = visual.As[Visualization]()
#		if visualization != None:
#			# print visualization
#			if visualization.TypeId == VisualTypeIdentifiers.GraphicalTable:
#				appendLogMessage(page.Title + ' : ' + visual.Title)
#