single-multi-select-relational.transmogrifier.tsc #1

Option Explicit

' Used to "convert" a single select or relational to a multi
' Translations currently supported (or real soon now)

'							:	Destination Field type
'							:	Single	multi		single		multi       multi		multi
' Source field type	:	select	select	relational	relational  user		group	
'------------------  +  ------   ------   ----------  ----------  -------  -------
' single-select		:  no       yes      yes         yes			no			no
' multi-select			:  no       no       no          yes			no			no	
' single-relational	:  future   no       no          yes			no			no	
' multi-relational	:  no       future   no          no				no			no
' user					:	no			no			no				no				future	future


' What we need to do:
' Step 1. make sure field selectable values match
' If source is single/multi selection, iterate the selections.  If it's a relational,
' iterate the aux table records.  If the destingation is a single/multi selection,
' create selection values for the destination value.  If the destination is a
' relational field, then create a new tuple for each selectable item.  Replicate as 
' many of the fields of the selection value as we can.
'		ts_selections.ts_name -> usr_aux.ts_title
'		ts_selections.ts_value -> usr_aux.ts_value (if it exists).
' Aux tables destinations should be designed with all fields to default to some value.
' This will prevent errors when the script attempts to create new records without
' completely initializing all aux table fields.  

' Step 2. Update existing issue records so that new field issues have same selections
' as old issues.


' ** *********************************************************************
' ** $Id: //guest/paul_m_thompson/TeamTrack/single-multi-select-relational.transmogrifier.tsc#1 $
' ** $Change: 7352 $
' ** $DateTime: 2009/08/17 12:56:11 $
' ** $Author: paul_m_thompson $
' ** *********************************************************************




' Source field TS_ID or DB name.  If numeric, is interpreted as field TS_ID,
' otherwise interpreted as field DB name.  Field type must be single/multi-
' select or single/multi-relational.
Const SOURCE_FIELD=3158

' Destination field TS_ID or DB name.  If numeric, is interpreted as field TS_ID,
' otherwise interpreted as field DB name.  Field type must be multi-select or
' single/multi-relational.
Const DEST_FIELD=1037

' *********************************************************************************************

' These constants can (and should) be imported from System_Manifest_Constants.tsc

CONST TS_FIELDS_TABLEID=3
CONST TS_SELECTIONS_TABLEID=12
CONST TS_STATES_TABLEID=13
CONST TS_TRANSITIONS_TABLEID=16

CONST TS_TABLES_TABLEID=18
CONST TS_WORKFLOWS_TABLEID=34
CONST TS_PROJECTS_TABLEID=8
CONST TS_PROJECTSELECTIONS_TABLEID=9
CONST TS_PROJECTTRANSITIONS_TABLEID=24

CONST TS_FLDTYPE_NUMERIC             = 100  ' Integer or floating-point field.
CONST TS_FLDTYPE_TEXT                = 101  ' Text field up to 255 characters.
CONST TS_FLDTYPE_DATETIME            = 103 ' Date/Time field.
CONST TS_FLDTYPE_SELECTION           = 104 ' Single selection field.
CONST TS_FLDTYPE_BINARY              = 105 ' Binary field.
CONST TS_FLDTYPE_STATE               = 106  ' System-defined state field.
CONST TS_FLDTYPE_USER                = 107  ' User selection field.
CONST TS_FLDTYPE_PROJECT             = 108  ' System-defined project field.
CONST TS_FLDTYPE_SUMMATION           = 109  ' Calculated summation field.
CONST TS_FLDTYPE_MULTIPLE_SELECTION  = 110  ' Multi-selection selection field. DbVer 10
CONST TS_FLDTYPE_CONTACT             = 111 ' Contact selection field.  DbVer 10
CONST TS_FLDTYPE_COMPANY             = 112 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_INCIDENT            = 113  ' Incident selection field.  DbVer 10
CONST TS_FLDTYPE_PRODUCT             = 114 ' Obsolete.  DbVe
CONST TS_FLDTYPE_SERVICEAGREEMENT    = 115 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_FOLDER              = 116  ' Folder link selection field.  DbVer 10
CONST TS_FLDTYPE_KEYWORDLIST         = 117 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_PRODUCTLIST         = 118 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_PROBLEM             = 119 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_RESOLUTION          = 120 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_MERCHANDISE         = 121 ' Obsolete.  DbVer 10
CONST TS_FLDTYPE_RELATIONAL          = 122  ' Single selection relational field.  DbVer 10
CONST TS_FLDTYPE_SUBRELATIONAL       = 123 ' Sub-relational selection field.  DbVer 10
CONST TS_FLDTYPE_SYSTEM              = 124  ' System field.  DbVer 10
CONST TS_FLDTYPE_MULTIPLE_RELATIONAL = 125 ' Multi-selection relational field.  DbVer 10
CONST TS_FLDTYPE_MULTIPLE_USER       = 126 ' Multi-selection field of users.  DbVer 57001
CONST TS_FLDTYPE_MULTIPLE_GROUP      = 127 ' Multi-selection field of groups.

' *********************************************************************************************

Dim vbQuote , vbCr , vbLf , vbCrLf , vbEol

' *********************************************************************************************

vbQuote=chr(34)
vbCr=chr(13)
vbLf=chr(10)
vbCrLf=vbCr & vbLf
vbEol="<br>" & vbCrLf

' *********************************************************************************************

' get Field info for source field
Dim objApprecSrc , objApprecDest , strMojo
Call Ext.LogInfoMsg("*** get source field info '" + CStr(SOURCE_FIELD) + "'" + vbCrLf)
strMojo = GetFieldRec(SOURCE_FIELD,objApprecSrc)
Call Ext.LogInfoMsg("*** Src Field info=" + strMojo + vbCrLf)

Call Ext.LogInfoMsg("*** get dest field info '" + CStr(DEST_FIELD) + "'")
strMojo = GetFieldRec(DEST_FIELD,objApprecDest)
Call Ext.LogInfoMsg("*** Dest Field info=" + strMojo + vbCrLf)

' The only way to detect if the function couldn't match the field is to check the object value
' returned in the second param for NOTHING.  The other "IsXXX()" functions will not tell us!
If  Not ((objApprecSrc Is Nothing) OR (objApprecDest Is Nothing)) Then

Call Ext.LogInfoMsg("*** got AppRecord objects for '" + CStr(objApprecSrc.GetDisplayName) + "' and '" + CStr(objApprecDest.GetDisplayName) + "'")
	dim str , nSrcFldType , nDestFldType

	' If the source
	Call objApprecSrc.GetFieldValue("FLDTYPE",str)
	nSrcFldType = CInt(str)

	Call objApprecDest.GetFieldValue("FLDTYPE",str)
	nDestFldType = CInt(str)

	Select Case nSrcFldType
		Case TS_FLDTYPE_SELECTION
			Select Case nDestFldType
				Case TS_FLDTYPE_MULTIPLE_SELECTION
					Call Ext.LogInfoMsg("single -> multi")
				Case TS_FLDTYPE_RELATIONAL
					Call Ext.LogInfoMsg("single -> relational")
				Case TS_FLDTYPE_MULTIPLE_RELATIONAL
					Call Ext.LogInfoMsg("single -> multi-relational")
				Case Else
					Call Ext.LogErrorMsg("Unsupported conversion")
			End Select
		Case TS_FLDTYPE_MULTIPLE_SELECTION
			Select Case nDestFldType
				Case TS_FLDTYPE_MULTIPLE_RELATIONAL
					Call Ext.LogInfoMsg("multi -> multi-relational")
				Case Else
					Call Ext.LogErrorMsg("Unsupported conversion")
			End Select
		Case TS_FLDTYPE_RELATIONAL
			Select Case nDestFldType
				Case TS_FLDTYPE_SELECTION
					' future
					Call Ext.LogInfoMsg("future implementation retrograde : relational -> selection")
				Case TS_FLDTYPE_MULTIPLE_RELATIONAL
					Call Ext.LogInfoMsg("relational -> multi-relational")
				Case Else
					Call Ext.LogErrorMsg("Unsupported conversion")
			End Select
		Case TS_FLDTYPE_MULTIPLE_RELATIONAL
			Select Case nDestFldType
				Case TS_FLDTYPE_MULTIPLE_SELECTION
					' future
					Call Ext.LogInfoMsg("future implementation retrograde : multi-relational -> multi-select")
				Case Else
					Call Ext.LogErrorMsg("Unsupported conversion")
			End Select
		Case Else
			Call Ext.LogErrorMsg("Unsupported conversion")
	End Select


Else
	Call Ext.LogErrorMsg("Couldn't get AppRecords for field ID '" + CStr(SOURCE_FIELD) + "' or field ID '" + CStr(DEST_FIELD) + "'")
End If


' *********************************************************************************************
' *********************************************************************************************
' *********************************************************************************************
' Find the record in TS_FIELDS with the specified ts_id or ts_dbname.
' Set the second param to the Field's AppRecord object
' Return a string containing groovy info about the field.
Function GetFieldRec(vntFldId, byRef objApprec)
	Dim strWhere , str2
	str2 = "<no field data found>" + vbCrLf



Set objApprec = Nothing
Set objApprec = Ext.CreateAppRecord(-1)
	' calling CreateAppRecord on a non-existant table returns a non-empty, non-null valid object
	' that isn't usable.
If objApprec is Nothing Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  is nothing!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  isn't nothing" )
End If

If IsEmpty(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  IsEmpty()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  isn't IsEmpty()" )
End If

If IsNull(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  IsNull()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  isn't IsNull()" )
End If

If IsObject(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  IsObject()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS)  isn't IsObject()" )
End If

	
	' We're going to get a record from TS_FIELDS.  The normal TeamScript calls for working with
	' fields ( CreateObject{'TeamTrack.Field'} , varRecord.GetFieldValue , etc) are for use within
	' the context of accessing an issue/item in a primary table.  We're going directly to TS_FIELDS
	' and therefore have to do a little more work, especially getting the possible selections for
	' a single or multi selection field.
	
	' Using an AppRecord to query the TS_FIELDS table works for almost everything, but obviously
	' we can't call the Fields object-specific methods like "GetSelectionList()".
	' an actual Field object.  I can't use the optional "recType" param for
	' CreateAppRecord because - as I understand it - that specifies the type of field to be
	' **created** (ts_FldType) using the constants TS_FLDTYPE_xxxx defined in the DB Schema for
	' TS_FIELDS table.
	Set objApprec = Ext.CreateAppRecord(TS_FIELDS_TABLEID)

	
If objApprec is Nothing Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  is nothing!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  isn't nothing" )
End If

If IsEmpty(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  IsEmpty()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  isn't IsEmpty()" )
End If

If IsNull(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  IsNull()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  isn't IsNull()" )
End If

If IsObject(objApprec) Then
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  IsObject()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS)  isn't IsObject()" )
End If



	If IsObject(objApprec) Then
Call Ext.LogInfoMsg("*** successfully created AppRecord for fields table '" + CStr(TS_FIELDS_TABLEID) + "'" )
		If IsNumeric(vntFldId) Then
			strWhere="TS_ID=" + CStr(vntFldId)
		Else
			strWhere="TS_DBNAME like('" + CStr(vntFldId) + "')"
		End If
Call Ext.LogInfoMsg("*** GetFieldRec: where clause = '" + strWhere + "'")

		If objApprec.ReadWithWhere(strWhere) Then
			' Return the tuple of the specified field in TS_FIELDS
Call Ext.LogInfoMsg("*** Yeeha! Found entry for '" + CStr(vntFldId) + "' in TS_FIELDS")

			dim str , nFldTsId
			str2 = vbCrLf + "Field Mojo:" + vbCrLf
			Call objApprec.GetFieldValue("ID",str)
			nFldTsId = CInt(str)
			str2 = str2 + "     ts_id='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("TABLEID",str)
			str2 = str2 + "     ts_tableid='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("NAME",str)
			str2 = str2 + "     ts_name='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("DBNAME",str)
			str2 = str2 + "     ts_dbname='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("FLDTYPE",str)
			str2 = str2 + "     ts_fldtype='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("LEN",str)
			str2 = str2 + "     ts_len='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("ATTRIBUTES",str)
			str2 = str2 + "     ts_attributes='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("STATUS",str)
			str2 = str2 + "     ts_status='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("PROPERTY",str)
			str2 = str2 + "     ts_property='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("OPTIONS",str)
			str2 = str2 + "     ts_options='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("MASTERID",str)
			str2 = str2 + "     ts_masterid='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("RELATIONID",str)
			str2 = str2 + "     ts_relationid='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("FIELDID",str)
			str2 = str2 + "     ts_fieldid='" + str + "'" + vbCrLf
			Call objApprec.GetFieldValue("DESCRIPTION",str)
			str2 = str2 + "     ts_description='" + str + "'" + vbCrLf


			Dim arl , ar
			' Field.GetSelectionList() Returns an AppRecordList object
			Set arl = GetSelectionsAsArl(nFldTsId)
			'IS nothing!  Isn't empty, isn't null, is object.


			
If arl is Nothing Then
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  is nothing!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  isn't nothing" )
End If

If IsEmpty(arl) Then
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  IsEmpty()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  isn't IsEmpty()" )
End If

If IsNull(arl) Then
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  IsNull()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  isn't IsNull()" )
End If

If IsObject(arl) Then
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  IsObject()!!!!!!!!!!!!!!" )
Else
Call Ext.LogInfoMsg("*** GetSelectionsAsArl()  isn't IsObject()" )
End If


			
			If not arl is Nothing Then
Call Ext.LogInfoMsg("objApprec isn't nothing!!!")
				str2 = str2 + vbCrLf + "Selection List:" + vbCrLf + ","
				' Iterate the list of TS_SELECTIONS records
				For Each ar in arl
					Call ar.GetFieldValue("NAME",str)
					str2 = str2 + trim(str) + ","
				next 	'why does this line give a "collection required" run-time error?
			Else
				Call Ext.LogErrorMsg("Couldn't GetSelectionList() on field object 'objApprec'")
			End If

		Else
			Call Ext.LogErrorMsg("Couldn't find field TS_ID='" + CStr(vntFldId) + "' in table #'" + CStr(TS_FIELDS_TABLEID) + "'")
			Set objApprec=Nothing
		End If
	Else
		Call Ext.LogErrorMsg("failed to create appRecord for table '" + CStr(TS_FIELDS_TABLEID) + "'" )
			Set objApprec=Nothing
	End If

	' Return value
	GetFieldRec = str2

End Function

' *********************************************************************************************

Function GetSelectionsAsArl(nFldTsId)

	Set GetSelectionsAsArl = Nothing

	' Get all possible selectable values for field specified
	dim arl , strWhere
	Set arl = Ext.CreateAppRecordList(TS_SELECTIONS_TABLEID)
	If IsObject(arl) Then
		strWhere = "TS_FLDID=" + CStr(nFldTsId) + " AND TS_STATUS=0"
Call Ext.LogInfoMsg("GetSelectionsAsArl(" + CStr(	nFldTsId) + ")  :: where clause=" + strWhere)
		If arl.ReadWithWhere(strWhere,"TS_ORDERINDEX") Then
Call Ext.LogInfoMsg("ReadWithWhere(" + strWhere + ") success on field TS_ID=" + CStr(nFldTsId))
			Set GetSelectionsAsArl = arl
		Else
			Call Ext.LogErrorMsg("ReadWithWhere(" + strWhere + ") failed on field TS_ID=" + CStr(nFldTsId))
		End If
	Else
		Call Ext.LogErrorMsg("Failed to create AppRecordList for field TS_ID=" + CStr(nFldTsId))
	End if
	
End Function

' *********************************************************************************************