Helix SwarmHelix Swarm

GenERD.tsc

  • //
  • guest/
  • paul_m_thompson/
  • gd.src/
  • tsc/
  • GenERD.tsc #1
  • View
  • Commits
  • Open Download .zip Download (45 KB) 
  1. Option Explicit
  2.  
  3. '  $File: //depot/GenERD.tsc $
  4. '  $DateTime: 2012/05/06 23:36:43 $
  5. '  $Revision: #2 $
  6. '  $Change: 2 $
  7. '  $Author: pthompson $
  8.  
  9.  
  10. '  provides:   main
  11.  
  12. '  This script will dump the primary and aux tables for a project, table or
  13. '  workflow.  The script is run in the URL context like this:
  14. '     http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd
  15. '     or
  16. '     http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptID=xxx
  17. '        where "xxx" is the TS_ID of this script.
  18.  
  19. '  See the AppScript manual section "URL Direct Access Context" for more info.
  20. '  Note that while the params "ScriptPage" and "scriptName" are NOT case-
  21. '  sensitive, the name of the script *IS* case sensitive.
  22.  
  23. '  To use, add this script plus the "vb_constants.tsl" and "DB Schema Constants.tsl" 
  24. '     teamscript library files to an SBM Process App or to the Global Process App.
  25. '     Make sure the "INCLUDE" lines below specify the name of the included scripts by
  26. '     script name.  Note that the file names may be identical to the script names.
  27. '     Deploy the PA.
  28. '  To run the script, log into SBM then change the URL to include the ScriptPage and ScriptName
  29. '     params as per above.  If you want to report on a specific project, table, or workflow
  30. '     include the params described below.  If you don't include any other params you
  31. '     get all Primary tables, all aux tables, plus Companies and Contacts.
  32. '     You'll notice the following:
  33. '        1 line that says "<BEGINNING OF DOCUMENT>".  Confirmation that "beyond this point there be data".
  34. '        1 line of database & system configuration stuff (with it's header line),
  35. '        Database field header line followed by a bunch-o-lines of database field information.
  36. '        1 line that says "<END OF DOCUMENT>".  Confirms that the program ran
  37. '           all the way to the end.
  38.  
  39. '  You probably want the field information in Excel.  To do that, copy the database field
  40. '     header line and the lines after to an editor and save as a "CSV" file, then open it in Excel.
  41. '     Go through the Excel dialogs to specify how to parse the contents and there you have it.
  42.  
  43.  
  44. '  Parameters may be added to the URL to specify the project name,
  45. '  table name (or dB name), workflow name or Output format.
  46. '  Parameters *must* be properly URL-encoded (see http://en.wikipedia.org/wiki/Percent-encoding).
  47. '  Parameters are not case sensitive and values are automatically "wild-carded"
  48. '  so that a param "work" will match "Work Groups" and "non-working".  The
  49. '  default without any params is to basically print all application-related
  50. '  fields
  51.  
  52. '  The following params may be specified :
  53. '     proj        project name or ID
  54. '     tbl         table name or ID
  55. '     wfl         workflow name or ID
  56. '     delim       delimiter to use between fields.  Default is SEMI-COLON (';')
  57. '     format      text, html, css, xls
  58. '                 Default is "text": delimited text using the delimiter specified by "delim" param.
  59. '                 "html" uses HTML tables
  60. '                 "css" uses CSS with "div" and "span" elements.  WORK IN PROCESS - NOT FUNCTIONAL
  61. '                 "xls" outputs content as "Content-Type: application/vnd.ms-excel".   WORK IN PROCESS - NOT FUNCTIONAL
  62.  
  63. '  examples
  64. '    All fields for the "Work Groups" project
  65.  
  66. '       http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd&proj=work
  67. '    or
  68. '       http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd&proj=Work%32Groups
  69.  
  70.  
  71. '    All fields for SharePoint Project Servers table (TS_SHAREPOINTPROJECTSERVERS)
  72.  
  73. '       http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd&tbl=sharepoint
  74. '    or
  75. '       http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd&tbl=SHAREPOINTPROJECTSERVERS
  76.  
  77.  
  78. ' If you know Excel, you can create a Web query in Excel and have it import the data directly.
  79. '     Data / From Web / New Web Query 
  80. '     Address = http://myserver/tmtrack/tmtrack.dll?ScriptPage&scriptName=GenErd&format=html
  81.  
  82. '     NOTE the added "&format=html" which helps Excel parse the data
  83.  
  84. ' -----------------------------------------------------------------------------
  85. ' -----------------------------------------------------------------------------
  86.  
  87. $INCLUDE(DB Schema Constants.tsl)
  88. $INCLUDE(vb_constants.tsl)
  89.  
  90. ' NOTE: We don't use the "EmitWebPageX.tsl" library because we need to send
  91. '  output to a string buffer ... not the output stream.
  92.  
  93. ' -----------------------------------------------------------------------------
  94. CONST URL_CONTEXT = "URL"
  95.  
  96. CONST SOLUTION_PARAM = "sol"
  97. CONST PROJECT_PARAM = "proj"
  98. CONST TABLE_PARAM = "tbl"
  99. CONST WORKFLOW_PARAM = "wfl"
  100.  
  101. CONST DELIM_PARAM = "delim"
  102. CONST FORMAT_PARAM = "format"
  103.  
  104. CONST FORMAT_TEXT_PARAM = "text"
  105. CONST FORMAT_TEXT_MODE = 1
  106. CONST FORMAT_HTML_PARAM = "html"
  107. CONST FORMAT_HTML_MODE = 2
  108. CONST FORMAT_CSS_PARAM = "css"
  109. CONST FORMAT_CSS_MODE = 3
  110. CONST FORMAT_XLS_PARAM = "xls"
  111. CONST FORMAT_XLS_MODE = 4
  112.  
  113. ' -----------------------------------------------------------------------------
  114.  
  115. ' Global Output buffer.  All output text is appended to this string then
  116. ' sent to Stream using appropriate call
  117. Dim gstrOutputBuffer
  118.  
  119. ' -----------------------------------------------------------------------------
  120.  
  121.  
  122. ' Call LogIt("context=" & Shell.Context & "vbCrLf ->" & vbCrLf & " <- vbCrLf   vbHtmlNewLine ->" & vbHtmlNewLine  & "<- vbHtmlNewLine")
  123.  
  124. If LCase(Shell.Context) = LCase(URL_CONTEXT) Then
  125.  
  126.    Call EmitWebPageHead()
  127.  
  128.    Dim strSolution , strProject , strTable , strWorkflow , strDelim , strFormatMode, nFormatMode , strEmptyString
  129.  
  130.    strSolution = Shell.Params.Item(SOLUTION_PARAM)
  131.    strProject = Shell.Params.Item(PROJECT_PARAM)
  132.    strTable = Shell.Params.Item(TABLE_PARAM)
  133.    strWorkflow = Shell.Params.Item(WORKFLOW_PARAM)
  134.    strDelim = Shell.Params.Item(DELIM_PARAM)
  135.    strFormatMode = Shell.Params.Item(FORMAT_PARAM)
  136.  
  137.    ' Select output formatting mode.  Default is TEXT
  138.    If LCase(strFormatMode) = FORMAT_HTML_PARAM Then
  139.       nFormatMode = FORMAT_HTML_MODE
  140.       strEmptyString = "&nbsp;"
  141.    ElseIf LCase(strFormatMode) = FORMAT_CSS_PARAM  Then
  142.       nFormatMode = FORMAT_CSS_MODE
  143.       strEmptyString = "&nbsp;"
  144.    ElseIf LCase(strFormatMode) = FORMAT_XLS_PARAM  Then
  145.       ' I have yet to figure out the formatting for output directly to XLS.
  146.       nFormatMode = FORMAT_XLS_MODE
  147.       strEmptyString = ""
  148.    Else
  149.       nFormatMode = FORMAT_TEXT_MODE
  150.       strEmptyString = ""
  151.    End If
  152.  
  153.    If Len(strDelim) < 1 Then strDelim = ";"
  154.  
  155. ' Call LogIt("strSolution='" & strSolution & "'   strProject='" & strProject & "'   strTable='" & strTable & "'   strWorkflow='" & strWorkflow & "'   strDelim='" & strDelim & "'   nFormatMode=" & nFormatMode & "   strFormatMode='" & strFormatMode & "'"   )
  156.  
  157.  
  158.    Dim arlFields , arecField , strWhere
  159.    Set arlFields = Ext.CreateAppRecordList(Ext.TableID("TS_FIELDS","database"))
  160. ' Call  LogIt("arlFields is a " & typename(arlFields))
  161.  
  162.    ' projects -> workflows -> tables -> solutions
  163.    ' Project   has   TS_WORKFLOWID
  164.    ' Workflow  has   TS_TABLEID
  165.    ' Table     has   TS_SOLUTIONID
  166.  
  167.    ' Field     has   TS_TABLEID
  168.  
  169.    ' The WHERE is querying TS_FIELDS
  170.    If Len(strProject) > 0 Then
  171.       If Len(strWhere) > 0 Then strWhere = strWhere & " OR "
  172.       strWhere = "(ts_TableId In (Select wfl.ts_TableId from ts_Workflows wfl, ts_Projects prj Where prj.ts_WorkflowID=wfl.ts_id AND prj.ts_Name like '%" & strProject & "%'))"
  173.    End if
  174.  
  175.    If Len(strWorkflow) > 0 Then
  176.       If Len(strWhere) > 0 Then strWhere = strWhere & " OR "
  177.       strWhere = "(ts_TableId In (Select wfl.ts_TableId from ts_Workflows wfl Where wfl.ts_Name like '%" & strWorkflow & "%'))"
  178.    End if
  179.  
  180.    If Len(strTable) > 0 Then
  181.       If Len(strWhere) > 0 Then strWhere = strWhere & " OR "
  182.       strWhere = "(ts_TableId In (Select tbl.ts_id from ts_Tables tbl where tbl.ts_name like '%" & strTable & "%' OR tbl.ts_DbName like '%" & strTable & "%'))"
  183.    Else
  184.       ' If no options specified, default to all custom tables plus companies and contacts table
  185.       If Len(strWhere) > 0 Then strWhere = strWhere & " OR "
  186.       strWhere = "(ts_TableId = 37 OR ts_TableId = 38 Or ts_TableId >= 1000)"
  187.    End if
  188.  
  189.    ' Add an Order by Clause
  190.     strWhere = strWhere & " ORDER BY TS_TABLEID, TS_NAME"
  191.  
  192. ' Call  LogIt("strWhere='" & strWhere & "'")
  193.  
  194.  
  195.    If arlFields.ReadWithWhere(strWhere) Then
  196.       ' Call  LogIt("ReadWithWhere returned " & arlFields.Count() & " records.")
  197.       ' Some helpful identifying info about this instance:
  198.       ' ts_systemsettings
  199.       Dim n_SystemSettings_TblId , str_StrValFldName
  200.       n_SystemSettings_TblId = CInt(Ext.TableId("TS_SYSTEMSETTINGS","database"))
  201.  
  202.       Call EmitSectionStart("SystemSettings")
  203.          Call EmitHeaderStart("SystemSettingsHeader")
  204.             Call EmitLineStart("SystemSettingsLabels")
  205.                Call OutputChunk( "Database ODBC DSN" )
  206.                Call OutputChunk( "Database Name" )
  207.                Call OutputChunk( "Database Server" )
  208.                Call OutputChunk( "Is remote" )
  209.                Call OutputChunk( "DatabaseIdentifier" )
  210.                Call OutputChunk( "CharSet" )
  211.                Call OutputChunk( "Locale" )
  212.                Call OutputChunk( "TimeZone" )
  213.                Call OutputChunk( "LicenseServer" )
  214.                Call OutputChunk( "EnvironmentSet" )
  215.                Call OutputChunk( "AdminEmailToolbar" )
  216.                Call OutputChunk( "SMTPServer:SMTPPort" )
  217.                Call OutputChunk( "SMTPDomain" )
  218.                Call OutputChunk( "NSHTTPLinkAddress:NSHTTPLinkPort" )
  219.                Call OutputChunk( "SMTPAuthUsername")
  220.             Call EmitLineEnd()
  221.          Call EmitHeaderEnd()
  222.  
  223.          Dim strDsn , strDbName , strServerName , bRemote
  224.          Call Shell.Db.GetConnectionInfo(strDsn , strDbName , strServerName , bRemote)
  225.  
  226.          Call EmitLineStart("SystemSettingsValues")
  227.             Call OutputChunk( strDsn )
  228.             Call OutputChunk( strDbName )
  229.             Call OutputChunk( strServerName )
  230.             Call OutputChunk( bRemote )
  231.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='DatabaseIdentifier'") )
  232.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='CharSet'") )
  233.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='Locale'") )
  234.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='TimeZone'") )
  235.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='LicenseServer'") )
  236.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='EnvironmentSet'") )
  237.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='AdminEmailToolbar'") )
  238.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='SMTPServer'") & ":" & GetField(n_SystemSettings_TblId,"LONGVALUE","ts_Name='SMTPPort'") )
  239.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='SMTPDomain'") )
  240.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='NSHTTPLinkAddress'") & ":" & GetField(n_SystemSettings_TblId,"LONGVALUE","ts_Name='NSHTTPLinkPort'") )
  241.             Call OutputChunk( GetField(n_SystemSettings_TblId,"STRINGVALUE","ts_Name='SMTPAuthUsername'") )
  242.          Call EmitLineEnd()
  243.       Call EmitSectionEnd()
  244.  
  245.  
  246.       Call EmitBlankLine()
  247.  
  248.  
  249.       ' Write a CSV-formatted header line for all field data columns
  250.       Call EmitSectionStart("MainFields")
  251.          Call EmitHeaderStart("MainFieldsHeader")
  252.             Call EmitLineStart("MainFieldsLabels")
  253.                Call OutputChunk( "Table_Name" )
  254.                Call OutputChunk( "Table_dB_Name" )
  255.                Call OutputChunk( "Table_ID" )
  256.                Call OutputChunk( "Field Name" )
  257.                Call OutputChunk( "Description" )
  258.                Call OutputChunk( "Field_dB_Name" )
  259.                Call OutputChunk( "Field ID" )
  260.                Call OutputChunk( "Field UUID" )
  261.                Call OutputChunk( "Deleted" )
  262.                Call OutputChunk( "SysCode:Descr" )
  263.                Call OutputChunk( "Type" )
  264.                Call OutputChunk( "Attributes" )
  265.                Call OutputChunk( "Length" )
  266.                Call OutputChunk( "Reqd" )
  267.                Call OutputChunk( "Property" )
  268.                Call OutputChunk( "Prefix" )
  269.                Call OutputChunk( "Suffix" )
  270.                Call OutputChunk( "FK_to" )
  271.                Call OutputChunk( "Dependent on" )
  272.                Call OutputChunk( "SubRel Fld Displayed" )
  273.             Call EmitLineEnd()
  274.          Call EmitHeaderEnd()
  275.  
  276.  
  277.          For Each arecField in arlFields
  278.             ' Exit For
  279.             Call OutputField(arecField)
  280.          Next
  281.       Call EmitSectionEnd()
  282.  
  283.    Else
  284.       ' Call  LogIt("ReadWithWhere returned 0 items")
  285.    End If
  286.  
  287.    Call EmitWebPageTail()
  288.  
  289.  
  290.    Select Case nFormatMode
  291.       Case FORMAT_TEXT_MODE
  292.          ' Downloads & saves the CSV content as "tmtrack.dll".
  293.          ' When opened in Excel it needs "text to columns".  Doesn't really save a lot of work.
  294.          ' Call writeResponse(gstrOutputBuffer , "application/csv")
  295.          Call Ext.WriteStream(gstrOutputBuffer)
  296.       Case FORMAT_HTML_MODE
  297.          Call Ext.WriteStream(gstrOutputBuffer)
  298.       Case FORMAT_CSS_MODE
  299.          ' TODO: Need styles to do presentation with the contant.
  300.          Call Ext.WriteStream(gstrOutputBuffer)
  301.       Case FORMAT_XLS_MODE
  302.          ' TODO: content needs the formatting detailed here in http://en.wikipedia.org/wiki/Microsoft_Excel
  303.          Call writeResponse(gstrOutputBuffer , "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
  304.    End Select
  305.  
  306. Else
  307.    Call Ext.LogErrorMsg("Expected URL context")
  308. End If
  309.  
  310. ' *****************************************************************************
  311. ' Typical headers:
  312. '  Date: Mon, 09 Apr 2012 21:07:56 GMT
  313. '  Server: Microsoft-IIS/6.0
  314. '  X-Powered-By: ASP.NET
  315. '  Expires: 0
  316. '  Cache-Control: no-cache
  317. '  Content-Type: text/html; charset=UTF-8
  318. '  Content-Length: 429853
  319.  
  320.  
  321.  
  322. Sub EmitWebPageHead()
  323. '   Call Ext.WriteStream("<!DOCTYPE HTML PUBLIC " & vbQuote & "-//W3C//DTD HTML 4.01//EN" & vbQuote & " " & vbCrLf & _
  324. '        vbQuote & "http://www.w3.org/TR/html4/strict.dtd" & vbQuote & ">" & vbCrLf)
  325. '   Call Ext.WriteStream("<HTML>" & vbCrLf)
  326. '   Call Ext.WriteStream("<HEAD>" & vbCrLf)
  327. '   Call Ext.WriteStream("<TITLE>GenERD</TITLE>" & vbCrLf)
  328. '   Call Ext.WriteStream("</HEAD>" & vbCrLf)
  329. '   Call Ext.WriteStream("<BODY>" & vbCrLf)
  330. '   Call Ext.WriteStream("<P>&lt;BEGINNING OF DOCUMENT&gt;" & vbHtmlNewLine)
  331.  
  332.    gstrOutputBuffer = gstrOutputBuffer & "<!DOCTYPE HTML PUBLIC " & vbQuote & "-//W3C//DTD HTML 4.01//EN" & vbQuote & " " & vbCrLf & _
  333.       vbQuote & "http://www.w3.org/TR/html4/strict.dtd" & vbQuote & ">" & vbCrLf & _
  334.       "<HTML>" & vbCrLf & _
  335.       "<HEAD>" & vbCrLf & _
  336.       "<TITLE>GenERD</TITLE>" & vbCrLf & _
  337.       "</HEAD>" & vbCrLf & _
  338.       "<BODY>" & vbCrLf & _
  339.       "<P>&lt;BEGINNING OF DOCUMENT&gt;" & vbHtmlNewLine
  340.  
  341. End Sub
  342.  
  343. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  344. Sub EmitWebPageTail()
  345.  
  346. '   Call Ext.WriteStream("<P>&lt;END OF DOCUMENT&gt;" & vbHtmlNewLine)
  347. '   Call Ext.WriteStream("</BODY>" & vbCrLf)
  348. '   Call Ext.WriteStream("</HTML>" & vbCrLf)
  349.  
  350.    gstrOutputBuffer = gstrOutputBuffer & "<P>&lt;END OF DOCUMENT&gt;" & vbHtmlNewLine & _
  351.       "</BODY>" & vbCrLf & _
  352.       "</HTML>" & vbCrLf
  353.  
  354. End Sub
  355.  
  356. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  357. ' Beginning of a header
  358. Sub EmitSectionStart(str)
  359.    Select Case nFormatMode
  360.       Case FORMAT_TEXT_MODE
  361.          ' Text mode doesn't care about headers or sections
  362.          ' gstrOutputBuffer = gstrOutputBuffer & ""
  363.       Case FORMAT_HTML_MODE
  364.          If Len(str)>0 Then
  365.             gstrOutputBuffer = gstrOutputBuffer & "<table id='" & str & "' border='1'><tbody>" & vbCrLf
  366.          Else
  367.             gstrOutputBuffer = gstrOutputBuffer & "<table border='1' id='ESS'><tbody>" & vbCrLf
  368.          End If
  369.       Case FORMAT_CSS_MODE
  370.          gstrOutputBuffer = gstrOutputBuffer & "<div>"
  371.    End Select
  372. End Sub
  373.  
  374. Sub EmitSectionEnd()
  375.    Select Case nFormatMode
  376.       Case FORMAT_TEXT_MODE
  377.          ' Text mode doesn't care about headers or sections
  378.          gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  379.       Case FORMAT_HTML_MODE
  380.          gstrOutputBuffer = gstrOutputBuffer & "</tbody></table>" & vbCrLf
  381.       Case FORMAT_CSS_MODE
  382.          gstrOutputBuffer = gstrOutputBuffer & "</div>"
  383.    End Select
  384. End Sub
  385.  
  386. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  387. Sub EmitHeaderStart(str)
  388.    Select Case nFormatMode
  389.       Case FORMAT_TEXT_MODE
  390.          ' Text mode doesn't care about headers or sections
  391.          ' gstrOutputBuffer = gstrOutputBuffer & ""
  392.       Case FORMAT_HTML_MODE
  393.          If Len(str)>0 Then
  394.             gstrOutputBuffer = gstrOutputBuffer & "<thead id='" & str & "'>" & vbCrLf
  395.          Else
  396.             gstrOutputBuffer = gstrOutputBuffer & "<thead id='EHS'>" & vbCrLf
  397.          End If
  398.       Case FORMAT_CSS_MODE
  399.          gstrOutputBuffer = gstrOutputBuffer & "<div>"
  400.    End Select
  401. End Sub
  402.  
  403. Sub EmitHeaderEnd()
  404.    Select Case nFormatMode
  405.       Case FORMAT_TEXT_MODE
  406.          ' Text mode doesn't care about headers or sections
  407.          ' gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  408.       Case FORMAT_HTML_MODE
  409.          gstrOutputBuffer = gstrOutputBuffer & "</thead>" & vbCrLf
  410.       Case FORMAT_CSS_MODE
  411.          gstrOutputBuffer = gstrOutputBuffer & "</div>" & vbCrLf
  412.    End Select
  413. End Sub
  414.  
  415. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  416. 'Eventually format Line as a Table Line
  417. Sub EmitLineStart(str)
  418.    Select Case nFormatMode
  419.       Case FORMAT_TEXT_MODE
  420.          ' Text mode doesn't care about headers or sections
  421.          ' gstrOutputBuffer = gstrOutputBuffer & ""
  422.       Case FORMAT_HTML_MODE
  423.          If Len(str)>0 Then
  424.             gstrOutputBuffer = gstrOutputBuffer & "<tr id='" & str & "'>" & vbCrLf
  425.          Else
  426.             gstrOutputBuffer = gstrOutputBuffer & "<tr id='ELS'>" & vbCrLf
  427.          End If
  428.       Case FORMAT_CSS_MODE
  429.          gstrOutputBuffer = gstrOutputBuffer & "<div>" & vbCrLf
  430.    End Select
  431. End Sub
  432.  
  433.  
  434. Sub EmitLineEnd()
  435.    Select Case nFormatMode
  436.       Case FORMAT_TEXT_MODE
  437.          ' Text mode doesn't care about headers or sections
  438.          gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  439.       Case FORMAT_HTML_MODE
  440.          gstrOutputBuffer = gstrOutputBuffer & "</tr>" & vbCrLf
  441.       Case FORMAT_CSS_MODE
  442.          gstrOutputBuffer = gstrOutputBuffer & "</div>" & vbCrLf
  443.    End Select
  444. End Sub
  445.  
  446.  
  447. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  448. Sub EmitBlankLine()
  449.    Select Case nFormatMode
  450.       Case FORMAT_TEXT_MODE
  451.          gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  452.       Case FORMAT_HTML_MODE
  453.          gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  454.       Case FORMAT_CSS_MODE
  455.          gstrOutputBuffer = gstrOutputBuffer & vbHtmlNewLine
  456.    End Select
  457. End Sub
  458.  
  459.  
  460. ' * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * ** * **
  461. 'Eventually format chunk as a Table Cell
  462. Sub OutputChunk(str)
  463.    Select Case nFormatMode
  464.       Case FORMAT_TEXT_MODE
  465.          gstrOutputBuffer = gstrOutputBuffer & str & strDelim
  466.       Case FORMAT_HTML_MODE
  467.          gstrOutputBuffer = gstrOutputBuffer & "<td>" & str & "</td>"
  468.       Case FORMAT_CSS_MODE
  469.          gstrOutputBuffer = gstrOutputBuffer & "<span>" & str & "</span>"
  470.    End Select
  471. End Sub
  472.  
  473.  
  474. ' *****************************************************************************
  475.  
  476. Sub LogIt(str)
  477.    If Ext.OutputStreamExists() Then
  478.       ' Call Ext.WriteStream( str & vbCrLf )
  479.       gstrOutputBuffer = gstrOutputBuffer &  str & vbHtmlNewLine
  480.    Else
  481.       Call  Ext.LogInfoMsg(str)
  482.    End If
  483. End Sub
  484.  
  485. ' *****************************************************************************
  486.  
  487. ' Param is an AppRecord point to a record in TS_FIELDS
  488. Sub OutputField(objFld)
  489.    Dim strTableId , strTblType
  490.    Dim strFldType , strFldUuid , strAttribs , strLen, strStatus , strProp , strReqd , strSysCode , strOptions
  491.    Dim strPrefix , strSuffix , strMasterId , strRelationId , strSubRelFldId
  492.  
  493.    ' We need the table ID and type.
  494.    Call objFld.GetFieldValue("TABLEID",strTableId)
  495.    strTblType = GetField("TS_TABLES","TYPE","TS_ID=" & strTableId)
  496.  
  497.    Call objFld.GetFieldValue("FLDTYPE",strFldType)
  498.    Call objFld.GetFieldValue("UUID",strFldUuid)
  499.    Call objFld.GetFieldValue("ATTRIBUTES",strAttribs)
  500.    Call objFld.GetFieldValue("LEN",strLen)
  501.    Call objFld.GetFieldValue("STATUS",strStatus)
  502.    Call objFld.GetFieldValue("PROPERTY",strProp)
  503.  
  504.    Call objFld.GetFieldValue("REQUIRED",strReqd)
  505.    Call objFld.GetFieldValue("SYSCODE",strSysCode)
  506.    Call objFld.GetFieldValue("OPTIONS",strOptions)
  507.    Call objFld.GetFieldValue("DISPLAYPREFIX",strPrefix)
  508.    Call objFld.GetFieldValue("DISPLAYSUFFIX",strSuffix)
  509.    Call objFld.GetFieldValue("MASTERID",strMasterId)
  510.    Call objFld.GetFieldValue("RELATIONID",strRelationId)
  511.    Call objFld.GetFieldValue("FIELDID",strSubRelFldId)
  512.  
  513.    ' Field's Table name, db name, ID
  514.    Call EmitLineStart("")
  515.  
  516.       Call OutputChunk(Ext.TableDisplayName(strTableId))
  517.       Call OutputChunk(Ext.TableDatabaseName(strTableId))
  518.       Call OutputChunk(strTableId)
  519.  
  520.       ' Field name, Description, db Name, ID, Deleted, SysCode
  521.       Call OutputChunk( objFld.GetDisplayName() )
  522.       ' Only need to quote when in TEXT mode
  523.       If nFormatMode=FORMAT_TEXT_MODE Then
  524.             Call OutputChunk( vbQuote & GetFld(objFld,"DESCRIPTION") & vbQuote )
  525.       Else
  526.             Call OutputChunk( GetFld(objFld,"DESCRIPTION") )
  527.       End If
  528.       Call OutputChunk( "TS_" & GetFld(objFld,"DBNAME"))
  529.       Call OutputChunk( objFld.GetID() )
  530.       Call OutputChunk( strFldUuid )
  531.       Call OutputChunk( Deleted(strStatus) )
  532.  
  533.       ' diagnostic SYSCODE stuff
  534.       ' Decoding SYSCODE requires taking into account the Table ID and type.
  535.       Call OutputChunk( strSysCode & ":" & SyscodeDescr(strSysCode,strTableId,strTblType) )
  536.  
  537.  
  538.       ' Field type, Attributes, Length, Required, Property
  539.       Call OutputChunk(FieldTypeDescr(strFldType))
  540.       Call OutputChunk(AttribDescr(strFldType,strAttribs))
  541.       Call OutputChunk(strLen)
  542.       If CInt(strReqd)=0 Then Call OutputChunk(strEmptyString) Else Call OutputChunk("Reqd")
  543.       Call OutputChunk(PropDescr(strProp))
  544.  
  545.  
  546.       ' Display Prefix, Suffix
  547.       Call OutputChunk(strPrefix)
  548.       Call OutputChunk(strSuffix)
  549.  
  550.  
  551.  
  552.       ' Relational stuff
  553.       '  If strRelationId is non-zero and strFldType is relational (a relational
  554.       '  selection field, or a user/group field, etc), then strRelationId is a foreign
  555.       '  key into the TS_TABLES table indicating the table from which possible
  556.       '  values are obtained. If strFldType is TS_FLDTYPE_SUBRELATIONAL, strRelationId
  557.       '  is the relational field that supplies the current selection value
  558.       '  allowing for more than one relational field based on the same table.
  559.       ' FK_to
  560.       If (CInt(strRelationId) <> 0) THen
  561.          If (strFldType = TS_FLDTYPE_SUBRELATIONAL) Then
  562.             ' Relation ID is relational field ID
  563.             Call OutputChunk(GetFieldFullName(strTableId,strRelationId,-1))
  564.          Else
  565.             ' Relation ID is table ID
  566.             Call OutputChunk(Ext.TableDatabaseName(strRelationId))
  567.          End If
  568.  
  569.       Else
  570.          Call OutputChunk(strEmptyString)
  571.       End If
  572.  
  573.       Call OutputChunk(MasterField(strTableId, strMasterId))            ' "Dependent on"
  574.  
  575.       ' Properly formatting subrelational FIELDID is a bit more work than I have time for right now....
  576.       If strSubRelFldId=0 Then
  577.          Call OutputChunk(strEmptyString)
  578.       Else
  579.          If (strFldType = TS_FLDTYPE_SUBRELATIONAL) Then
  580.             ' strRelationId points to relational field in this field's table that determines "driving" value
  581.             ' strSubRelFldId is field to actually display
  582. ' Call Ext.LogInfoMsg("Subrelational field ID=" & strSubRelFldId)                                                       ' =680
  583. ' Call Ext.LogInfoMsg("GetFieldFullName()=" & GetFieldFullName(strTableId,strRelationId,-1))                            ' =Business Unit (USR_SANDBOX.TS_BUSINESS_UNIT)
  584. ' Call Ext.LogInfoMsg("GetFieldTableId()=" & GetFieldTableId(strSubRelFldId) )                                          ' =1031
  585. ' Call Ext.LogInfoMsg("GetFieldFullName()=" & GetFieldFullName(GetFieldTableId(strSubRelFldId) ,strSubRelFldId,-1) )    '=Biz Unit User Z (USR_BUSINESS_UNIT.TS_BIZ_UNIT_USER_Z)
  586.  
  587.             Call OutputChunk( GetFieldFullName(GetFieldTableId(strSubRelFldId) ,strSubRelFldId,-1) )
  588.             ' Call OutputChunk(strSubRelFldId)    'SubRel
  589.          End If
  590.       End If
  591.  
  592.    Call EmitLineEnd()
  593.  
  594. End Sub
  595.  
  596. ' *****************************************************************************
  597.  
  598. '  Field name is either UPPERCASE DATABASE NAME without "TS_", or Mixed Case Display Name
  599. Function GetFld(arec,strFldName)
  600.    Dim str
  601.    If arec.GetFieldValue(strFldName,str) Then
  602.       GetFld = Trim(str)
  603.    Else
  604.       GetFld = strFldName
  605.    End If
  606. End Function
  607.  
  608. ' *****************************************************************************
  609.  
  610. ' Return a fully qualified db name (tablename.fieldname) given the table and field IDs
  611. ' 3rd param (flag)
  612. '     < 0 format result as "Field Display Name (TBL_DBNAME.FLD_DBNAME)"
  613. '     = 0 format result as "TBL_DBNAME.FLD_DBNAME"
  614. '     > 0 format result as "TBL_DBNAME.FLD_DBNAME (Field Display Name)"
  615.  
  616. ' Field ID may be a numeric value or field name.
  617. '     system field names in all lowercase
  618. '     VARIABLE FIELD DATABASE NAMES IN UPPERCASE WITHOUT "TS_" PREFIX, Or any Case but with "TS_" prefix.
  619. '     Variable Field Display Names In Mixed Case
  620.  
  621. '    If isNumeric(fldId) Then
  622. '        arec.Read(fldId)
  623. '    ElseIf fldId = UCase(fldId) or UCase(Left(fldId),3) = "TTS_"  Then
  624. '       nTblId = Ext.TableId(varTblId,"database")
  625. '    Else
  626. '       nTblId = Ext.TableId(varTblId,"display")
  627. '    End If
  628.  
  629.  
  630. Function GetFieldFullName(ByVal tblId,fldId,flg)
  631.    Dim arec , strFldName
  632.    set arec = Ext.CreateAppRecord(Ext.TableID("TS_FIELDS","database"))
  633.    If arec.ReadWithWhere("ts_tableid=" & tblId & " and ts_id=" & fldId) Then
  634.       strFldName = Ext.TableDatabaseName(tblId) & "." & "TS_" &  GetFld(arec,"DBNAME")
  635.       If flg < 0 Then
  636.          GetFieldFullName = arec.GetName & " (" & strFldName & ")"
  637.       ElseIf flg > 0 Then
  638.          GetFieldFullName = strFldName & " (" & arec.GetDisplayName & ")"
  639.       Else
  640.          GetFieldFullName = strFldName
  641.       End If
  642.  
  643.    Else
  644.       GetFieldFullName = "Can't read fldID '" & fldId & "' for tableID '" & tblId & "' in TS_FIELDS"
  645.    End If
  646.  
  647. End Function
  648.  
  649. ' *****************************************************************************
  650.  
  651. Function FieldTypeDescr(n)
  652.    Select Case n
  653.       Case TS_FLDTYPE_NUMERIC       FieldTypeDescr = "Numeric"
  654.       Case TS_FLDTYPE_TEXT          FieldTypeDescr = "Text"
  655.       Case TS_FLDTYPE_DATETIME      FieldTypeDescr = "Date/Time"
  656.       Case TS_FLDTYPE_SELECTION     FieldTypeDescr = "Single selection"
  657.       Case TS_FLDTYPE_BINARY        FieldTypeDescr = "Binary"
  658.       Case TS_FLDTYPE_STATE         FieldTypeDescr = "System state"
  659.       Case TS_FLDTYPE_USER          FieldTypeDescr = "User selection"
  660.       Case TS_FLDTYPE_PROJECT       FieldTypeDescr = "System project"
  661.       Case TS_FLDTYPE_SUMMATION     FieldTypeDescr = "Summation"
  662.       Case TS_FLDTYPE_MULTIPLE_SELECTION FieldTypeDescr = "Multi-select"
  663.       Case TS_FLDTYPE_CONTACT       FieldTypeDescr = "Contact"
  664.       Case TS_FLDTYPE_COMPANY       FieldTypeDescr = "Company (Obsolete)"
  665.       Case TS_FLDTYPE_INCIDENT      FieldTypeDescr = "Incident selection"
  666.       Case TS_FLDTYPE_PRODUCT       FieldTypeDescr = "Product (Obsolete)"
  667.       Case TS_FLDTYPE_SERVICEAGREEMENT FieldTypeDescr = "Service Agreement (Obsolete)"
  668.       Case TS_FLDTYPE_FOLDER        FieldTypeDescr = "Folder link"
  669.       Case TS_FLDTYPE_KEYWORDLIST   FieldTypeDescr = "Keyword List (Obsolete)"
  670.       Case TS_FLDTYPE_PRODUCTLIST   FieldTypeDescr = "Product List (Obsolete)"
  671.       Case TS_FLDTYPE_PROBLEM       FieldTypeDescr = "Problem (Obsolete)"
  672.       Case TS_FLDTYPE_RESOLUTION    FieldTypeDescr = "Obsolete."
  673.       Case TS_FLDTYPE_MERCHANDISE   FieldTypeDescr = "Obsolete."
  674.       Case TS_FLDTYPE_RELATIONAL    FieldTypeDescr = "Single relational"
  675.       Case TS_FLDTYPE_SUBRELATIONAL FieldTypeDescr = "Sub-relational"
  676.       Case TS_FLDTYPE_SYSTEM        FieldTypeDescr = "System"
  677.       Case TS_FLDTYPE_MULTIPLE_RELATIONAL FieldTypeDescr = "Multi-relational"
  678.       Case TS_FLDTYPE_MULTIPLE_USER FieldTypeDescr = "Multi-user"
  679.       Case TS_FLDTYPE_MULTIPLE_GROUP FieldTypeDescr = "Multi-group"
  680.       Case Else FieldTypeDescr = "<Unknown FLDTYPE>"
  681.    End Select
  682.  
  683. End Function
  684.  
  685. ' *****************************************************************************
  686.  
  687. Function AttribDescr(fldtype,attr)
  688.    Select Case fldtype
  689.  
  690.       Case TS_FLDTYPE_TEXT
  691.          Select Case attr
  692.             Case TS_FLDATTRIB_MEMO          AttribDescr = "Memo"
  693.             Case TS_FLDATTRIB_FIXEDTEXT     AttribDescr = "Fixed length"
  694.             Case TS_FLDATTRIB_JOURNAL       AttribDescr = "Journal"
  695.             Case TS_FLDATTRIB_JOURNAL_RO    AttribDescr = "Append-Only Journal"
  696.             Case Else AttribDescr = "<Unknown ATTRIBUTE value for TS_FLDTYPE_TEXT>"
  697.          End Select
  698.       Case TS_FLDTYPE_NUMERIC
  699.          Select Case attr
  700.             Case TS_FLDATTRIB_INT  AttribDescr = "Integer"
  701.             Case TS_FLDATTRIB_FLOAT  AttribDescr = "Floating Point"
  702.             Case TS_FLDATTRIB_FIXED_PRECISION  AttribDescr = "Fixed Precision"
  703.             Case Else AttribDescr = "<Unknown ATTRIBUTE value for TS_FLDTYPE_NUMERIC>"
  704.          End Select
  705.       Case TS_FLDTYPE_DATETIME
  706.          Select Case attr
  707.             Case TS_FLDATTRIB_DT_DATEONLY AttribDescr = "Date only"
  708.             Case TS_FLDATTRIB_DT_DATETIME AttribDescr = "Date/Time"
  709.             Case TS_FLDATTRIB_DT_TIMEOFDAY AttribDescr = "Time only"
  710.             Case TS_FLDATTRIB_DT_ELAPSEDTIME AttribDescr = "Elapsed time"
  711.             Case Else AttribDescr = "<Unknown ATTRIBUTE value for TS_FLDTYPE_DATETIME>"
  712.          End Select
  713.       Case TS_FLDTYPE_BINARY
  714.          Select Case attr
  715.             Case TS_FLDATTRIB_BIN_LISTBOX  AttribDescr = "Listbox"
  716.             Case TS_FLDATTRIB_BIN_RADIOBUTTON  AttribDescr = "Radio Buttons"
  717.             Case TS_FLDATTRIB_BIN_CHECKBOX  AttribDescr = "Checkbox"
  718.             Case TS_FLDATTRIB_BIN_TRINARY  AttribDescr = "Trinary"
  719.             Case Else AttribDescr = "<Unknown ATTRIBUTE value for TS_FLDTYPE_BINARY>"
  720.          End Select
  721.  
  722.    End Select
  723. End Function
  724.  
  725. ' *****************************************************************************
  726.  
  727. ' The schema docs are a vague on SysCode.
  728.  
  729. Function SyscodeDescr(ByVal strSysCode,strTblId,strTblType)
  730.  
  731.    Dim strDescr
  732.  
  733.    strSysCode = CInt(strSysCode)
  734.  
  735.    '  Values for TS_SYSCODE column:
  736.    '  System fields are fields that have special, application-defined semantics.
  737.    '     They are identified by a non-zero TS_SYSCODE value.
  738.  
  739.    '  Note: the documentation is not clear what "application-defined" means, but
  740.    '     Syscode values *appear* to be dependent on table ID and Table type.
  741.  
  742.  
  743.  
  744.    ' Table ID
  745.    '        TS_TBLID_CASES  1  Deprecated. See note below.
  746.    '        TS_TBLID_INCIDENTS  36  Deprecated. See note below.
  747.    '        TS_TBLID_MERCHANDISE  39  Deprecated. See note below.
  748.    '        TS_TBLID_SERVICEAGREEMENTS  40  Deprecated. See note below.
  749.    '        TS_TBLID_PRODUCTS  43  Deprecated. See note below.
  750.  
  751.    '        TS_TBLID_COMPANIES  37
  752.    '        TS_TBLID_CONTACTS  38
  753.  
  754.    '        TS_TBLID_PROBLEMS  41
  755.    '        TS_TBLID_RESOLUTIONS  42
  756.  
  757.    ' TableType
  758.    '        TS_TBLTYPE_SYS     0  System table, e.g. TS_USERS.
  759.    '        TS_TBLTYPE_PRI     1  Primary table, e.g. TTT_ISSUES.
  760.    '        TS_TBLTYPE_AUX     2  Auxiliary table, e.g. TTS_PRODUCTS.
  761.    '        TS_TBLTYPE_SYSAUX  3  System auxiliary table, e.g. TS_CONTACTS.
  762.  
  763.  
  764.    If strTblId=TS_TBLID_CASES OR strTblId=TS_TBLID_INCIDENTS Then
  765.       ' System fields for both Issues and Incidents:
  766.       Select Case strSysCode
  767.          Case TS_SYSFLD_ID                            strDescr = "Issue/Incident: ID - Reserved for future use."
  768.          Case TS_SYSFLD_PARENTID                      strDescr = "Issue/Incident: Project ID - Reserved for future use."
  769.          Case TS_SYSFLD_PROJECTID                     strDescr = "Issue/Incident: Project ID."
  770.          Case TS_SYSFLD_TITLE                         strDescr = "Issue/Incident: Title."
  771.          Case TS_SYSFLD_DESC                          strDescr = "Issue/Incident: Description."
  772.          Case TS_SYSFLD_STATE                         strDescr = "Issue/Incident: State."
  773.          Case TS_SYSFLD_DT_CREATE                     strDescr = "Issue/Incident: Submit date."
  774.          Case TS_SYSFLD_DT_LASTMODIFIED               strDescr = "Issue/Incident: Last modified date."
  775.          Case TS_SYSFLD_DT_LASTSTATECHANGE            strDescr = "Issue/Incident: Last state change date."
  776.          Case TS_SYSFLD_DT_CLOSE                      strDescr = "Issue/Incident: Close date."
  777.          Case TS_SYSFLD_DT_REOPEN                     strDescr = "Issue/Incident: Reserved for future use."
  778.          Case TS_SYSFLD_USER_OWNER                    strDescr = "Issue/Incident: Owner field"
  779.          Case TS_SYSFLD_USER_SUBMITTER                strDescr = "Issue/Incident: Submitter field"
  780.          Case TS_SYSFLD_USER_LASTMODIFIER             strDescr = "Issue/Incident: Last Modifier."
  781.          Case TS_SYSFLD_USER_LASTSTATECHANGER         strDescr = "Issue/Incident: Last state changer."
  782.          Case TS_SYSFLD_TEXT_DISPLAYID                strDescr = "Issue/incident id field displayed to the user."
  783.          Case TS_SYSFLD_SEL_CASETYPE                  strDescr = "Issue/incident type."
  784.          Case TS_SYSFLD_BIN_ACTIVEINACTIVE            strDescr = "Issue/Incident: Active/inactive."
  785.          Case TS_SYSFLD_FOLDERID                      strDescr = "Issue/Incident: Folder ID."
  786.          Case TS_SYSFLD_LASTINCIDENT                  strDescr = "Issue/Incident: Last referenced incident ID"
  787.          Case TS_SYSFLD_MULTIUSER_SECONDARYOWNER      strDescr = "Issue/Incident: Secondary owner."
  788.  
  789.          ' Additional system fields for Incidents:
  790.          Case TS_SYSFLD_COMPANY                       strDescr = "Incident: Associated company."
  791.          Case TS_SYSFLD_CONTACT                       strDescr = "Incident: Associated contact."
  792.          Case TS_SYSFLD_MERCHANDISE                   strDescr = "Incident: Associated merchandise."
  793.          Case TS_SYSFLD_SERVICEAGREEMENT              strDescr = "Incident: Associated service agreement."
  794.          Case TS_SYSFLD_PROBLEM                       strDescr = "Incident: Knowledge Base problem related to this incident."
  795.          Case TS_SYSFLD_RESOLUTION                    strDescr = "Incident: Knowledge Base resolution related to this incident."
  796.          Case TS_SYSFLD_RESOLUTIONTITLE               strDescr = "Incident: Resolution title"
  797.          Case TS_SYSFLD_RESOLUTIONDESC                strDescr = "Incident: Resolution description"
  798.       End Select
  799.  
  800.  
  801.    ' System fields for Companies:
  802.    ElseIf strTblId=TS_TBLID_COMPANIES Then
  803.       Select Case strSysCode
  804.          Case TS_SYSFLD_TITLE                         strDescr = "Company: name."
  805.          Case TS_SYSFLD_LASTINCIDENT                  strDescr = "Company: Last incident."
  806.          Case TS_SYSFLD_COMPANY_NUMBER                strDescr = "Company: number."
  807.          Case TS_SYSFLD_COMPANY_PRICONTACT            strDescr = "Company: primary contact."
  808.          Case TS_SYSFLD_COMPANY_SECCONTACT            strDescr = "Company: secondary contact."
  809.          Case TS_SYSFLD_COMPANY_ADDRESS1              strDescr = "Company: first address line."
  810.          Case TS_SYSFLD_COMPANY_ADDRESS2              strDescr = "Company: second address line."
  811.          Case TS_SYSFLD_COMPANY_CITY                  strDescr = "Company: city."
  812.          Case TS_SYSFLD_COMPANY_STATE                 strDescr = "Company: state."
  813.          Case TS_SYSFLD_COMPANY_COUNTRY               strDescr = "Company: country."
  814.          Case TS_SYSFLD_COMPANY_ZIPCODE               strDescr = "Company: zip code."
  815.          Case TS_SYSFLD_COMPANY_FAXNUMBER             strDescr = "Company: fax telephone number."
  816.          Case TS_SYSFLD_COMPANY_PHONENUMBER           strDescr = "Company: telephone number."
  817.       End Select
  818.  
  819.    ' System fields for Contacts:
  820.    ElseIf strTblId=TS_TBLID_CONTACTS Then
  821.       Select Case strSysCode
  822.          Case TS_SYSFLD_TITLE                         strDescr = "Contact: name."
  823.          Case TS_SYSFLD_LASTINCIDENT                  strDescr = "Contact: Last incident."
  824.          Case TS_SYSFLD_CONTACT_COMPID                strDescr = "Contact: Company ID"
  825.          Case TS_SYSFLD_CONTACT_FNAME                 strDescr = "Contact: First name."
  826.          Case TS_SYSFLD_CONTACT_MNAME                 strDescr = "Contact: Middle name."
  827.          Case TS_SYSFLD_CONTACT_LNAME                 strDescr = "Contact: Obsolete, use TS_SYSFLD_TITLE."
  828.          Case TS_SYSFLD_CONTACT_USERID                strDescr = "Contact: User ID."
  829.          Case TS_SYSFLD_CONTACT_PHONE                 strDescr = "Contact: Phone number."
  830.          Case TS_SYSFLD_CONTACT_EMAIL                 strDescr = "Contact: Email."
  831.          Case TS_SYSFLD_COMPANY_ADDRESS1              strDescr = "Contact: Company first address line."
  832.          Case TS_SYSFLD_COMPANY_ADDRESS2              strDescr = "Contact: Company second address line."
  833.          Case TS_SYSFLD_CONTACT_CITY                  strDescr = "Contact: city."
  834.          Case TS_SYSFLD_CONTACT_STATE                 strDescr = "Contact: state."
  835.          Case TS_SYSFLD_CONTACT_COUNTRY               strDescr = "Contact: country."
  836.          Case TS_SYSFLD_CONTACT_ZIPCODE               strDescr = "Contact: zip code."
  837.          Case TS_SYSFLD_CONTACT_FAXNUMBER             strDescr = "Contact: fax telephone number."
  838.          Case TS_SYSFLD_CONTACT_MOBILENUMBER          strDescr = "Contact: mobile telephone number."
  839.       End Select
  840.  
  841.    ' System fields for Problems/Resolutions:
  842.    ElseIf strTblId=TS_TBLID_PROBLEMS OR strTblId=TS_TBLID_RESOLUTIONS  Then
  843.       Select Case strSysCode
  844.          Case TS_SYSFLD_TITLE                         strDescr = "Problem/Resolution: Title."
  845.          Case TS_SYSFLD_DESC                          strDescr = "Problem/Resolution: Description."
  846.          Case TS_SYSFLD_DT_LASTMODIFIED               strDescr = "Problem/Resolution: Last modified date."
  847.          Case TS_SYSFLD_FOLDERID                      strDescr = "Problem/Resolution: Folder ID."
  848.          Case TS_SYSFLD_PROBLEM                       strDescr = "Problem/Resolution: Knowledge Base problem related to this resolution."
  849.          Case TS_SYSFLD_VISIBILITY                    strDescr = "Problem/Resolution: Visibility."
  850.          Case TS_SYSFLD_PRODUCTS                      strDescr = "Problem/Resolution: Products affected."
  851.       End Select
  852.  
  853.    ' System fields for Service Agreements:
  854.    ElseIf strTblId=TS_TBLID_SERVICEAGREEMENTS Then
  855.       Select Case strSysCode
  856.          Case TS_SYSFLD_TITLE                         strDescr = "Service agreement: name."
  857.          Case TS_SYSFLD_CONTACT                       strDescr = "Service agreement: Associated company."
  858.          Case TS_SYSFLD_DT_EXPIRATION                 strDescr = "Service agreement: Expiration date."
  859.       End Select
  860.  
  861.    ' System fields for user-created primary tables:
  862.    ElseIf strTblType=TS_TBLTYPE_PRI Then
  863.       Select Case strSysCode
  864.          Case TS_SYSFLD_PROJECTID                     strDescr = "Project ID."
  865.          Case TS_SYSFLD_TITLE                         strDescr = "Title."
  866.          Case TS_SYSFLD_DESC                          strDescr = "Description."
  867.          Case TS_SYSFLD_STATE                         strDescr = "State."
  868.          Case TS_SYSFLD_DT_CREATE                     strDescr = "Submit date."
  869.          Case TS_SYSFLD_DT_LASTMODIFIED               strDescr = "Last modified date."
  870.          Case TS_SYSFLD_DT_LASTSTATECHANGE            strDescr = "Last state change date."
  871.          Case TS_SYSFLD_DT_CLOSE                      strDescr = "Close date."
  872.          Case TS_SYSFLD_USER_OWNER                    strDescr = "Owner field"
  873.          Case TS_SYSFLD_USER_SUBMITTER                strDescr = "Submitter field"
  874.          Case TS_SYSFLD_USER_LASTMODIFIER             strDescr = "Last Modifier."
  875.          Case TS_SYSFLD_USER_LASTSTATECHANGER         strDescr = "Last state changer."
  876.          Case TS_SYSFLD_TEXT_DISPLAYID                strDescr = "Item id field displayed to the user."
  877.          Case TS_SYSFLD_SEL_CASETYPE                  strDescr = "Item type."
  878.          Case TS_SYSFLD_BIN_ACTIVEINACTIVE            strDescr = "Active/inactive."
  879.          Case TS_SYSFLD_MULTIUSER_SECONDARYOWNER      strDescr = "Secondary Owner."
  880.          Case TS_SYSFLD_RESOLUTIONTITLE               strDescr = "Resolution title"
  881.          Case TS_SYSFLD_RESOLUTIONDESC                strDescr = "Resolution description"
  882.       End Select
  883.  
  884.    ' System fields for user-created auxiliary tables:
  885.    ElseIf strTblType=TS_TBLTYPE_AUX Then
  886.       Select Case strSysCode
  887.          Case TS_SYSFLD_TITLE                         strDescr = "Title."
  888.          Case TS_SYSFLD_DESC                          strDescr = "Description."
  889.          Case TS_SYSFLD_DT_CREATE                     strDescr = "Submit date."
  890.          Case TS_SYSFLD_DT_LASTMODIFIED               strDescr = "Last modified date."
  891.          Case TS_SYSFLD_USER_SUBMITTER                strDescr = "Submitter field"
  892.          Case TS_SYSFLD_USER_LASTMODIFIER             strDescr = "Last Modifier."
  893.          Case TS_SYSFLD_TEXT_DISPLAYID                strDescr = "Item id field displayed to the user."
  894.          Case TS_SYSFLD_BIN_ACTIVEINACTIVE            strDescr = "Active/inactive."
  895.       End Select
  896.  
  897.    ' System fields for SharePoint auxiliary tables:
  898.    ElseIf strTblType=TS_TBLTYPE_AUX Then
  899.       Select Case strSysCode
  900.          Case TS_SYSFLD_URL                           strDescr = "SharePoint Url."
  901.          Case TS_SYSFLD_DOC_LIB                       strDescr = "SharePoint Document Library."
  902.          Case TS_SYSFLD_PARENT_SITENAME               strDescr = "SharePoint Parent site name."
  903.          Case TS_SYSFLD_URL_ADDUSER                   strDescr = "SharePoint AddUser Url."
  904.          Case TS_SYSFLD_SHAREPOINT_USER               strDescr = "SBM proxy SharePoint user id."
  905.          Case TS_SYSFLD_SHAREPOINT_PASS               strDescr = "SBM proxy SharePoint password."
  906.       End Select
  907.    End If
  908.  
  909.    If Len(strDescr) > 0 Then
  910.       SyscodeDescr = strDescr
  911.    ElseIf strSysCode = 0 Then
  912.       SyscodeDescr = ""
  913.    Else
  914.       SyscodeDescr = "<unknown SYSCODE " & strSysCode & ">"
  915.    End If
  916.  
  917.  
  918. End Function
  919.  
  920. ' *****************************************************************************
  921.  
  922. ' Return the specified field from specified table using specified where
  923.  
  924. Function GetField(strTblId,strFldId,strWhere)
  925.  
  926.    Dim nTblId
  927.    Dim arecTbl , strVal
  928.  
  929.    'Table can be either a numeric ID, database name, or display name.
  930.    If IsNumeric(strTblId) Then nTblId = CLng(strTblId) Else nTblId = Ext.TableId(strTblId)
  931.    Set arecTbl = Ext.CreateAppRecord(nTblId)
  932.    If arecTbl.ReadWithWhere(strWhere) Then
  933.       If arecTbl.GetFieldValue(strFldId,strVal) Then GetField = strVal Else Call Ext.LogErrorMsg("GetField() : can't read field '" & strFldId & "'")
  934.    Else
  935.       Call Ext.LogErrorMsg("GetField() : ReadWithWhere(" & strWhere & ") failed")
  936.    End If
  937.  
  938.  
  939. End Function
  940.  
  941.  
  942. ' *****************************************************************************
  943.  
  944. ' Given a field ID, return it's table ID
  945. Function GetFieldTableId(strFldId)
  946.    Dim arecField , strTblId
  947.    Set arecField = Ext.CreateAppRecord(Ext.TableID("TS_FIELDS","database"))
  948.    If arecField.Read(strFldId) Then Call arecField.GetFieldValue("tableid",strTblId)
  949.  
  950.    Set arecField = Nothing
  951.    GetFieldTableId = strTblId
  952. End Function
  953.  
  954. ' *****************************************************************************
  955.  
  956.  
  957.  Function Deleted(status)
  958.    Select Case status
  959.  
  960.       Case 0 Deleted = strEmptyString
  961.       Case 1 Deleted = "Deleted"
  962.       Case Else Deleted = "<Unknown DELETE status>"
  963.  
  964.    End Select
  965.  End Function
  966.  
  967.  ' *****************************************************************************
  968.  Function MasterField(tblId, fldId)
  969.       MasterField = strEmptyString
  970.       If fldId > 0 Then
  971.          MasterField = GetFieldFullName(tblId, fldId,-1)
  972.       End If
  973.  End Function
  974.  
  975.  ' *****************************************************************************
  976.  Function PropDescr(prop)
  977.    Dim strDesc
  978.  
  979.    If prop And TS_FLDPROP_NONEDITABLE Then strDesc = "R/O;"
  980.    If prop And TS_FLDPROP_CALC_BEFORE Then strDesc = strDesc & "**x;"
  981.    If prop And TS_FLDPROP_CALC_AFTER Then strDesc = strDesc & "x**;"
  982.    If prop And TS_FLDPROP_CALC_ADD_CUR Then strDesc = strDesc & "CV+=;"
  983.    If prop And TS_FLDPROP_CALC_EMPTY_INVALID Then strDesc = strDesc & "no '';"
  984.    If prop And TS_FLDPROP_CALC_EMPTY_SKIP_CALC Then strDesc = strDesc & "''==skip calc;"
  985.    If prop And TS_FLDPROP_CALC_EMPTY_TREAT_ZERO Then strDesc = strDesc & "''== FV=0 & calc;"
  986.    If prop And TS_FLDPROP_CHECKBOXES Then strDesc = strDesc & "[ ];"
  987.  
  988.    If Len(strDesc) > 1 Then strDesc = Left(strDesc,Len(strDesc)-1)
  989.    PropDescr = strDesc
  990.  End Function
  991.  
  992.  
  993. ' *****************************************************************************
  994.  
  995. ' Use of shell.redirectHTTP suggested by Jeff Malin ...
  996. ' return a string as Excel data (rather than ext.writeStream which returns as HTML)
  997. Function writeResponse(strContent , strContentType)
  998.   dim Response
  999.   Response = "HTTP/1.1 200 OK" & vbcrlf & _
  1000.    "Date: " & now & vbcrlf & _
  1001.    "Server: Microsoft-IIS/6.0" & vbcrlf & _
  1002.    "Expires: 0" & vbcrlf & _
  1003.    "Connection: close" & vbcrlf & _
  1004.    "Content-Type: " & strContentType & "; charset=UTF-8" & vbcrlf
  1005.   strContent = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" & vbcrlf & strContent
  1006.   shell.redirectHTTP = Response & vbcrlf & strContent ' Must separate the headers from the Content with a linefeed
  1007. end function
  1008.  
  1009.  
  1010. ' see: http://en.wikipedia.org/wiki/Microsoft_Excel
  1011.  
  1012. ' application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  1013. ' application/vnd.ms-excel
  1014. ' application/msexcel
  1015. ' application/csv
  1016. ' text/csv
  1017.  
  1018.  
  1019.  
# Change User Description Committed
#1 8137 Paul M. Thompson Initial test of accessing files in Google Drive via junction in the P4 workspace. 13 years ago