+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="AltTable.ascx.vb" \r
- Inherits="AltTable" %>\r
+++ /dev/null
-Partial Class AltTable\r
-Inherits System.Web.UI.UserControl\r
-\r
-public TblName as string\r
-public TblAlias as string\r
-public Delim as string = ","\r
-public altFields as New ArrayList()\r
-\r
-Public Property FieldList() As String\r
- Get\r
- dim i as Integer\r
- dim result as String=""\r
- for i=0 to altFields.Count-1\r
- if i > 0 then result &= Delim\r
- result &= altFields(i).ColName\r
- next\r
- Return result\r
- End Get\r
- Set(ByVal Value As String)\r
- dim i as Integer\r
- dim arFields=Value.split(Delim)\r
- for i=0 to ubound(arFields)\r
- if i = altFields.Count then altFields.Add(New AltColumnClass())\r
- altFields(i).ColName=arFields(i)\r
- next\r
- End Set\r
-End Property\r
-\r
-Public Property FieldData() As String\r
- Get\r
- dim i as Integer\r
- dim result as String=""\r
- for i=0 to altFields.Count-1\r
- if i > 0 then result &= Delim\r
- result &= altFields(i).Data\r
- next\r
- Return result\r
- End Get\r
- Set(ByVal Value As String)\r
- dim i as Integer\r
- dim arData=Value.split(Delim)\r
- for i=0 to ubound(arData)\r
- if IsNothing(altFields(i)) then altFields(i)=New AltColumnClass()\r
- altFields(i).Data=arData(i)\r
- next\r
- End Set\r
-End Property\r
-\r
-\r
-Public Function FieldCondition(FieldIdx as Integer, optional WithAlias as Boolean=true) As String\r
- dim s as String = altFields(FieldIdx).ColName & "=" & altFields(FieldIdx).Data\r
- if WithAlias then s = TblAlias & "." & s\r
- FieldCondition=s\r
-End Function\r
-\r
-\r
-Public Function KeyCondition(optional WithAlias as Boolean=false) As String\r
- dim i as Integer\r
- dim k as String = ""\r
- for i=0 to altFields.Count-1\r
- if altFields(i).isKey then\r
- k &= " AND (" & FieldCondition(i,WithAlias) & ")"\r
- end if\r
- next\r
- KeyCondition=k\r
-End Function\r
-\r
-\r
-Public Class AltColumnClass\r
- public ColName as string\r
- public Data as string\r
- public isKey as boolean\r
-End Class\r
-\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="GridColumn.ascx.vb" \r
- Inherits="GridColumn" %>\r
+++ /dev/null
-Partial Class GridColumn\r
-Inherits System.Web.UI.UserControl\r
-\r
-' ----------------------------------------------------\r
-' Properties\r
-' ----------------------------------------------------\r
-\r
-Public DataType as String ' maps to LiveGrid column 'type'\r
-Public canSort as Boolean = True\r
-Public canDrag as Boolean = False\r
-Public canFilter as Boolean = True\r
-Public control as String ' javascript code to create control object\r
-Public format as String\r
-Public visible as Boolean = True\r
-Public ClassName as String\r
-Public filterUI as String\r
-Public filterCol as Integer = -1\r
-Public spec as String\r
-\r
-' number formatting\r
-Public multiplier as Double = 1.0\r
-Public decPlaces as Integer = 0\r
-Public decPoint as String\r
-Public thouSep as String\r
-Public negSign as String\r
-Public prefix as String\r
-Public suffix as String\r
-\r
-' date formatting\r
-Public dateFmt as String\r
-\r
-' LiveGrid Forms\r
-Public pattern as String\r
-Public min as String\r
-Public max as String\r
-Public Help as String\r
-Public TxtAreaRows as Integer = 4\r
-Public TxtAreaCols as Integer = 80\r
-Public ColName as String\r
-Public ColData as String\r
-Public SelectSql as String\r
-Public SelectCtl as String\r
-Public SelectFilter as String\r
-Public SelectValues as String\r
-Public SelectRows as Integer = -1\r
-Public Formula as String\r
-Public TableIdx as Integer = 0\r
-Public FilterFlag as Boolean = False\r
-Public FieldName as String\r
-Public isNullable as Boolean = False\r
-Public Writeable as Boolean = True\r
-Public FixedLength as Boolean\r
-Public isKey as Boolean = False\r
-Public Length as Integer = -1\r
-Public TypeName as String ' .net type\r
-Public panelIdx as Integer = -1\r
-Public ConfirmDeleteColumn as Boolean = False\r
-Public InsertOnly as Boolean = False\r
-Public UpdateOnly as Boolean = False\r
-Public [ReadOnly] as Boolean = False\r
-Public FormView as String\r
-Public AddQuotes as Boolean = True\r
-Public AltTable as String\r
-Public MultiSelect as Boolean = False\r
-Public DescriptionCol as String\r
-Public DescriptionField as String ' should populate automatically based on DescriptionCol\r
-Public noFormBreak as Boolean = False\r
-\r
-Private _EntryType as String\r
-Private _colHeading As String\r
-Private _width As Integer = -1\r
-\r
-\r
-Public Property Heading() As String\r
- Get\r
- Return _colHeading\r
- End Get\r
- Set\r
- _colHeading = value\r
- End Set\r
-End Property\r
-\r
-Public Property Width() As Integer\r
- Get\r
- Return _width\r
- End Get\r
- Set\r
- _width = value\r
- End Set\r
-End Property\r
-\r
-Public Function isLookupField() as Boolean\r
- isLookupField=CBool(mid(_EntryType,2,1)="L")\r
-End Function\r
-\r
-Public Property EntryType() As String\r
- Get\r
- Return _EntryType\r
- End Get\r
- Set\r
- select case value\r
- case "TA","tinyMCE","R","RL","S","SL","CL","N","B","T","I","F":\r
- case "D": DataType="date"\r
- case "DT": DataType="datetime"\r
- case "H": visible=false\r
- case else: Throw New Exception("Invalid EntryType")\r
- end select\r
- _EntryType = value\r
- End Set\r
-End Property\r
-\r
-\r
-Private function FmtBool(b)\r
- if b then FmtBool="true" else FmtBool="false"\r
-end function\r
-\r
-Public ReadOnly Property script() As String\r
- Get\r
- if not IsNothing(Me.spec) then Return "'" & Me.spec & "'"\r
- dim a as New ArrayList()\r
- if not IsNothing(Me.DataType) then a.Add("type: '" & Me.DataType & "'")\r
- if not IsNothing(Me.control) then a.Add("control: " & Me.control)\r
- if not IsNothing(Me.pattern) then a.Add("pattern: '" & Me.pattern & "'")\r
- if not IsNothing(Me.min) then a.Add("min: '" & Me.min & "'")\r
- if not IsNothing(Me.max) then a.Add("max: '" & Me.max & "'")\r
- if not IsNothing(Me.Help) then a.Add("Help: '" & Me.Help & "'")\r
- if not IsNothing(Me.format) then a.Add("format: '" & Me.format & "'")\r
- if not Me.canSort then a.Add("canSort: false")\r
- if Me.canDrag then a.Add("canDrag: true")\r
- if not Me.canFilter then a.Add("canFilter: false")\r
- if not Me.visible then a.Add("visible: false")\r
- if Me._width >= 0 then a.Add("width: " & Me._width)\r
- if not IsNothing(Me.ColName) then a.Add("ColName: '" & Me.ColName & "'")\r
- if not IsNothing(Me.FieldName) then a.Add("FieldName: '" & Me.FieldName & "'")\r
- if not IsNothing(Me.ClassName) then a.Add("ClassName: '" & Me.ClassName & "'")\r
- if not IsNothing(Me.filterUI) then a.Add("filterUI: '" & Me.filterUI & "'")\r
- if Me.filterCol >= 0 then a.Add("filterCol: " & Me.filterCol)\r
-\r
- if Me.multiplier <> 1.0 then a.Add("multiplier: " & Me.multiplier)\r
- if Me.decPlaces > 0 then a.Add("decPlaces: " & Me.decPlaces)\r
- if not IsNothing(Me.decPoint) then a.Add("decPoint: '" & Me.decPoint & "'")\r
- if not IsNothing(Me.thouSep) then a.Add("thouSep: '" & Me.thouSep & "'")\r
- if not IsNothing(Me.negSign) then a.Add("negSign: '" & Me.negSign & "'")\r
- if not IsNothing(Me.prefix) then a.Add("prefix: '" & Me.prefix & "'")\r
- if not IsNothing(Me.suffix) then a.Add("suffix: '" & Me.suffix & "'")\r
- if not IsNothing(Me.dateFmt) then a.Add("dateFmt: '" & Me.dateFmt & "'")\r
- if Me.panelIdx >= 0 then a.Add("panelIdx: " & Me.panelIdx)\r
-\r
- if not IsNothing(Me.EntryType) then\r
- a.Add("EntryType: '" & Me.EntryType & "'")\r
- if Me.EntryType="D" and ucase(Me.ColData)="TODAY" then\r
- a.Add("ColData: '" & DateTime.Today.ToString("s") & "'")\r
- else\r
- a.Add("ColData: '" & replace(Me.ColData,"'","\'") & "'")\r
- end if\r
- if Me.EntryType="TA" or Me.EntryType="tinyMCE" then\r
- a.Add("TxtAreaRows: " & Me.TxtAreaRows)\r
- a.Add("TxtAreaCols: " & Me.TxtAreaCols)\r
- end if\r
- if not IsNothing(Me.FormView) then a.Add("FormView: '" & Me.FormView & "'")\r
- if not IsNothing(Me.SelectCtl) then a.Add("SelectCtl: '" & Me.SelectCtl & "'")\r
- if not IsNothing(Me.SelectValues) then a.Add("SelectValues: '" & Me.SelectValues & "'")\r
- if Me.Length >= 0 then a.Add("Length: " & Me.Length)\r
- if Me.isNullable then a.Add("isNullable: true")\r
- if Me.isKey then a.Add("isKey: true")\r
- a.Add("Writeable: " & FmtBool(Me.Writeable))\r
- if Me.InsertOnly then a.Add("InsertOnly: true")\r
- if Me.UpdateOnly or not IsNothing(AltTable) then a.Add("UpdateOnly: true")\r
- if Me.ReadOnly then a.Add("ReadOnly: true")\r
- if Me.MultiSelect then a.Add("MultiSelect: true")\r
- if Me.noFormBreak then a.Add("noFormBreak: true")\r
- if not IsNothing(Me.DescriptionField) then a.Add("DescriptionField: '" & Me.DescriptionField & "'")\r
- if Me.SelectRows > 0 then a.Add("SelectRows: " & Me.SelectRows)\r
- end if\r
- Return " {" & String.Join("," & vbCrLf & " ", a.ToArray(Type.GetType("System.String"))) & " }"\r
- End Get\r
-End Property\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="GridPanel.ascx.vb" \r
- Inherits="GridPanel" %>\r
+++ /dev/null
-Partial Class GridPanel\r
-Inherits System.Web.UI.UserControl\r
-\r
-private _heading as string\r
-\r
-public property heading as string\r
- get\r
- return _heading\r
- end get\r
- set\r
- _heading=value\r
- end set\r
-end property\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="LiveGrid.ascx.vb" \r
- Inherits="LiveGrid" Debug="true" %>\r
-<%@ Register TagPrefix="Rico" TagName="Column" Src="GridColumn.ascx" %>\r
-<%@ Register TagPrefix="Rico" TagName="Panel" Src="GridPanel.ascx" %>\r
-<%@ Register TagPrefix="Rico" TagName="AltTable" Src="AltTable.ascx" %>\r
-<%@ Register TagPrefix="Rico" TagName="sqlParse" Src="sqlParse.ascx" %>\r
-<%@ Register TagPrefix="Rico" TagName="sqlCompatibilty" Src="sqlCompatibilty.ascx" %>\r
-\r
-<script type='text/javascript'>\r
-<%=Me.init_Script %>\r
-<%=Me.globalInitScript%>\r
-</script>\r
-\r
-<%=Me.DebugString%>\r
-<%=Me.Bookmark%>\r
-<table id='<%=Me.UniqueId %>' class='ricoLiveGrid' cellpadding='0' cellspacing='0'>\r
-<thead class='ricoLG_top ui-widget-header'>\r
-<asp:placeholder runat='server' id='LiveGridHeadingsTop' />\r
-<tr id='<%=Me.UniqueId %>_hdg_main'>\r
-<asp:placeholder runat='server' id='LiveGridHeadingsMain' />\r
-</tr>\r
-<asp:placeholder runat='server' id='LiveGridHeadingsBottom' />\r
-</thead>\r
-</table>\r
+++ /dev/null
-Imports System.Data\r
-\r
-Partial Class LiveGrid\r
-Inherits System.Web.UI.UserControl\r
- \r
-' ----------------------------------------------------\r
-' Constants\r
-' ----------------------------------------------------\r
-\r
-Public Const sizeToWindow=-1\r
-Public Const sizeToData=-2\r
-Public Const sizeToBody=-3\r
-Public Const sizeToParent=-4\r
-\r
-' ----------------------------------------------------\r
-' Private Properties\r
-' ----------------------------------------------------\r
-\r
-Private _rows As Integer = sizeToBody\r
-Private _sqlFilters as New ArrayList()\r
-Private _gridHeading As ITemplate = Nothing\r
-Private _headingTop As ITemplate = Nothing\r
-Private _headingBottom As ITemplate = Nothing\r
-Protected globalInitScript as String = ""\r
-Protected HdgContainer As New GridContainer()\r
-Protected DebugString As String\r
-Protected oSqlCompat as sqlCompatibilty\r
-Protected oParseMain as New sqlParse()\r
-\r
-\r
-' ----------------------------------------------------\r
-' Public Properties\r
-' ----------------------------------------------------\r
-Public columns as New ArrayList()\r
-Public dataProvider as String = "ricoQuery.aspx"\r
-Public menuEvent as String = "dblclick"\r
-Public frozenColumns as Integer = 0\r
-Public canSortDefault as Boolean = True\r
-Public canHideDefault as Boolean = True\r
-Public canFilterDefault as Boolean = True\r
-Public allowColResize as Boolean = True\r
-Public highlightElem as String = "menuRow"\r
-Public highlightMethod as String\r
-Public prefetchBuffer as Boolean = True\r
-Public DisplayTimer as Boolean = True\r
-Public DisplayBookmark as Boolean = True\r
-Public Caption as String\r
-Public click as String\r
-Public dblclick as String\r
-Public contextmenu as String\r
-Public headingSort as String\r
-Public beforeInit as String\r
-Public afterInit as String\r
-Public TableFilter as String\r
-Public FilterLocation as Integer = -2\r
-Public FilterAllToken as String\r
-Public FilterBoxMaxLen as Integer = -1\r
-Public FilterAnchorLeft as Boolean = false ' when matching text box values, should they match beginning of string (true) or anywhere in string (false)?
-Public saveColumnWidth as Boolean = True\r
-Public saveColumnFilter as Boolean = False\r
-Public saveColumnSort as Boolean = False\r
-Public cookieDays as Integer\r
-Public DefaultSort as String\r
-Public maxPrint as Integer = -1\r
-Public dndMgrIdx as Integer = -1\r
-Public UsingMinRico as Boolean = False ' using minified version of Rico?\r
-Public sessions as Boolean = True\r
-Public minPageRows as Integer = -1\r
-Public maxPageRows as Integer = -1\r
-Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels\r
-Public debug as Boolean = False\r
-Public LogSqlOnError as Boolean = false ' include sql statement in results if an error occurs (true/false)\r
-\r
-' ----------------------------------------------------\r
-' Public Properties for buffer\r
-' ----------------------------------------------------\r
-Public BufferType as String = "AjaxSQL" ' can be overridden to AjaxXML\r
-Public fmt as String = "xml"\r
-Public largeBufferSize as Integer = -1 ' controls size of client buffer and AJAX fetch size\r
-Public requestParameters as New Hashtable()\r
-\r
-\r
-Public Property rows() As Integer\r
- Get\r
- Return _rows\r
- End Get\r
- Set(ByVal Value As Integer)\r
- _rows=Value\r
- End Set\r
-End Property\r
-\r
-<TemplateContainer(GetType(GridContainer))> _\r
-Public Property GridColumns() As ITemplate\r
- Get\r
- Return _gridHeading\r
- End Get\r
- Set\r
- _gridHeading = value\r
- End Set\r
-End Property\r
-\r
-<TemplateContainer(GetType(GridContainer))> _\r
-Public Property HeadingTop() As ITemplate\r
- Get\r
- Return _headingTop\r
- End Get\r
- Set\r
- _headingTop = value\r
- End Set\r
-End Property\r
-\r
-<TemplateContainer(GetType(GridContainer))> _\r
-Public Property HeadingBottom() As ITemplate\r
- Get\r
- Return _headingBottom\r
- End Get\r
- Set\r
- _headingBottom = value\r
- End Set\r
-End Property\r
-\r
-Public WriteOnly Property sqlQuery() As String\r
- Set(ByVal SqlText As String)\r
- if oParseMain.ParseSelect(SqlText) then\r
- ' sync column headings\r
- dim i as Integer\r
- for i=0 to oParseMain.SelectList.Count-1\r
- if i < Me.columns.Count then\r
- if IsNothing(Me.columns(i).Heading) then\r
- Me.columns(i).Heading=oParseMain.SelectList(i).name\r
- else\r
- oParseMain.SelectList(i).name=Me.columns(i).Heading\r
- end if\r
- else\r
- AddCalculatedField(oParseMain.SelectList(i).name, oParseMain.SelectList(i).sql)\r
- end if\r
- next\r
- if sessions then session.contents(Me.UniqueId)=oParseMain\r
- else\r
- Throw New Exception("Invalid SQL statement")\r
- end if\r
- End Set\r
-End Property\r
-\r
-Public ReadOnly Property ParseClone() As object\r
- Get\r
- return oParseMain.Clone()\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property TimerSpan() As String\r
- Get\r
- if Me.DisplayTimer then\r
- Return "<span id='" & Me.UniqueId & "_timer' class='ricoSessionTimer'> </span>"\r
- else\r
- Return ""\r
- end if\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property BookmarkSpan() As String\r
- Get\r
- if Me.DisplayBookmark then\r
- Return "<span id='" & Me.UniqueId & "_bookmark'> </span>"\r
- else\r
- Return ""\r
- end if\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property SaveMsgSpan() As String\r
- Get\r
- if Me.formView then\r
- Return "<span id='" & Me.UniqueId & "_savemsg' class='ricoSaveMsg'></span>"\r
- else\r
- Return ""\r
- end if\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property CaptionSpan() As String\r
- Get\r
- if IsNothing(Caption) then\r
- Return ""\r
- else\r
- Return "<span id='" & Me.UniqueId & "_caption' class='ricoCaption'>" & Me.Caption & "</span>"\r
- end if\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property FilterIcon() As String\r
- Get\r
- if FilterLocation >= -1 then\r
- Return "<a id='ex3_filterLink' href='#' style='margin-right:1em;'></a>"\r
- else\r
- Return ""\r
- end if\r
- End Get\r
-End Property\r
-\r
-Protected ReadOnly Property Bookmark() As String\r
- Get\r
- if Me.DisplayBookmark or Me.DisplayTimer or not IsNothing(Caption) then\r
- Return "<p class='ricoBookmark'>" & Me.CaptionSpan & Me.TimerSpan & Me.FilterIcon & Me.BookmarkSpan & Me.SaveMsgSpan & "</p>"\r
- else\r
- Return ""\r
- end if\r
- End Get\r
-End Property\r
-\r
-Private function FmtBool(b)\r
- if b then FmtBool="true" else FmtBool="false"\r
-end function\r
-\r
-Protected ReadOnly Property init_Script() As String\r
- Get\r
- Dim script as New System.Text.StringBuilder(), confirmCol as Integer=0\r
- script.Append("var " & Me.UniqueId & " = {};" & vbCrLf)\r
- script.Append("function " & Me.UniqueId & "_init" & "() {" & vbCrLf)\r
- if not IsNothing(beforeInit) then script.Append(beforeInit & vbCrLf)\r
-\r
- ' grid options\r
-\r
- script.Append(" " & optionsVar & " = {" & vbCrLf)\r
- script.Append(" visibleRows: " & Me.rows & "," & vbCrLf)\r
- script.Append(" frozenColumns: " & frozenColumns & "," & vbCrLf)\r
- script.Append(" canSortDefault: " & FmtBool(canSortDefault) & "," & vbCrLf)\r
- script.Append(" canHideDefault: " & FmtBool(canHideDefault) & "," & vbCrLf)\r
- script.Append(" canFilterDefault: " & FmtBool(canFilterDefault) & "," & vbCrLf)\r
- script.Append(" allowColResize: " & FmtBool(allowColResize) & "," & vbCrLf)\r
- script.Append(" highlightElem: '" & highlightElem & "'," & vbCrLf)\r
- if not IsNothing(highlightMethod) then script.Append(" highlightMethod: '" & highlightMethod & "'," & vbCrLf)\r
- script.Append(" prefetchBuffer: " & FmtBool(prefetchBuffer) & "," & vbCrLf)\r
- script.Append(" menuEvent: '" & menuEvent & "'," & vbCrLf)\r
- if not IsNothing(RecordName) then script.Append(" RecordName: '" & RecordName & "'," & vbCrLf)\r
- script.Append(" saveColumnInfo: {width:" & FmtBool(saveColumnWidth) & ", filter:" & FmtBool(saveColumnFilter) & ", sort:" & FmtBool(saveColumnSort) & "}," & vbCrLf)\r
- if not IsNothing(cookieDays) then script.Append(" cookieDays: " & cookieDays & "," & vbCrLf)\r
- \r
- if panels.count > 0 then\r
- script.Append(" PanelNamesOnTabHdr: " & FmtBool(PanelNamesOnTabHdr) & "," & vbCrLf)\r
- script.Append(" panels: ['" & join(panels.ToArray(),"','") & "']," & vbCrLf)\r
- end if\r
- if not IsNothing(headingSort) then script.Append(" headingSort: '" & headingSort & "'," & vbCrLf)\r
- if not IsNothing(click) then script.Append(" click: " & click & "," & vbCrLf)\r
- if not IsNothing(dblclick) then script.Append(" dblclick: " & dblclick & "," & vbCrLf)\r
- if not IsNothing(contextmenu) then script.Append(" contextmenu: " & contextmenu & "," & vbCrLf)\r
- if FilterLocation >= -1 then script.Append(" FilterLocation: " & FilterLocation & "," & vbCrLf)\r
- if not IsNothing(FilterAllToken) then script.Append(" FilterAllToken: '" & FilterAllToken & "'," & vbCrLf)\r
- if FilterBoxMaxLen >= 0 then script.Append(" FilterBoxMaxLen: " & FilterBoxMaxLen & "," & vbCrLf)\r
- if FilterAnchorLeft then script.Append(" FilterAnchorLeft: " & FmtBool(FilterAnchorLeft) & "," & vbCrLf)\r
- if maxPrint >= 0 then script.Append(" maxPrint: " & maxPrint & "," & vbCrLf)\r
- if dndMgrIdx >= 0 then script.Append(" dndMgrIdx: " & dndMgrIdx & "," & vbCrLf)\r
- if minPageRows >= 0 then script.Append(" minPageRows: " & minPageRows & "," & vbCrLf)\r
- if maxPageRows >= 0 then script.Append(" maxPageRows: " & maxPageRows & "," & vbCrLf)\r
- if defaultWidth > 0 then script.Append(" defaultWidth: " & defaultWidth & "," & vbCrLf)\r
- \r
- if formView then\r
- script.Append(" canAdd: " & FmtBool(canAdd) & "," & vbCrLf)\r
- script.Append(" canEdit: " & FmtBool(canEdit) & "," & vbCrLf)\r
- script.Append(" canClone: " & FmtBool(canClone) & "," & vbCrLf)\r
- script.Append(" canDelete: " & FmtBool(canDelete) & "," & vbCrLf)\r
- script.Append(" ConfirmDelete: " & FmtBool(ConfirmDelete) & "," & vbCrLf)\r
- script.Append(" TableSelectNew: '" & TableSelectNew & "'," & vbCrLf)\r
- script.Append(" TableSelectNone: '" & TableSelectNone & "'," & vbCrLf)\r
- if panelHeight > 0 then script.Append(" panelHeight: " & panelHeight & "," & vbCrLf)\r
- if panelWidth > 0 then script.Append(" panelWidth: " & panelWidth & "," & vbCrLf)\r
- if maxDisplayLen > 0 then script.Append(" maxDisplayLen: " & maxDisplayLen & "," & vbCrLf)\r
- if not IsNothing(formOpen) then script.Append(" formOpen: " & formOpen & "," & vbCrLf)\r
- if not IsNothing(formClose) then script.Append(" formClose: " & formClose & "," & vbCrLf)\r
- if not IsNothing(formSubmit) then script.Append(" formSubmit: " & onSubmitResponse & "," & vbCrLf)\r
- if not IsNothing(onSubmitResponse) then script.Append(" onSubmitResponse: " & onSubmitResponse & "," & vbCrLf)\r
- if not IsNothing(showSaveMsg) then script.Append(" showSaveMsg: '" & showSaveMsg & "'," & vbCrLf)\r
- end if\r
- script.Append(" columnSpecs : [" & vbCrLf)\r
- Dim c as Integer\r
- for c=0 to columns.count-1\r
- if c > 0 then script.Append("," & vbCrLf)\r
- script.Append(CType(columns(c),GridColumn).script)\r
- if columns(c).ConfirmDeleteColumn then confirmCol=c\r
- next\r
- script.Append("]")\r
- if formView then script.Append("," & vbCrLf & "ConfirmDeleteCol: " & confirmCol)\r
- script.Append(vbCrLf & " }" & vbCrLf)\r
-\r
- ' buffer\r
-\r
- dim a as New ArrayList()\r
- script.Append(" " & bufferOptVar & " = {")\r
- if requestParameters.Count > 0 then\r
- Dim param As DictionaryEntry\r
- For Each param In requestParameters\r
- a.Add(vbCrLf & " {name:'" & param.Key & "',value:'" & param.Value & "'}")\r
- Next\r
- script.Append(vbCrLf & " requestParameters: [" & String.Join(",", a.ToArray(Type.GetType("System.String"))) & vbCrLf & " ]")\r
- end if\r
- if BufferType="AjaxSQL" then\r
- if a.Count > 0 then script.Append(",")\r
- script.Append(vbCrLf & " TimeOut: " & Session.Timeout & ",")\r
- if largeBufferSize > 0 then script.Append(vbCrLf & " largeBufferSize: " & largeBufferSize & ",")\r
- script.Append(vbCrLf & " fmt: '" & fmt & "'")\r
- end if\r
- script.Append(vbCrLf & " }" & vbCrLf)\r
- script.Append(" " & bufferVar & " = new Rico.Buffer." & BufferType & "('" & dataProvider & "', " & bufferOptVar & ");" & vbCrLf)\r
-\r
- ' grid\r
-\r
- script.Append(" " & gridVar & " = new Rico.LiveGrid ('" & Me.UniqueId & "', " & bufferVar & ", " & optionsVar & ");" & vbCrLf)\r
- if not IsNothing(menuEvent) then\r
- script.Append(" " & gridVar & ".menu = new Rico.GridMenu();" & vbCrLf)\r
- end if\r
-\r
- ' form\r
-\r
- if formView then\r
- script.Append(" if(typeof " & Me.UniqueId & "_FormInit=='function') " & Me.UniqueId & "_FormInit();" & vbCrLf)\r
- script.Append(" " & formVar & "=new Rico.TableEdit(" & gridVar & ");" & vbCrLf)\r
- end if\r
-\r
- script.Append(" if(typeof " & Me.UniqueId & "_InitComplete=='function') " & Me.UniqueId & "_InitComplete();" & vbCrLf)\r
- if not IsNothing(afterInit) then script.Append(afterInit & vbCrLf)\r
-\r
- script.Append("}" & vbCrLf)\r
- Return script.ToString\r
- End Get\r
-End Property\r
-\r
-\r
-' ----------------------------------------------------\r
-' Properties for LiveGridForms\r
-' ----------------------------------------------------\r
-\r
-Public dbConnection as object\r
-Public formView as Boolean = false\r
-Public TableSelectNew as String = "___new___"\r
-Public TableSelectNone as String = ""\r
-Public canAdd as Boolean = true\r
-Public canEdit as Boolean = true\r
-Public canClone as Boolean = false\r
-Public canDelete as Boolean = true\r
-Public ConfirmDelete as Boolean = true\r
-Public RecordName as String\r
-Public PanelNamesOnTabHdr as Boolean = true\r
-Public showSaveMsg as String\r
-Public dbDialect as String\r
-Public panels as New ArrayList()\r
-Public panelHeight as Integer = -1\r
-Public panelWidth as Integer = -1\r
-Public maxDisplayLen as Integer = -1\r
-\r
-' events\r
-Public formOpen as String\r
-Public formClose as String\r
-Public formSubmit as String\r
-Public onSubmitResponse as String\r
-\r
-Public gridVar as String\r
-Public formVar as String\r
-Public bufferVar as String\r
-Public bufferOptVar as String ' name of buffer options js var\r
-Public optionsVar as String ' name of grid options js var\r
-\r
-Protected Tables as New ArrayList()\r
-Protected _action As String\r
-Protected MainTbl as Integer = -1\r
-\r
-\r
-Public Property TableName() As String\r
- Get\r
- if MainTbl >= 0 then\r
- Return Tables(MainTbl).TblName\r
- else\r
- Return Nothing\r
- end if\r
- End Get\r
- Set\r
- MainTbl=Tables.Count\r
- dim tab as new AltTable()\r
- tab.TblName=value\r
- tab.TblAlias="t"\r
- AddTable(tab)\r
- End Set\r
-End Property\r
-\r
-Public Function AddTable(t as AltTable) as Integer\r
- AddTable=Tables.Count\r
- if IsNothing(t.TblAlias) then t.TblAlias="a" & Tables.Count\r
- Tables.Add(t)\r
-End Function\r
-\r
-Public ReadOnly Property action() As String\r
- Get\r
- Return _action\r
- End Get\r
-End Property\r
-\r
-Public ReadOnly Property CurrentField() As GridColumn\r
- Get\r
- Return columns(columns.count-1)\r
- End Get\r
-End Property\r
-\r
-\r
-' ----------------------------------------------------\r
-' Methods\r
-' ----------------------------------------------------\r
-Sub Page_Init()\r
- formVar=Me.UniqueId & "['edit']"\r
- gridVar=Me.UniqueId & "['grid']"\r
- bufferVar=Me.UniqueId & "['buffer']"\r
- bufferOptVar=Me.UniqueId & "['bufferopt']"\r
- optionsVar=Me.UniqueId & "['options']"\r
- dim actionparm as String="_action_" & Me.UniqueId\r
- _action=trim(Request.QueryString(actionparm))\r
- if _action="" then _action=trim(Request.Form(actionparm))\r
- if _action="" then _action="table" else _action=lcase(_action)\r
-\r
- If Not (_gridHeading Is Nothing) Then\r
- _gridHeading.InstantiateIn(HdgContainer)\r
- For Each ctrl As Control In HdgContainer.Controls\r
- If TypeOf(ctrl) is GridColumn then\r
- AddColumn(CType(ctrl,GridColumn))\r
- ElseIf TypeOf(ctrl) is GridPanel then\r
- panels.Add(CType(ctrl,GridPanel).heading)\r
- ElseIf TypeOf(ctrl) is AltTable then\r
- AddTable(ctrl)\r
- end if\r
- Next\r
- End If\r
-\r
- If Not (_headingTop Is Nothing) Then\r
- Dim container As New GridContainer()\r
- _headingTop.InstantiateIn(container)\r
- LiveGridHeadingsTop.Controls.Add(container)\r
- End If\r
- \r
- If Not (_headingBottom Is Nothing) Then\r
- Dim container As New GridContainer()\r
- _headingBottom.InstantiateIn(container)\r
- LiveGridHeadingsBottom.Controls.Add(container)\r
- End If\r
-End Sub\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Adds a new column to grid, returns column index\r
-' -------------------------------------------------------------\r
-Public Function AddColumn(ColumnObj as GridColumn) as integer\r
- if ColumnObj.isLookupField() then\r
- \r
- ' this items get applied to the lookup field instead of the code field\r
- dim Hdg as string = ColumnObj.Heading\r
- dim width as integer = ColumnObj.Width\r
- dim filterUI as string = ColumnObj.filterUI\r
- dim ConfirmDelete as boolean = ColumnObj.ConfirmDeleteColumn\r
- dim DescriptionCol as String = ColumnObj.DescriptionCol\r
-\r
- ColumnObj.Heading=Hdg & " Code"\r
- ColumnObj.panelIdx=panels.count-1\r
- ColumnObj.FieldName=ExtFieldId(columns.count)\r
- ColumnObj.filterUI=Nothing\r
- ColumnObj.Width=-1\r
- ColumnObj.visible=false\r
- ColumnObj.ConfirmDeleteColumn=false\r
- if not IsNothing(DescriptionCol) then\r
- ColumnObj.DescriptionField=ExtFieldId(columns.count+1)\r
- end if\r
- columns.Add(ColumnObj)\r
- \r
- ColumnObj=new GridColumn()\r
- ColumnObj.filterUI=filterUI\r
- ColumnObj.Width=width\r
- ColumnObj.Heading=Hdg\r
- ColumnObj.ConfirmDeleteColumn=ConfirmDelete\r
- if IsNothing(DescriptionCol) then\r
- ColumnObj.Formula="" ' to be filled in by FormSqlQuery()\r
- else\r
- ColumnObj.ColName=DescriptionCol\r
- ColumnObj.FormView="hidden"\r
- ColumnObj.EntryType="T"\r
- end if\r
- end if\r
- ColumnObj.panelIdx=panels.count-1\r
- ColumnObj.FieldName=ExtFieldId(columns.count)\r
- AddColumn=columns.count\r
- columns.Add(ColumnObj)\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Adds a new column to grid, returns column index\r
-' -------------------------------------------------------------\r
-Public Function AddCalculatedField(Heading as string, ColumnFormula as string, optional width as integer = -1, optional ClassName as string = "") as GridColumn\r
- Dim ColumnObj as New GridColumn()\r
- if left(ColumnFormula,1) <> "(" then ColumnFormula="(" & ColumnFormula & ")"\r
- ColumnObj.ColName="Calc_" & columns.count\r
- ColumnObj.Formula=ColumnFormula\r
- ColumnObj.Heading=Heading\r
- if width >= 0 then ColumnObj.Width=width\r
- if ClassName <> "" then ColumnObj.ClassName=ClassName\r
- AddColumn(ColumnObj)\r
- AddCalculatedField=ColumnObj\r
-End Function\r
-\r
-\r
-Private Function IsFieldName(s) as boolean\r
- dim i as integer, c as string\r
- i=1\r
- IsFieldName=false\r
- while i <= len(s)\r
- c=mid(s,i,1)\r
- if (c >= "0" and c <= "9" and i > 1) or (c >= "A" and c <= "Z") or (c >= "a" and c <= "z") or (c = "_") then\r
- i=i+1\r
- else\r
- exit function\r
- end if\r
- end while\r
- IsFieldName=(i > 1)\r
-End Function\r
-\r
-\r
-' name used external to this script\r
-Private function ExtFieldId(i) as string\r
- ExtFieldId=Me.UniqueId & "_" & i\r
-end function\r
-\r
-\r
-Private function FormatValue(v as String, ByVal ColIdx as Integer) as String\r
- dim addquotes as Boolean = columns(ColIdx).AddQuotes\r
- select case left(columns(ColIdx).EntryType,1)\r
- case "I","F":\r
- addquotes=false\r
- if not IsNumeric(v) then v=""\r
- case "N":\r
- if v=TableSelectNew then\r
- v=trim(Request.Form("textnew__" & ExtFieldId(ColIdx)))\r
- elseif v=TableSelectNone then\r
- v=""\r
- end if\r
- case "S","R":\r
- if v=TableSelectNone then v=""\r
- end select\r
- if v="" and columns(ColIdx).isNullable then\r
- FormatValue="NULL"\r
- elseif addquotes then\r
- FormatValue=oSqlCompat.addQuotes(v)\r
- else\r
- FormatValue=v\r
- end if\r
-end function\r
-\r
-\r
-Private function FormatFormValue(idx as Integer) as String\r
- dim v as String\r
- if IsNothing(columns(idx).EntryType) then exit function\r
- if columns(idx).EntryType="H" or columns(idx).FormView="exclude" then\r
- v=columns(idx).ColData\r
- else\r
- v=trim(Request.Form(ExtFieldId(idx)))\r
- end if\r
- FormatFormValue=FormatValue(v,idx)\r
-end function\r
-\r
-\r
-Private Function AltTableJoinClause(AltTabIdx as Integer) as String\r
- dim i as Integer, Condition as String\r
- for i=0 to columns.Count-1\r
- if columns(i).TableIdx=MainTbl and columns(i).isKey then\r
- AddCondition(Condition, Tables(MainTbl).TblAlias & "." & columns(i).ColName & "=" & Tables(AltTabIdx).TblAlias & "." & columns(i).ColName)\r
- end if\r
- next\r
- for i=0 to Tables(AltTabIdx).altFields.Count-1\r
- if Tables(AltTabIdx).altFields(i).isKey then\r
- AddCondition(Condition, Tables(AltTabIdx).FieldCondition(i))\r
- end if\r
- next\r
- AltTableJoinClause=Condition\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Add a condition to a where or having clause\r
-' -------------------------------------------------------------\r
-Public Sub AddCondition(ByRef WhereClause as String, ByVal NewCondition as String)\r
- if IsNothing(NewCondition) then exit sub\r
- If IsNothing(WhereClause) Then\r
- WhereClause = "(" & NewCondition & ")"\r
- Else\r
- WhereClause &= " AND (" & NewCondition & ")"\r
- End If\r
-End Sub\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the form value of a column based on its column name (insert or update action)\r
-' -------------------------------------------------------------\r
-Public Function FormValue(ByVal SearchName as String) as String\r
- Dim idx as Integer = ColIndex(SearchName)\r
- if idx < 0 then\r
- FormValue = ""\r
- else\r
- FormValue = FormatFormValue(idx)\r
- end if\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the index of a column based on its column name, or -1 if not found\r
-' -------------------------------------------------------------\r
-Public Function ColIndex(ByVal SearchName as String) as Integer\r
- dim i as Integer\r
- ColIndex=-1\r
- for i=0 to columns.Count-1\r
- if columns(i).ColName=SearchName then\r
- ColIndex=i\r
- Exit Function\r
- end if\r
- next\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the a column object based on its column name, or Nothing if not found\r
-' -------------------------------------------------------------\r
-Public Function getColumnByName(ByVal SearchName as String) as GridColumn\r
- dim i as Integer\r
- for i=0 to columns.Count-1\r
- if columns(i).ColName=SearchName then\r
- getColumnByName=columns(i)\r
- Exit Function\r
- end if\r
- next\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the a column object based on its heading, or Nothing if not found\r
-' -------------------------------------------------------------\r
-Public Function getColumnByHeading(ByVal SearchName as String) as GridColumn\r
- dim i as Integer\r
- for i=0 to columns.Count-1\r
- if columns(i).Heading=SearchName then\r
- getColumnByHeading=columns(i)\r
- Exit Function\r
- end if\r
- next\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the index of a table based on its table name\r
-' -------------------------------------------------------------\r
-Public Function TabIndex(ByVal SearchName as String)\r
- dim i as Integer\r
- for i=0 to Tables.Count-1\r
- if Tables(i).TblName=SearchName then\r
- TabIndex=i\r
- Exit Function\r
- end if\r
- next\r
-End Function\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Return the index of the new filter\r
-' -------------------------------------------------------------\r
-Public Function AddFilter(ByVal newfilter as String)\r
- AddFilter=_sqlFilters.Count\r
- _sqlFilters.Add(newfilter)\r
-End Function\r
-\r
-\r
-' form where clause based on table's primary key\r
-Public function MainTableKeyWhereClause() as String\r
- MainTableKeyWhereClause = TableKeyWhereClause(MainTbl)\r
-End Function\r
-\r
-\r
-' form where clause based on table's primary key\r
-Private function TableKeyWhereClause(TabIdx as Integer) as String\r
- dim i as Integer, w as String\r
- for i=0 to columns.Count-1\r
- if (columns(i).TableIdx=MainTbl or columns(i).TableIdx=TabIdx) and IsNothing(columns(i).Formula) and columns(i).isKey then\r
- AddCondition(w, columns(i).ColName & "=" & FormatValue(trim(Request.Form("_k" & i)),i))\r
- end if\r
- next\r
- if IsNothing(w) then\r
- 'Throw New Exception("no key value")\r
- else\r
- TableKeyWhereClause=" WHERE " & w\r
- end if\r
-end function\r
-\r
-\r
-Protected Sub GetColumnInfo()\r
- dim t as Integer, r as Integer, c as Integer, colname as String, schemaTable As DataTable\r
- Dim restrictions(3) As String\r
- if IsNothing(Me.dbConnection) then exit sub\r
- for t=0 to Tables.Count-1\r
- if debug then DebugString &= "<p>Table: " & Tables(t).TblName & " tblidx=" & t & " colcnt=" & columns.Count\r
-\r
- Dim command = Me.dbConnection.CreateCommand()\r
- command.CommandText = "select * from " & Tables(t).TblName\r
- dim rdr = command.ExecuteReader(CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly)\r
- schemaTable = rdr.GetSchemaTable()\r
- For Each colinfo As DataRow In schemaTable.Rows\r
- colname = colinfo("ColumnName").ToString\r
- for c=0 to columns.Count-1\r
- if t=columns(c).TableIdx and colname=columns(c).ColName then\r
- with columns(c)\r
- .isNullable=CBool(colinfo("AllowDBNull"))\r
- .TypeName=replace(colinfo("DataType").ToString(),"System.","")\r
- if .TypeName<>"String" AndAlso not IsDBNull(colinfo("NumericPrecision")) AndAlso colinfo("NumericPrecision")<>0 then\r
- .Length=colinfo("NumericPrecision")\r
- elseif not IsDBNull(colinfo("ColumnSize")) then\r
- .Length=colinfo("ColumnSize")\r
- end if\r
- .Writeable=not colinfo("IsReadOnly")\r
- .isKey=colinfo("IsKey")\r
- 'columns(c).FixedLength=((colinfo("COLUMN_FLAGS") and &H0000010) <> 0)\r
- if debug then DebugString &= "<br> Column: " & colname & " type=" & .TypeName & " len=" & .Length & " nullable=" & .isNullable & " isKey=" & .isKey\r
- end with\r
- exit for\r
- end if\r
- next\r
- \r
- for c=0 to Tables(t).altFields.Count-1\r
- if colname=Tables(t).altFields(c).ColName then\r
- Tables(t).altFields(c).isKey=colinfo("IsKey")\r
- exit for\r
- end if\r
- next\r
- Next\r
- rdr.Close()\r
- \r
- ' AllowDBNull is not accurate when using Jet driver\r
- if InStr(Me.dbConnection.ConnectionString,"Microsoft.Jet") > 0 then\r
- restrictions(2)=Tables(t).TblName\r
- schemaTable = Me.dbConnection.GetSchema("Columns",restrictions)\r
- For Each colinfo As DataRow In schemaTable.Rows\r
- colname = colinfo("column_name").ToString\r
- for c=0 to columns.Count-1\r
- if t=columns(c).TableIdx and colname=columns(c).ColName then\r
- with columns(c)\r
- .isNullable=CBool(colinfo("is_nullable"))\r
- if debug then DebugString &= "<br> Column: " & colname & " nullable=" & .isNullable\r
- end with\r
- exit for\r
- end if\r
- next\r
- Next\r
- end if\r
- Next\r
-End Sub\r
-\r
-\r
-Protected Function UpdateDatabase(sqltext as String, actiontxt as String) as String\r
- dim cnt as Integer\r
- if IsNothing(Me.dbConnection) then\r
- UpdateDatabase="<p>ERROR: no database connection</p>"\r
- else\r
- Try\r
- Dim command = Me.dbConnection.CreateCommand()\r
- command.CommandText = sqltext\r
- cnt=command.ExecuteNonQuery()\r
- UpdateDatabase="<p class='ricoFormResponse " & actiontxt & "Successfully'></p>"\r
- Catch ex As Exception\r
- dim msg="<p>ERROR: unable to update database - " & server.HTMLencode(ex.Message.ToString()) & "</p>"\r
- if LogSqlOnError then msg &= "<br>" & server.HTMLencode(sqltext)\r
- UpdateDatabase=msg\r
- End Try\r
- 'if debug then msg &= " - " & sqltext & " - Records affected: " & cnt\r
- end if\r
-End Function\r
-\r
-\r
-Public Sub DeleteRecord(writer as HTMLTextWriter)\r
- dim sqltext as String = "DELETE FROM " & Tables(MainTbl).TblName & TableKeyWhereClause(MainTbl)\r
- writer.WriteLine(UpdateDatabase(sqltext, "deleted"))\r
-End Sub\r
-\r
-\r
-Public Sub InsertRecord(writer as HTMLTextWriter)\r
- dim i as Integer, keyIdx as Integer\r
- dim keyCnt as Integer=0\r
- dim sqlcol as String=""\r
- dim sqlval as String=""\r
- for i=0 to columns.Count-1\r
- if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).UpdateOnly=false then\r
- if columns(i).isKey then\r
- keyCnt=keyCnt+1\r
- keyIdx=i\r
- end if\r
- if columns(i).Writeable then\r
- sqlcol &= "," & columns(i).ColName\r
- sqlval &= "," & FormatFormValue(i)\r
- end if\r
- end if\r
- next\r
- if IsNothing(sqlcol) then\r
- writer.WriteLine("<p>Nothing to add</p>")\r
- else\r
- dim sqltext as String="insert into " & Tables(MainTbl).TblName & " (" & mid(sqlcol,2) & ") values (" & mid(sqlval,2) & ")"\r
- dim updateMsg as String = UpdateDatabase(sqltext, "added")\r
- writer.WriteLine(updateMsg)\r
- end if\r
-End Sub\r
-\r
-\r
-Public Sub UpdateRecord(writer as HTMLTextWriter)\r
- dim i as Integer, sqltext as String, errmsg as String=""\r
- for i=0 to Tables.Count-1\r
- if i<>MainTbl then errmsg &= UpdateAltTableRecords(i)\r
- next\r
- if errmsg<>"" then\r
- writer.WriteLine("<p>" & errmsg & "</p>")\r
- exit sub\r
- end if\r
- for i=0 to columns.Count-1\r
- if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).Writeable and columns(i).InsertOnly=false then\r
- sqltext &= "," & columns(i).ColName & "=" & FormatFormValue(i)\r
- end if\r
- next\r
- if not IsNothing(sqltext) then\r
- sqltext="UPDATE " & Tables(MainTbl).TblName & " SET " & mid(sqltext,2) & TableKeyWhereClause(MainTbl)\r
- writer.WriteLine(UpdateDatabase(sqltext, "updated"))\r
- elseif Tables.Count > 1 then\r
- ' only updated altTable records\r
- writer.WriteLine("<p class='ricoFormResponse updatedSuccessfully'></p>")\r
- else\r
- writer.WriteLine("<p>Nothing to update</p>")\r
- end if\r
-End Sub\r
-\r
-\r
-Private function UpdateAltTableRecords(tabidx as Integer)\r
- dim j as Integer, cnt as Integer\r
- dim sqltext as String, colnames as String, coldata as String\r
- dim whereClause as String, errmsg as String\r
-\r
- ' check for existing record\r
-\r
- whereClause = TableKeyWhereClause(tabidx) & Tables(tabidx).KeyCondition()\r
- sqltext="select count(*) from " & Tables(tabidx).TblName & " " & Tables(tabidx).TblAlias & whereClause\r
- Try\r
- Dim command = Me.dbConnection.CreateCommand()\r
- command.CommandText = sqltext\r
- cnt=command.ExecuteScalar()\r
- Catch ex As Exception\r
- errmsg = "UpdateAltTableRecords Count<br>ERROR: " & server.HTMLencode(ex.Message.ToString())\r
- if LogSqlOnError then errmsg &= "<br>" & sqltext\r
- UpdateAltTableRecords = errmsg\r
- exit function\r
- End Try\r
- \r
- if cnt = 0 then\r
-\r
- ' insert new record\r
- \r
- colnames=""\r
- coldata=""\r
- for j=0 to columns.Count-1\r
- if (columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType)) or columns(j).isKey then\r
- colnames &= "," & columns(j).ColName\r
- coldata &= "," & FormatFormValue(j)\r
- end if\r
- next\r
- for j=0 to Tables(tabidx).altFields.Count-1\r
- colnames &= "," & Tables(tabidx).altFields(j).ColName\r
- coldata &= "," & Tables(tabidx).altFields(j).Data\r
- next\r
- Try\r
- Dim command = Me.dbConnection.CreateCommand()\r
- sqltext="insert into " & Tables(tabidx).TblName & " (" & mid(colnames,2) & ") values (" & mid(coldata,2) & ")"\r
- command.CommandText = sqltext\r
- cnt=command.ExecuteNonQuery()\r
- Catch ex As Exception\r
- errmsg="UpdateAltTableRecords Insert<br>ERROR: " & server.HTMLencode(ex.Message.ToString())\r
- if LogSqlOnError then errmsg &= "<br>" & sqltext\r
- UpdateAltTableRecords = errmsg\r
- End Try\r
-\r
- else\r
-\r
- ' update record\r
- \r
- sqltext=""\r
- for j=0 to columns.Count-1\r
- if columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType) then\r
- sqltext &= "," & columns(j).ColName & "=" & FormatFormValue(j)\r
- end if\r
- next\r
- for j=0 to Tables(tabidx).altFields.Count-1\r
- sqltext &= "," & Tables(tabidx).altFields(j).ColName & "=" & Tables(tabidx).altFields(j).Data\r
- next\r
- if sqltext <> "" then\r
- Try\r
- Dim command = Me.dbConnection.CreateCommand()\r
- sqltext="update " & Tables(tabidx).TblName & " set " & mid(sqltext,2) & whereClause\r
- command.CommandText = sqltext\r
- cnt=command.ExecuteNonQuery()\r
- Catch ex As Exception\r
- errmsg="UpdateAltTableRecords Update<br>ERROR: " & server.HTMLencode(ex.Message.ToString())\r
- if LogSqlOnError then errmsg &= "<br>" & sqltext\r
- UpdateAltTableRecords = errmsg\r
- End Try\r
- end if\r
- end if\r
-end function\r
-\r
-\r
-' -------------------------------------\r
-' form main sql query to populate the grid\r
-' -------------------------------------\r
-Protected Sub FormSqlQuery()\r
- Dim oParseLookup=new sqlParse\r
- Dim oParseSubQry=new sqlParse\r
- Dim i as Integer\r
- Dim j as Integer\r
- Dim s as String\r
- Dim tabidx as Integer\r
- Dim csvPrimaryKey as String\r
- if debug then DebugString &= "<p>FormSqlQuery"\r
- oParseMain.FromClause=Tables(MainTbl).TblName & " t"\r
- for i=0 to Tables.Count-1\r
- if i<>MainTbl then\r
- s="left join " & Tables(i).TblName & " " & Tables(i).TblAlias & " ON " & AltTableJoinClause(i)\r
- oParseMain.AddJoin(s)\r
- end if\r
- next\r
- oParseMain.AddWhereCondition(TableFilter)\r
- \r
- ' build sql for each column\r
- \r
- for i=0 to columns.Count-1\r
- if columns(i).TableIdx>=0 then tabidx=columns(i).TableIdx\r
- if columns(i).FilterFlag then\r
- ' add any column filters to where clause\r
- oParseMain.AddWhereCondition(Tables(tabidx).TblAlias & "." & columns(i).ColName & "='" & columns(i).ColData & "'")\r
- end if\r
-\r
- if not IsNothing(columns(i).Formula) then\r
-\r
- ' computed column\r
-\r
- oParseMain.AddColumn("(" & columns(i).Formula & ")", columns(i).Heading)\r
-\r
- elseif tabidx=MainTbl then\r
-\r
- ' column from main table - avoid subqueries to make it compatible with MS Access & MySQL < v4.1\r
-\r
- if columns(i).isKey then\r
- if not IsNothing(csvPrimaryKey) then csvPrimaryKey &= ","\r
- csvPrimaryKey &= Tables(tabidx).TblAlias & "." & columns(i).ColName\r
- end if\r
- if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then\r
- Dim TblAlias as String="t" & CStr(i)\r
- s=replace(columns(i).SelectSql,"%alias%",TblAlias & ".")\r
- oParseLookup.ParseSelect(s)\r
- if oParseLookup.SelectList.count=2 then\r
- Dim codeField as String=oParseLookup.SelectList(0).sql\r
- Dim descField as String=oParseLookup.SelectList(1).sql\r
- If IsFieldName(descField) Then\r
- descField=TblAlias & "." & descField\r
- Else\r
- descField=replace(replace(descField,"%alias%",TblAlias & "."),"%aliasmain%","t.")\r
- End If\r
- s="left join " & oParseLookup.FromClause & " " & TblAlias & " on t." & columns(i).ColName & "=" & TblAlias & "." & replace(replace(codeField,"%alias%",""),"%aliasmain%","")\r
- if not IsNothing(oParseLookup.WhereClause) then s &= " and " & replace(oParseLookup.WhereClause,"%alias%",TblAlias & ".")\r
- oParseMain.AddJoin(s)\r
- oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)\r
- if IsNothing(columns(i).DescriptionCol) then\r
- columns(i+1).Formula=descField\r
- end if\r
- \r
- else\r
- Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")\r
- end if\r
- else\r
- oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)\r
- end if\r
-\r
- else\r
-\r
- ' column from alt table - no avoiding subqueries here\r
-\r
- if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then\r
- oParseLookup.ParseSelect(columns(i).SelectSql)\r
- if oParseLookup.SelectList.count=2 then\r
- Dim descQuery as String="select " & oParseLookup.SelectList(1).sql & " from " & oParseLookup.FromClause & " where " & _\r
- oParseLookup.SelectList(0).sql & "=" & Tables(tabidx).TblAlias & "." & columns(i).ColName\r
- if not IsNothing(oParseLookup.WhereClause) then descQuery=descQuery & " and " & oParseLookup.WhereClause\r
- oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)\r
- columns(i+1).Formula="(" & descQuery & ")"\r
- else\r
- Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")\r
- end if\r
- else\r
- oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)\r
- end if\r
-\r
- end if\r
-\r
- if not IsNothing(columns(i).EntryType) then\r
- Dim SessionColId as String = ExtFieldId(i)\r
- if InStr("CSNR",left(columns(i).EntryType,1)) > 0 then\r
- if not IsNothing(columns(i).SelectSql) then\r
- s=columns(i).SelectSql\r
- if not IsNothing(columns(i).SelectFilter) then\r
- oParseLookup.ParseSelect(s)\r
- oParseLookup.AddWhereCondition(columns(i).SelectFilter)\r
- s=oParseLookup.UnparseSelect\r
- end if\r
- oParseMain.SelectList(i).LookupQuery=replace(replace(s,"%alias%",""),"%aliasmain%","")\r
- else\r
- oParseMain.SelectList(i).LookupQuery="select distinct " & columns(i).ColName & " from " & Tables(tabidx).TblName & " where " & columns(i).ColName & " is not null"\r
- end if\r
- end if\r
- end if\r
- next\r
- if not IsNothing(DefaultSort) then\r
- oParseMain.AddSort(DefaultSort)\r
- elseif not IsNothing(csvPrimaryKey) then\r
- oParseMain.AddSort(csvPrimaryKey)\r
- end if\r
-End Sub\r
-\r
-\r
-Protected Overrides Sub OnPreRender(ByVal e As System.EventArgs) \r
- MyBase.OnPreRender(e)\r
- if not IsNothing(dbConnection) then\r
- oSqlCompat=New sqlCompatibilty(dbDialect)\r
- end if\r
- \r
- ' Create headings\r
- Dim i as Integer\r
- for i=0 to columns.Count-1\r
- if not IsNothing(columns(i).AltTable) then\r
- columns(i).TableIdx=TabIndex(columns(i).AltTable)\r
- else\r
- columns(i).TableIdx=MainTbl\r
- end if\r
- Dim cell as New TableHeaderCell()\r
- cell.Text=columns(i).Heading\r
- if i<frozenColumns then cell.CssClass="ricoFrozen"\r
- LiveGridHeadingsMain.Controls.Add(cell)\r
- next\r
- \r
- if BufferType="AjaxXML" or not sessions then Me.DisplayTimer=false\r
- if Tables.count > 0 then\r
- Me.GetColumnInfo()\r
- if _action="table" and sessions then\r
- Me.FormSqlQuery()\r
- session.contents(Me.UniqueId)=oParseMain\r
- session.contents(Me.UniqueId & ".filters")=Me._sqlFilters\r
- elseif _action="query" then\r
- Me.FormSqlQuery()\r
- end if\r
- end if\r
-\r
- ' populate globalInitScript\r
- Dim FixedGridScript as String = ""\r
- Dim VarGridScript as String = ""\r
- Dim gblFormView as Boolean = false\r
- If Not Page.IsStartupScriptRegistered("LiveGridInit") Then\r
- For Each ctrl As Control In Page.Controls\r
- If TypeOf(ctrl) is LiveGrid then\r
- if CType(ctrl,LiveGrid).Rows >= 0 then\r
- FixedGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf\r
- else\r
- VarGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf\r
- end if\r
- if CType(ctrl,LiveGrid).formView then gblFormView=true\r
- End If\r
- Next\r
- 'globalInitScript = "Rico.acceptLanguage('" & Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") & "');" & vbCrLf\r
- if not UsingMinRico then\r
- globalInitScript &= "Rico.loadModule('LiveGridAjax','LiveGridMenu');" & vbCrLf\r
- if gblFormView then globalInitScript &= "Rico.loadModule('LiveGridForms');" & vbCrLf\r
- end if\r
- globalInitScript &= "Rico.onLoad( function() {" & vbCrLf\r
- globalInitScript &= FixedGridScript ' initialize grids with fixed # of rows first\r
- globalInitScript &= VarGridScript & "});" & vbCrLf ' then initialize grids with variable # of rows\r
- Page.RegisterStartupScript("LiveGridInit", "")\r
- End If\r
-End Sub\r
-\r
-\r
-Public Class GridContainer\r
- Inherits Control\r
- Implements INamingContainer\r
-End Class\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="SimpleGrid.ascx.vb" \r
- Inherits="SimpleGrid" Debug="true" %>\r
-<%@ Register TagPrefix="Rico" TagName="Column" Src="GridColumn.ascx" %>\r
-\r
+++ /dev/null
-Imports System.Data\r
-\r
-Partial Class SimpleGrid\r
-Inherits System.Web.UI.UserControl\r
-\r
-Private _gridHeading As ITemplate = Nothing\r
-Protected HdgContainer As New GridContainer()\r
-\r
-Public columns as New ArrayList()\r
-Public gridVar as String\r
-Public optionsVar as String ' name of grid options js var\r
-Public FilterLocation as Integer = -2\r
-Public FilterAllToken as String\r
-Public FilterBoxMaxLen as Integer = -1\r
-Public FilterAnchorLeft as Boolean = false ' when matching text box values, should they match beginning of string (true) or anywhere in string (false)?
-Public UsingMinRico as Boolean = False ' using minified version of Rico?\r
-Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels\r
-Public allowColResize as Boolean = True\r
-Public menuEvent as String\r
-public rows as New ArrayList()\r
-public FrozenCols as Integer\r
-private LastRow,LastHeadingRow,ResizeRowIdx\r
-\r
-\r
-\r
-Sub Page_Init()\r
- gridVar=Me.UniqueId & "['grid']"\r
- optionsVar=Me.UniqueId & "['options']"\r
-\r
- If Not (_gridHeading Is Nothing) Then\r
- _gridHeading.InstantiateIn(HdgContainer)\r
- For Each ctrl As Control In HdgContainer.Controls\r
- If TypeOf(ctrl) is GridColumn then\r
- columns.Add(ctrl)\r
- end if\r
- Next\r
- End If\r
-End Sub\r
-\r
-\r
-Public Class HeadingCellClass\r
- Public content As String, span As Integer\r
-\r
- Public Sub New(Optional contentParm As String = "", Optional spanParm As Integer = 1)\r
- content=contentParm\r
- span=spanParm\r
- End Sub\r
-End Class\r
-\r
-class SimpleGridCell\r
- public content as String\r
- private attr As New Hashtable()\r
-\r
- Public Function HeadingCell() as object\r
- Dim s as String, span as Integer\r
- s="<td"\r
- span=1\r
- If attr.contains("colspan") Then\r
- span=CInt(attr("colspan"))\r
- s &= " colspan='" & span & "'"\r
- End If\r
- dim content as String=s & "><div class='ricoLG_col'>" & DataCell("") & "</div></td>"\r
- dim result() as object = {content,span}\r
- HeadingCell = result\r
- End Function\r
-\r
- Public Function DataCell(rowclass as String) as String\r
- dim s as String, k as String\r
- s = "<div"\r
- attr("class")=trim("ricoLG_cell " & attr("class") & " " & rowclass)\r
- for each k in attr.keys\r
- If k<>"colspan" Then s=s & " " & k & "='" & attr(k) & "'"\r
- next\r
- s=s & ">" & content & "</div>"\r
- DataCell=s\r
- End Function\r
-\r
- Public Function HtmlCell()\r
- dim s as String="", k as String\r
- for each k in attr.keys\r
- s &= " " & k & "='" & attr(k) & "'"\r
- next\r
- HtmlCell="<td" & s & ">" & content & "</td>"\r
- End Function\r
-\r
- Public Sub SetAttr(name as String, value as String)\r
- attr(name)=value\r
- End Sub\r
-End class\r
-\r
-\r
-class SimpleGridRow\r
- public cells as New ArrayList()\r
- private attr As New Hashtable()\r
- private CurrentCell as SimpleGridCell\r
-\r
- Public Sub AddCell(ByVal content as String)\r
- CurrentCell=new SimpleGridCell()\r
- cells.Add(CurrentCell)\r
- CurrentCell.content=content\r
- End Sub\r
- \r
- Public Function HeadingRow(ByVal c1 as Integer, ByVal c2 as Integer) as String\r
- dim s as String, a\r
- dim cellidx as Integer=0\r
- dim colidx as Integer=0\r
- while colidx < c1 and cellidx < cells.count\r
- a=cells(cellidx).HeadingCell()\r
- colidx+=CInt(a(1))\r
- cellidx+=1\r
- end while\r
- while (colidx <= c2 or c2=-1) and cellidx < cells.count\r
- a=cells(cellidx).HeadingCell()\r
- s &= a(0)\r
- colidx+=CInt(a(1))\r
- cellidx+=1\r
- end while\r
- HeadingRow = s\r
- End Function\r
- \r
- Public Function HeadingClass()\r
- HeadingClass=trim("ricoLG_hdg " & attr("class"))\r
- End Function\r
- \r
- Public Function CellCount()\r
- CellCount=cells.count\r
- End Function\r
-\r
- Public Function GetRowAttr(ByVal name)\r
- GetRowAttr=attr(name)\r
- End Function\r
-\r
- Public Sub SetRowAttr(ByVal name, ByVal value)\r
- attr(name)=value\r
- End Sub\r
-\r
- Public Sub SetCellAttr(ByVal name, ByVal value)\r
- CurrentCell.SetAttr(name,value)\r
- End Sub\r
-end class\r
-\r
-\r
-Public Function AddHeadingRow(ResizeRowFlag as Boolean)\r
- LastHeadingRow=AddDataRow()\r
- if ResizeRowFlag then ResizeRowIdx=LastHeadingRow\r
- AddHeadingRow=LastHeadingRow\r
-End Function\r
-\r
-Public Function AddDataRow()\r
- rows.Add(new SimpleGridRow())\r
- LastRow=rows.count-1\r
- AddDataRow=LastRow\r
-End Function\r
-\r
-Public Function HeadingRowCount()\r
- if IsNothing(LastHeadingRow) then\r
- HeadingRowCount=0\r
- else\r
- HeadingRowCount=LastHeadingRow+1\r
- end if\r
-End Function\r
-\r
-Public Function DataRowCount()\r
- if IsNothing(LastRow) then\r
- DataRowCount=0\r
- else\r
- DataRowCount=LastRow+1-HeadingRowCount()\r
- end if\r
-End Function\r
-\r
-' returns # of cells in the current row\r
-Public Function CellCount()\r
- CellCount=rows(LastRow).CellCount\r
-End Function\r
-\r
-Public Sub AddCell(ByVal content as String)\r
- rows(LastRow).AddCell(content)\r
-End Sub\r
-\r
-Public Sub AddCellToRow(ByVal RowIdx as Integer, ByVal content as String)\r
- LastRow=RowIdx\r
- AddCell(content)\r
-End Sub\r
-\r
-Public Sub SetRowAttr(ByVal name as String, ByVal value as String)\r
- rows(LastRow).SetRowAttr(name,value)\r
-End Sub\r
-\r
-Public Sub SetCellAttr(ByVal name as String, ByVal value as String)\r
- rows(LastRow).SetCellAttr(name,value)\r
-End Sub\r
-\r
-Private Sub RenderColumns(writer as HTMLTextWriter, c1 as Integer, c2 as Integer)\r
- dim r as Integer, c as Integer\r
- for c=c1 to c2\r
- writer.Write("<td><div class='ricoLG_col'>")\r
- for r=LastHeadingRow+1 to rows.count-1\r
- writer.Write(rows(r).cells(c).DataCell(rows(r).GetRowAttr("class")))\r
- next\r
- writer.WriteLine("</div></td>")\r
- next\r
-End Sub\r
-\r
-<TemplateContainer(GetType(GridContainer))> _\r
-Public Property GridColumns() As ITemplate\r
- Get\r
- Return _gridHeading\r
- End Get\r
- Set\r
- _gridHeading = value\r
- End Set\r
-End Property\r
-\r
-' returns true if there is a valid heading\r
-Private Sub CheckHeading()\r
- if not IsNothing(ResizeRowIdx) then exit sub\r
- rows.Insert(0, new SimpleGridRow())\r
- LastHeadingRow=0\r
- ResizeRowIdx=0\r
- Dim c as Integer\r
- for c=0 to columns.count-1\r
- rows(0).AddCell( CType(columns(c),GridColumn).heading )\r
- next\r
-End Sub\r
-\r
-Private function FmtBool(b)\r
- if b then FmtBool="true" else FmtBool="false"\r
-end function\r
-\r
-Protected Overrides Sub Render(writer as HTMLTextWriter)\r
- dim colcnt as Integer, r as Integer, c as Integer\r
- CheckHeading\r
- colcnt=rows(ResizeRowIdx).CellCount\r
- \r
- writer.WriteLine("<script type='text/javascript'>")\r
- if not UsingMinRico then writer.WriteLine("Rico.loadModule('SimpleGrid');")\r
- writer.WriteLine("var " & Me.UniqueId & " = {};")\r
- writer.WriteLine("Rico.onLoad( function() {")\r
- writer.WriteLine(" " & optionsVar & " = {")\r
- if FilterLocation >= -1 then writer.WriteLine(" FilterLocation: " & FilterLocation & ",")\r
- if not IsNothing(FilterAllToken) then writer.WriteLine(" FilterAllToken: '" & FilterAllToken & "',")\r
- if FilterBoxMaxLen >= 0 then writer.WriteLine(" FilterBoxMaxLen: " & FilterBoxMaxLen & ",")\r
- if FilterAnchorLeft then writer.WriteLine(" FilterAnchorLeft: " & lcase(FilterAnchorLeft) & ",")\r
- if defaultWidth > 0 then writer.WriteLine(" defaultWidth: " & defaultWidth & ",")\r
- if not IsNothing(menuEvent) then writer.WriteLine(" menuEvent: '" & menuEvent & "',")\r
- writer.WriteLine(" allowColResize: " & FmtBool(allowColResize) & "," & vbCrLf)\r
- writer.WriteLine(" columnSpecs: [")\r
- for c=0 to columns.count-1\r
- if c > 0 then writer.WriteLine(",")\r
- writer.Write(CType(columns(c),GridColumn).script)\r
- next\r
- writer.WriteLine(vbCrLf & " ]")\r
- writer.WriteLine(" };")\r
- writer.WriteLine(" " & gridVar & "=new Rico.SimpleGrid('" & Me.UniqueId & "', " & optionsVar & ");")\r
- writer.WriteLine(" if(typeof " & Me.UniqueId & "_InitComplete=='function') " & Me.UniqueId & "_InitComplete();" & vbCrLf)\r
- writer.WriteLine("});")\r
- writer.WriteLine("</script>")\r
- \r
- writer.Write("<div id='" & Me.UniqueId & "_outerDiv'><table id='" & Me.UniqueId & "' border='0' cellspacing='0' cellpadding='0'><tr valign='top'><td rowspan='2'>")\r
-\r
- '-------------------\r
- ' frozen columns\r
- '-------------------\r
-\r
- ' upper left\r
- writer.WriteLine("<table id='" & Me.UniqueId & "_tab0h' class='ricoLG_table ricoLG_top ricoLG_left' cellspacing='0' cellpadding='0'><thead>")\r
- for r=0 to LastHeadingRow\r
- writer.Write("<tr class='" & rows(r).HeadingClass() & "'")\r
- if r=ResizeRowIdx then writer.Write(" id='" & Me.UniqueId & "_tab0h_main'")\r
- writer.WriteLine(">")\r
- if FrozenCols > 0 then writer.Write(rows(r).HeadingRow(0,FrozenCols-1))\r
- writer.Write("</tr>")\r
- next\r
- writer.WriteLine("</thead></table>")\r
-\r
- writer.WriteLine("<div id='" & Me.UniqueId & "_frozenTabsDiv'>")\r
-\r
- ' lower left\r
- writer.Write("<table id='" & Me.UniqueId & "_tab0' class='ricoLG_table ricoLG_bottom ricoLG_left' cellspacing='0' cellpadding='0'>")\r
- writer.WriteLine("<tr>")\r
- if FrozenCols > 0 then RenderColumns(writer,0,FrozenCols-1)\r
- writer.Write("</tr>")\r
- writer.WriteLine("</table>")\r
-\r
- writer.WriteLine("</div></td>")\r
-\r
-\r
- '-------------------\r
- ' scrolling columns\r
- '-------------------\r
-\r
- ' upper right\r
- writer.Write("<td><div id='" & Me.UniqueId & "_innerDiv'>")\r
- 'writer.Write("<div id='" & Me.UniqueId & "_scrollTabsDiv'>")\r
- writer.WriteLine("<table id='" & Me.UniqueId & "_tab1h' class='ricoLG_table ricoLG_top ricoLG_right' cellspacing='0' cellpadding='0'><thead>")\r
- for r=0 to LastHeadingRow\r
- writer.Write("<tr class='" & rows(r).HeadingClass & "'")\r
- if r=ResizeRowIdx then writer.Write(" id='" & Me.UniqueId & "_tab1h_main'")\r
- writer.Write(">")\r
- writer.Write(rows(r).HeadingRow(FrozenCols,-1))\r
- writer.Write("</tr>")\r
- next\r
- writer.Write("</thead></table>")\r
- 'writer.Write("</div>")\r
- writer.WriteLine("</div></td></tr>")\r
-\r
- ' lower right\r
- writer.Write("<tr valign='top'><td><div id='" & Me.UniqueId & "_scrollDiv'>")\r
- writer.Write("<table id='" & Me.UniqueId & "_tab1' class='ricoLG_table ricoLG_bottom ricoLG_right' cellspacing='0' cellpadding='0'>")\r
- writer.WriteLine("<tr>")\r
- RenderColumns(writer,FrozenCols,colcnt-1)\r
- writer.Write("</tr>")\r
- writer.Write("</table>")\r
- writer.Write("</div></td></tr>")\r
-\r
- writer.WriteLine("</table></div>")\r
-End Sub\r
-\r
-' Response.Buffer must be true\r
-Public Sub RenderExcel(fileName)\r
- Dim r as Integer, c as Integer\r
- Dim sw As New System.IO.StringWriter\r
-\r
- HttpContext.Current.Response.Clear()\r
- if fileName<>"" then HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & fileName)\r
- HttpContext.Current.Response.ContentType = "application/ms-excel"\r
-\r
- CheckHeading\r
- sw.WriteLine("<table>")\r
- for r=0 to rows.count-1\r
- sw.WriteLine("<tr>")\r
- for c=0 to rows(r).CellCount()-1\r
- sw.Write(rows(r).cells(c).HtmlCell())\r
- next\r
- sw.WriteLine("</tr>")\r
- next\r
- sw.WriteLine("</table></div>")\r
- HttpContext.Current.Response.Write(sw.ToString)\r
- HttpContext.Current.Response.End()\r
-End Sub\r
-\r
-' Response.Buffer must be true\r
-Public Sub RenderDelimited(fileName,delim,SubstituteChar)\r
- Dim r as Integer, c as Integer\r
- Dim sw As New System.IO.StringWriter\r
-\r
- HttpContext.Current.Response.Clear()\r
- if fileName<>"" then HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & fileName)\r
- HttpContext.Current.Response.ContentType = "text/csv"\r
-\r
- CheckHeading\r
- for r=0 to rows.count-1\r
- for c=0 to rows(r).CellCount()-1\r
- if c > 0 then sw.Write(delim)\r
- sw.Write(replace(rows(r).cells(c).content,delim,SubstituteChar))\r
- next\r
- sw.WriteLine("")\r
- next\r
- HttpContext.Current.Response.Write(sw.ToString)\r
- HttpContext.Current.Response.End()\r
-End Sub\r
-\r
-Public Class GridContainer\r
- Inherits Control\r
- Implements INamingContainer\r
-End Class\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="vb" debug="true" ClassName="ricoResponse" %>\r
-<%@ Register TagPrefix="Rico" TagName="sqlParse" Src="sqlParse.ascx" %>\r
-<%@ Import Namespace="System.Data" %>\r
-<script runat="server">\r
-\r
-Public dbConnection as object\r
-Public dbDialect as String\r
-Protected dbVersion as String\r
-Protected dbClassName as String\r
-Public RequestId as string \r
-Public offset as integer = 0\r
-Public numrows as integer = 1999\r
-Public AllRowsMax as integer = 1999 ' max # of rows to send if numrows=-1\r
-Public gettotal as Boolean = true\r
-Public distinctCol as integer = -1\r
-Public editCol as integer = -1\r
-Public Headings(-1) as string\r
-Public HiddenCols(-1) as string\r
-Public filters as ArrayList\r
-Public orderByRef = false ' use column numbers in order by clause? (true/false)\r
-Public Wildcard as String="%"\r
-Public oParse as object ' parsed sql select statement to execute\r
-Public sqlText as String ' sql query to execute (either oParse or sqlText must be set prior to rendering)\r
-Public ErrorMsg as String ' may contain the text of an error message that occurred outside this control prior to rendering\r
-Public HeaderRows as new ArrayList() ' data that will be inserted before the query results\r
-Public FooterRows as new ArrayList() ' data that will be appended after the query results\r
-Public fmt as string\r
-Public SendHdg as Boolean = false\r
-Public RenderFlag as Boolean = true\r
-Protected command as object\r
-\r
-' DEBUGGING CONTROL\r
-Public sendDebugMsgs as Boolean = false ' send details of sql parsing/execution in ajax response? (true/false)\r
-Public LogSqlOnError as Boolean = false ' include sql statement in results if an error occurs (true/false)\r
-Protected DebugMsgs as new ArrayList()\r
-\r
-\r
-Protected Sub Page_Init(Sender As object, e As EventArgs)\r
- RequestId = trim(Request.QueryString("id"))\r
- fmt = trim(Request.QueryString("_fmt"))\r
- dim sRequestOffset as string = trim(Request.QueryString("offset"))\r
- dim sRequestSize as string = trim(Request.QueryString("page_size"))\r
- dim sRequestTotal as string = lcase(Request.QueryString("get_total"))\r
- dim sDistinct as string = trim(Request.QueryString("distinct"))\r
- dim sEdit as string = trim(Request.QueryString("edit"))\r
- dim sHidden as string = trim(Request.QueryString("hidden"))\r
- if not IsNumeric(sRequestOffset) then sRequestOffset="0"\r
-\r
- if sRequestOffset<>"" then sRequestOffset=Regex.Replace( sRequestOffset, "[^0-9-]", "" )\r
- if sRequestOffset<>"" then offset=CLng(sRequestOffset)\r
- if sRequestSize<>"" then sRequestSize=Regex.Replace( sRequestSize, "[^0-9-]", "" )\r
- if sRequestSize<>"" then numrows=CLng(sRequestSize)\r
- if sDistinct<>"" then distinctCol=CLng(sDistinct)\r
- if sEdit<>"" then editCol=CLng(sEdit)\r
- if sHidden<>"" then HiddenCols=split(sHidden,",")\r
- gettotal=(sRequestTotal="true")\r
-End Sub\r
-\r
-\r
-Protected Overrides Sub Render(writer as HTMLTextWriter)\r
- Me.RunQuery(writer)\r
-End Sub\r
-\r
-\r
-'Protected Overrides Sub Render(writer as HTMLTextWriter)\r
-Public Sub RunQuery(writer as HTMLTextWriter)\r
- Dim SqlRows as integer=0\r
- dim closetags as string, RowsStart as string, RowsEnd as string\r
-\r
- if not RenderFlag then exit sub\r
- Response.clear\r
- if fmt<>"xl" then\r
- Response.CacheControl = "no-cache"\r
- Response.AddHeader("Pragma", "no-cache")\r
- Response.Expires = -1\r
- end if\r
- select case fmt\r
- case "html":\r
- Response.ContentType="text/html"\r
- writer.WriteLine("<html><head></head><body>")\r
- closetags="</body></html>"\r
- RowsStart=vbLf & "<table border='1'>"\r
- RowsEnd=vbLf & "</table>"\r
- gettotal=false\r
- sendDebugMsgs=false\r
- SendHdg=true\r
- case "xl":\r
- Response.ContentType="application/vnd.ms-excel"\r
- Response.AddHeader("Content-Disposition", "attachment; filename=" & RequestId & ".xml")\r
- writer.WriteLine("<?xml version='1.0' encoding='iso-8859-1'?>")\r
- writer.WriteLine("<?mso-application progid='Excel.Sheet'?>")\r
- writer.WriteLine("<s:Workbook xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:s='urn:schemas-microsoft-com:office:spreadsheet'>")\r
- writer.WriteLine(" <s:Styles>")\r
- writer.WriteLine(" <s:Style s:ID='sDate'><s:NumberFormat s:Format='Short Date' /></s:Style>")\r
- writer.WriteLine(" </s:Styles>")\r
- writer.WriteLine(" <s:Worksheet s:Name='" & RequestId & "'>")\r
- closetags="</s:Worksheet></s:Workbook>"\r
- RowsStart=vbLf & "<s:Table>"\r
- RowsEnd=vbLf & "</s:Table>"\r
-\r
- gettotal=false\r
- sendDebugMsgs=false\r
- AllRowsMax=65534 ' allow 1 row for heading\r
- case "json":\r
- Response.ContentType="application/json"\r
- writer.Write("{" & vbLf & """id"":""" & RequestId & """")\r
- RowsStart="," & vbLf & """update_ui"":true," & vbLf & """offset"":" & offset & "," & vbLf & """rows"":["\r
- RowsEnd=vbLf & "]"\r
- closetags="}"\r
- case else:\r
- ' default to xml\r
- fmt="xml"\r
- Response.ContentType="text/xml"\r
- writer.WriteLine("<?xml version='1.0' encoding='iso-8859-1'?>")\r
- writer.WriteLine("<ajax-response><response type='object' id='" & RequestId & "'>")\r
- closetags="</response></ajax-response>"\r
- RowsStart=vbLf & "<rows update_ui='true' offset='" & offset & "'>"\r
- RowsEnd=vbLf & "</rows>"\r
- end select\r
-\r
- if RequestId="" then\r
- ErrorMsg="No ID provided!"\r
- elseif IsNothing(dbConnection) and (not IsNothing(oParse) or not IsNothing(sqlText)) then\r
- ErrorMsg="No database connection"\r
- end if\r
-\r
- if not IsNothing(ErrorMsg) then\r
- ErrorResponse(writer, ErrorMsg)\r
- else\r
-\r
- writer.WriteLine(RowsStart)\r
- try\r
- writer.WriteLine(join(HeaderRows.ToArray(),vbLf))\r
- if not IsNothing(dbConnection) then\r
- SqlRows=RenderQueryRows(writer)\r
- end if\r
- writer.WriteLine(join(FooterRows.ToArray(),vbLf))\r
- writer.WriteLine(RowsEnd)\r
- if SqlRows >= 0 and (fmt="xml" or fmt="json") then\r
- AppendResponse(writer, "rowcount", CStr(SqlRows+HeaderRows.count+FooterRows.count))\r
- end if\r
- if sendDebugMsgs then\r
- AppendArrayResponse(writer, "debug", DebugMsgs.ToArray())\r
- end if\r
- Catch ex As Exception\r
- writer.WriteLine(RowsEnd)\r
- dim msg as string = ex.Message\r
- if LogSqlOnError AndAlso not IsNothing(sqlText) then msg &= " - " & sqlText\r
- ErrorResponse(writer, msg)\r
- end try\r
- end if\r
- writer.WriteLine(closetags)\r
-End Sub\r
-\r
-\r
-Public Sub ErrorResponse(writer as HTMLTextWriter, msg as string)\r
- AppendResponse(writer,"error",msg)\r
-end sub\r
-\r
-\r
-Public Sub AppendResponse(writer as HTMLTextWriter, tag as string, content as string)\r
- select case fmt\r
- case "html", "xl":\r
- writer.write(vbLf & "<p>" & tag & "<br>" & server.htmlencode(content) & "</p>")\r
- case "json":\r
- writer.write("," & vbLf & """" & tag & """:""" & escapeJSON(content) & """")\r
- case "xml":\r
- writer.write(vbLf & "<" & tag & ">" & server.htmlencode(content) & "</" & tag & ">")\r
- end select\r
-end sub\r
-\r
-\r
-Public Sub AppendArrayResponse(writer as HTMLTextWriter, tag as string, arContent as object())\r
- dim item as string, i as integer\r
- select case fmt\r
- case "html", "xl":\r
- writer.write(vbLf & "<p>" & tag)\r
- for each item in arContent\r
- writer.write("<br>" & server.htmlencode(item))\r
- next\r
- writer.write("</p>")\r
- case "json":\r
- writer.write("," & vbLf & """" & tag & """:[")\r
- for i=0 to arContent.Length-1\r
- arContent(i)="""" & escapeJSON(arContent(i)) & """"\r
- next\r
- writer.write(join(arContent,",") & "]")\r
- case "xml":\r
- for each item in arContent\r
- writer.write(vbLf & "<" & tag & ">" & server.htmlencode(item) & "</" & tag & ">")\r
- next\r
- end select\r
-end sub\r
-\r
-\r
-' returns the total number of rows produced by the query (or -1 if unknown)\r
-Protected Function RenderQueryRows(writer as HTMLTextWriter) As Integer\r
- dim rowcnt as integer, fldNum as integer, dbDate as DateTime, strFieldItem as String, fldType as String, fldAttr as String\r
- dim firstCol as Integer=0, limitQuery as Boolean=false, eof as Boolean=false, n as String\r
- dim rdr as object\r
- dim totcnt as Integer=0\r
-\r
- RenderQueryRows=-1\r
- dbVersion=dbConnection.ServerVersion\r
- dbClassName=TypeName(dbConnection)\r
- command = dbConnection.CreateCommand()\r
- if not IsNothing(oParse) then\r
- if distinctCol >= 0 then\r
- ApplyQStringParms()\r
- sqlText=oParse.UnparseDistinctColumn(distinctCol)\r
- elseif editCol >= 0 then\r
- sqlText=oParse.SelectList(editCol).LookupQuery\r
- oParse=new sqlParse()\r
- oParse.ParseSelect(sqlText)\r
- ApplyQStringParms()\r
- sqlText=oParse.UnparseSelect()\r
- elseif numrows < 0 or offset=0 then\r
- ApplyQStringParms()\r
- sqlText=oParse.UnparseSelectSkip(HiddenCols)\r
- else\r
- ApplyQStringParms()\r
- select case dbDialect\r
- case "TSQL":\r
- if left(dbVersion,2) >= "09" then\r
- sqlText=oParse.UnparseWithRowNumber(offset,numrows+1,true,HiddenCols)\r
- firstCol=1\r
- limitQuery=true\r
- else\r
- sqlText=oParse.UnparseSelectSkip(HiddenCols)\r
- end if\r
- case "Oracle": \r
- sqlText=oParse.UnparseWithRowNumber(offset,numrows+1,false,HiddenCols)\r
- firstCol=1\r
- limitQuery=true\r
- case "MySQL":\r
- sqlText=oParse.UnparseSelectSkip(HiddenCols) & " LIMIT " & offset & "," & CStr(numrows+1)\r
- limitQuery=true\r
- case else:\r
- sqlText=oParse.UnparseSelectSkip(HiddenCols)\r
- end select \r
- end if\r
- end if\r
- if IsNothing(sqlText) then Exit Function\r
- DebugMsgs.add(sqlText)\r
- DebugMsgs.add(dbClassName)\r
- DebugMsgs.add("DB version=" & dbVersion)\r
- command.CommandText = sqlText\r
- rdr = command.ExecuteReader()\r
-\r
- if limitQuery then\r
- totcnt=offset\r
- else\r
- while (totcnt < offset) and (not eof)\r
- if rdr.Read() then\r
- totcnt += 1\r
- else\r
- eof=true\r
- end if\r
- end while\r
- end if\r
-\r
- rowcnt=0\r
- if numrows < 0 then numrows=AllRowsMax\r
- select case fmt\r
-\r
- case "json":\r
- if SendHdg then\r
- writer.Write(vbLf & "[")\r
- for fldNum=firstCol to rdr.FieldCount -1\r
- if IsNothing(oParse) then\r
- n=Nothing\r
- else\r
- n=oParse.Headings(fldNum-firstCol)\r
- end if\r
- if IsNothing(n) then n=rdr.GetName(fldNum)\r
- writer.Write("""" & escapeJSON(n) & """")\r
- next\r
- writer.Write("]")\r
- end if\r
- while (rowcnt < numrows) and (not eof)\r
- if rdr.Read() then\r
- if rowcnt > 0 or SendHdg then writer.Write(",")\r
- writer.Write(vbLf & "[")\r
- for fldNum = firstCol to rdr.FieldCount -1\r
- strFieldItem = ""\r
- if not rdr.IsDBNull(fldNum) then\r
- select case rdr.GetFieldType(fldNum).Name\r
- case "DateTime":\r
- dbDate=rdr.GetDateTime(fldNum)\r
- strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
- case else:\r
- strFieldItem = escapeJSON(rdr.GetValue(fldNum))\r
- end select\r
- end if\r
- if fldNum > firstCol then writer.Write(",")\r
- writer.Write("""" & strFieldItem & """")\r
- next\r
- writer.Write("]")\r
- rowcnt += 1\r
- else\r
- eof=true\r
- end if\r
- end while\r
-\r
- case "xl":\r
- writer.Write(vbLf & "<s:Row>")\r
- for fldNum=firstCol to rdr.FieldCount-1\r
- if IsNothing(oParse) then\r
- n=Nothing\r
- else\r
- n=oParse.Headings(fldNum-firstCol)\r
- end if\r
- if IsNothing(n) then n=rdr.GetName(fldNum)\r
- writer.Write("<s:Cell><s:Data s:Type='String'>" & server.HTMLEncode(n) & "</s:Data></s:Cell>")\r
- next\r
- writer.Write("</s:Row>")\r
- while (rowcnt < numrows) and (not eof)\r
- if rdr.Read() then\r
- rowcnt += 1\r
- writer.Write("<s:Row>")\r
- for fldNum = firstCol to rdr.FieldCount -1\r
- strFieldItem = ""\r
- fldAttr = ""\r
- fldType = "String"\r
- if not rdr.IsDBNull(fldNum) then\r
- select case UCase(Left(rdr.GetFieldType(fldNum).Name, 3))\r
- case "DAT":\r
- dbDate=rdr.GetDateTime(fldNum)\r
- strFieldItem = dbDate.ToString("s") ' convert to ISO-8601 format\r
- fldType = "DateTime"\r
- fldAttr = " s:StyleID='sDate'"\r
- case "INT", "DOU", "DEC":\r
- strFieldItem = CStr(rdr.GetValue(fldNum))\r
- fldType = "Number"\r
- case else:\r
- strFieldItem = server.HTMLEncode(rdr.GetValue(fldNum))\r
- end select\r
- end if\r
- writer.Write("<s:Cell" & fldAttr & "><s:Data s:Type='" & fldType & "'>" & strFieldItem & "</s:Data></s:Cell>")\r
- next\r
- writer.Write("</s:Row>")\r
- else\r
- eof=true\r
- end if\r
- end while\r
-\r
- case else:\r
- if SendHdg then\r
- writer.Write(vbLf & "<tr>")\r
- for fldNum=firstCol to rdr.FieldCount -1\r
- if IsNothing(oParse) then\r
- n=Nothing\r
- else\r
- n=oParse.Headings(fldNum-firstCol)\r
- end if\r
- if IsNothing(n) then n=rdr.GetName(fldNum)\r
- writer.Write("<td>" & server.HTMLEncode(n) & "</td>")\r
- next\r
- writer.Write("</tr>")\r
- end if\r
- while (rowcnt < numrows) and (not eof)\r
- if rdr.Read() then\r
- rowcnt += 1\r
- writer.Write("<tr>")\r
- for fldNum = firstCol to rdr.FieldCount -1\r
- strFieldItem = ""\r
- if not rdr.IsDBNull(fldNum) then\r
- select case rdr.GetFieldType(fldNum).Name\r
- case "DateTime":\r
- dbDate=rdr.GetDateTime(fldNum)\r
- strFieldItem = replace(dbDate.ToString("s"),"T"," ") ' convert to ISO-8601 format\r
- case else:\r
- strFieldItem = server.HTMLEncode(rdr.GetValue(fldNum))\r
- end select\r
- end if\r
- writer.Write("<td>" & strFieldItem & "</td>")\r
- next\r
- writer.Write("</tr>")\r
- else\r
- eof=true\r
- end if\r
- end while\r
- end select\r
- totcnt += rowcnt\r
-\r
- if not eof and gettotal then\r
- if limitQuery then\r
- rdr.Close()\r
- dim countSql,cnt\r
- countSql="SELECT " & oParse.UnparseColumnList() & " FROM " & oParse.FromClause\r
- if not IsNothing(oParse.WhereClause) then countSql &= " WHERE " & oParse.WhereClause\r
- if oParse.GroupBy.count > 0 then countSql &= " GROUP BY " & join(oParse.GroupBy.ToArray(),",")\r
- if not IsNothing(oParse.HavingClause) then countSql &= " HAVING " & oParse.HavingClause\r
- countSql="SELECT COUNT(*) FROM (" & countSql & ")"\r
- if dbDialect<>"Oracle" then countSql &= " AS rico_Main"\r
- DebugMsgs.add(countSql)\r
- command.CommandText = countSql\r
- totcnt = command.ExecuteScalar()\r
- eof=true\r
- else\r
- while rdr.Read()\r
- totcnt += 1\r
- end while\r
- eof=true\r
- end if\r
- end if\r
- if eof then RenderQueryRows=totcnt\r
- rdr.Close()\r
-End Function\r
-\r
-\r
-' returns the parameter symbol to insert into the sql string\r
-Private Function PushParam(ByVal newvalue) as String\r
- dim ParamName as String\r
- newvalue=cstr(newvalue)\r
- if newvalue="" then newvalue=" " ' empty string gets converted to TEXT data type instead of VARCHAR\r
- select case dbClassName\r
- case "SqlConnection":\r
- ParamName="@P" & CStr(command.parameters.count)\r
- PushParam=ParamName\r
- case else:\r
- ParamName=""\r
- PushParam="?"\r
- end select\r
- command.parameters.add(ParamName,newvalue)\r
- DebugMsgs.add("Param " & ParamName & " value=" & newvalue)\r
-End Function\r
-\r
-\r
-' assumes oParse is already initialized\r
-Private sub ApplyQStringParms()\r
- dim i, a, flen\r
- dim j as Integer, fop as String, ParamSymbol as String\r
- dim newfilter as string, qs as string, value as string\r
-\r
- for each qs in Request.QueryString\r
- select case left(qs,1)\r
- \r
- ' user-invoked condition\r
- case "w","h":\r
- i=mid(qs,2)\r
- if IsNumeric(i) then\r
- i=CInt(i)\r
- if i<0 or i>=filters.Count then exit for\r
- value=Request.QueryString(qs)\r
- newfilter=filters(i)\r
- j=InStr(1,newfilter," in (?)",1)\r
- if j>0 then\r
- a=split(value,",")\r
- for i=0 to ubound(a)\r
- ParamSymbol=PushParam(a(i))\r
- a(i)=ParamSymbol\r
- next\r
- newfilter=left(newfilter,j+4) & join(a,",") & mid(newfilter,j+6)\r
- elseif InStr(newfilter,"?")>0 then\r
- ParamSymbol=PushParam(value)\r
- if ParamSymbol<>"?" then newfilter=replace(newfilter,"?",ParamSymbol)\r
- end if\r
- if left(qs,1)="h" then\r
- oParse.AddHavingCondition(newfilter)\r
- else\r
- oParse.AddWhereCondition(newfilter)\r
- end if\r
- end if\r
- \r
- ' sort\r
- case "s":\r
- i=mid(qs,2)\r
- if not IsNumeric(i) then exit for\r
- i=CInt(i)\r
- if i<0 or i>=oParse.SelectList.count then exit for\r
- value=ucase(left(Request.QueryString(qs),4))\r
- if value<>"ASC" and value<>"DESC" then value="ASC"\r
- if orderByRef then\r
- oParse.AddSort(CStr(i+1) & " " & value)\r
- else\r
- oParse.AddSort(oParse.SelectList(i).sql & " " & value)\r
- end if\r
- \r
- ' user-supplied filter\r
- case "f":\r
- a=split(qs,"[")\r
- if ubound(a)=2 then\r
- if a(2)="op]" then\r
- i=left(a(1),len(a(1))-1)\r
- if not IsNumeric(i) then exit for\r
- if len(i)>3 then exit for\r
- i=CInt(i)\r
- if i<0 or i>oParse.SelectList.count then exit for\r
- fop=Request.QueryString(qs)\r
- newfilter=oParse.SelectList(i).sql\r
- select case fop\r
- case "EQ":\r
- newfilter = "(" & AddCoalesce(newfilter) & " IN " & GetMultiParmFilter(qs) & ")"\r
- case "LE":\r
- newfilter &= "<=" & PushParam(Request.QueryString(replace(qs,"[op]","[0]")))\r
- case "GE":\r
- newfilter &= ">=" & PushParam(Request.QueryString(replace(qs,"[op]","[0]")))\r
- case "NULL": newfilter &= " is null"\r
- case "NOTNULL": newfilter &= " is not null"\r
- case "LIKE":\r
- newfilter &= " LIKE " & PushParam(replace(Request.QueryString(replace(qs,"[op]","[0]")),"*",Wildcard))\r
- case "NE"\r
- newfilter = "(" & AddCoalesce(newfilter) & " NOT IN " & GetMultiParmFilter(qs) & ")"\r
- end select\r
- dim sql=oParse.SelectList(i).sql\r
- if (InStr(sql,"min(")>0 or _\r
- InStr(sql,"max(")>0 or _\r
- InStr(sql,"sum(")>0 or _\r
- InStr(sql,"count(")>0) and _\r
- InStr(sql,"(select ")<1 then\r
- oParse.AddHavingCondition(newfilter)\r
- else\r
- oParse.AddWhereCondition(newfilter)\r
- end if\r
- end if\r
- end if\r
- end select\r
- next\r
-end sub\r
-\r
-\r
-Private function AddCoalesce(newfilter as String) as String\r
- if dbDialect="Access" then\r
- newfilter="iif(IsNull(" & newfilter & "),''," & newfilter & ")"\r
- else\r
- newfilter="coalesce(" & newfilter & ",'')"\r
- end if\r
- AddCoalesce=newfilter\r
-end function\r
-\r
-\r
-Private function GetMultiParmFilter(qs as String) as String\r
- dim flenStr as String = Request.QueryString(replace(qs,"[op]","[len]"))\r
- if not IsNumeric(flenStr) then exit function\r
- dim flen as Integer = CInt(flenStr)\r
- dim j as Integer, param as String, filter as String = ""\r
- for j=0 to flen-1\r
- if j>0 then filter &= ","\r
- param=Request.QueryString(replace(qs,"[op]","[" & j & "]"))\r
- filter &= PushParam(param)\r
- next\r
- GetMultiParmFilter = "(" & filter & ")"\r
-end function\r
-\r
-\r
-Public function XmlStringCell(value as object) as String\r
- dim result\r
- if IsDBNull(value) then result="" else result=server.HTMLEncode(value)\r
- XmlStringCell="<td>" & result & "</td>"\r
-end function\r
-\r
-\r
-' for the root node, parentID should "" (empty string)\r
-' containerORleaf: L/zero (leaf), C/non-zero (container)\r
-' selectable: 0->not selectable, 1->selectable\r
-Public function WriteTreeRow(parentID,ID,description,containerORleaf,selectable)\r
- HeaderRows.Add(TreeRow(parentID,ID,description,containerORleaf,selectable))\r
-end function\r
-\r
-Public function TreeRow(parentID,ID,description,containerORleaf,selectable)\r
- TreeRow="<tr>" & XmlStringCell(parentID) & XmlStringCell(ID) & XmlStringCell(description) & XmlStringCell(containerORleaf) & XmlStringCell(selectable) & "</tr>"\r
-end function\r
-\r
-'******************************************************************************************\r
-'' @SDESCRIPTION: takes a given string and makes it JSON valid (http://json.org/)\r
-'' @AUTHOR: Michael Rebec\r
-'' @DESCRIPTION: all characters which needs to be escaped are beeing replaced by their\r
-'' unicode representation according to the\r
-'' RFC4627#2.5 - http://www.ietf.org/rfc/rfc4627.txt?number=4627\r
-'' @PARAM: val [string]: value which should be escaped\r
-'' @RETURN: [string] JSON valid string\r
-'******************************************************************************************\r
-public function escapeJSON(val)\r
- const cDoubleQuote = &h22\r
- const cRevSolidus = &h5C\r
- const cSolidus = &h2F\r
- dim i as integer, currentDigit as string\r
-\r
- for i = 1 to (len(val))\r
- currentDigit = mid(val, i, 1)\r
- if asc(currentDigit)> &h00 and asc(currentDigit) <&h1F then\r
- currentDigit = escapeJSONSquence(currentDigit)\r
- elseif asc(currentDigit)>= &hC280 and asc(currentDigit) <= &hC2BF then\r
- currentDigit = "\u00" + right(padLeft(hex(asc(currentDigit) - &hC200), 2, 0), 2)\r
- elseif asc(currentDigit)>= &hC380 and asc(currentDigit) <= &hC3BF then\r
- currentDigit = "\u00" + right(padLeft(hex(asc(currentDigit) - &hC2C0), 2, 0), 2)\r
- else\r
- select case asc(currentDigit)\r
- case cDoubleQuote: currentDigit = escapeJSONSquence(currentDigit)\r
- case cRevSolidus: currentDigit = escapeJSONSquence(currentDigit)\r
- case cSolidus: currentDigit = escapeJSONSquence(currentDigit)\r
- end select\r
- end if\r
- escapeJSON = escapeJSON & currentDigit\r
- next\r
-end function\r
- \r
-function escapeJSONSquence(digit)\r
- escapeJSONSquence = "\u00" + right(padLeft(hex(asc(digit)), 2, 0), 2)\r
-end function \r
- \r
-function padLeft(value, totalLength, paddingChar)\r
- padLeft = right(clone(paddingChar, totalLength) & value, totalLength)\r
-end function\r
- \r
-public function clone(byVal str, n)\r
- dim i as integer\r
- for i = 1 to n : clone = clone & str : next\r
-end function\r
-\r
-</script>\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="sqlCompatibilty.ascx.vb" \r
- Inherits="sqlCompatibilty" %>\r
+++ /dev/null
-Partial Class sqlCompatibilty\r
-Inherits System.Web.UI.UserControl\r
-\r
-' ---------------------------------------------------------------------------\r
-' Functions to allow cross-db compatibility within Rico\r
-'\r
-' SQL dialect possible values: Access, Oracle, TSQL (MS SQL Server)\r
-' ---------------------------------------------------------------------------\r
-\r
-Protected Dialect as String\r
-\r
-Sub New(dbDialect as String)\r
- Dialect=dbDialect\r
-End Sub\r
-\r
-Sub New()\r
-End Sub\r
-\r
-Sub SetDialectFromProvider(Provider as String)\r
- Provider=ucase(Provider)\r
- if Provider="SQLOLEDB" then\r
- Dialect="TSQL"\r
- elseif InStr(Provider,"ORACLE")>0 then\r
- Dialect="Oracle"\r
- elseif InStr(Provider,"JET")>0 then\r
- Dialect="Access"\r
- else\r
- Throw New Exception("Unknown ADO provider")\r
- end if\r
-End Sub\r
-\r
-Public function CurrentTime() as String\r
- select case Dialect\r
- case "TSQL","DB2": CurrentTime="CURRENT_TIMESTAMP"\r
- case "Access": CurrentTime="Now()"\r
- case else: CurrentTime="LOCALTIMESTAMP"\r
- end select\r
-end function\r
-\r
-Public function Convert2Char(s as String) as String\r
- select case Dialect\r
- case "TSQL" : Convert2Char="cast(" & s & " as varchar)"\r
- case "Access": Convert2Char="CStr(" & s & ")"\r
- case "DB2" : Convert2Char="CHAR(" & s & ")"\r
- case "Oracle": Convert2Char="cast(" & s & " as varchar2(20))"\r
- case else: Convert2Char=s ' implicit conversion (MySQL)\r
- end select\r
-end function\r
-\r
-Public function Wildcard() as String\r
- Wildcard="%"\r
-end function\r
-\r
-Public function SqlDay(s as String) as String\r
- select case Dialect\r
- case "Oracle": SqlDay="to_char(" & s & ",'DD')"\r
- case "MySQL": SqlDay="dayofmonth(" & s & ")"\r
- case else: SqlDay="day(" & s & ")"\r
- end select\r
-end function\r
-\r
-Public function SqlMonth(s as String) as String\r
- select case Dialect\r
- case "Oracle": SqlMonth="to_char(" & s & ",'MM')"\r
- case else: SqlMonth="month(" & s & ")"\r
- end select\r
-end function\r
-\r
-Public function SqlYear(s as String) as String\r
- select case Dialect\r
- case "Oracle": SqlYear="to_char(" & s & ",'YYYY')"\r
- case else: SqlYear="year(" & s & ")"\r
- end select\r
-end function\r
-\r
-Public function addQuotes(s as String) as String\r
- select case Dialect\r
- case "Access":\r
- if IsDate(s) then\r
- addQuotes="#" & s & "#"\r
- else\r
- addQuotes="""" & replace(s,"""","""""") & """"\r
- end if\r
- case "MySQL": addQuotes="'" & replace(replace(s,"\","\\"),"'","\'") & "'"\r
- case else: addQuotes="'" & replace(s,"'","''") & "'"\r
- end select\r
-end function\r
-\r
-Public function Concat(arStrings() as String, addQuotes as Boolean) as String\r
- dim i as Integer\r
- if addQuotes then\r
- For Each i in arStrings\r
- 'arStrings(i)=addQuotes(arStrings(i))\r
- next\r
- end if\r
- select case Dialect\r
- case "TSQL": Concat=join(arStrings,"+")\r
- case "Access": Concat=join(arStrings," & ")\r
- case "MySQL": Concat="concat(" & join(arStrings,",") & ")"\r
- case else: Concat=join(arStrings," || ")\r
- end select\r
-end function\r
-\r
-End Class\r
+++ /dev/null
-<%@ Control Language="VB"\r
- CodeFile="sqlParse.ascx.vb" \r
- Inherits="sqlParse" %>\r
-<%@ Import Namespace="System.Data" %>\r
-<%@ Import Namespace="System.Data.OleDb" %>\r
+++ /dev/null
-Partial Class sqlParse\r
-Inherits System.Web.UI.UserControl\r
-Implements ICloneable\r
-\r
-Public Class sqlColumn\r
- Public sql As String, name As String\r
- Public LookupQuery As String ' query to populate column\r
-\r
- Public Sub New(Optional sqlParm As String = "", Optional nameParm As String = "")\r
- if sqlParm<>"" then sql=sqlParm\r
- if nameParm<>"" then name=nameParm\r
- End Sub\r
-\r
- Public function Unparse()\r
- dim s As String=sql\r
- if not IsNothing(name) then\r
- s &= " AS " & name\r
- end if\r
- Unparse=s\r
- end Function\r
-End Class\r
-\r
-\r
-\r
-'********************************************************************************************************\r
-' Parse SQL a statement\r
-'********************************************************************************************************\r
-\r
-Public IsDistinct As Boolean\r
-Public SelectList As New ArrayList()\r
-Public GroupBy As New ArrayList()\r
-Public OrderBy As New ArrayList()\r
-Public FromClause As String, WhereClause As String, HavingClause As String\r
-Public Headings As New ArrayList() ' set after an any unparse* call\r
-\r
-Public Function Clone As Object Implements ICloneable.Clone\r
- Dim NewObj as object = Me.MemberwiseClone, item as String\r
- ' shallow copy of OrderBy is insufficient because it may be modified by ricoResponse.ascx\r
- NewObj.OrderBy = New ArrayList()\r
- for each item in Me.OrderBy\r
- NewObj.OrderBy.Add(item)\r
- next\r
- Return NewObj\r
-End Function\r
-\r
-' -------------------------------------------------------------\r
-' Rebuilds a SQL select statement that was parsed by ParseSelect\r
-' -------------------------------------------------------------\r
-Private Function Unparse(arSkipCols) As String\r
- dim sqltext As String = "SELECT "\r
- if IsDistinct then sqltext &= "DISTINCT "\r
- sqltext &= UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause\r
- if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
- if GroupBy.count > 0 then sqltext &= " GROUP BY " & join(GroupBy.ToArray(),",")\r
- if not IsNothing(HavingClause) then sqltext &= " HAVING " & HavingClause\r
- if OrderBy.count > 0 then sqltext &= " ORDER BY " & join(OrderBy.ToArray(),",")\r
- Unparse=sqltext\r
-end Function\r
-\r
-\r
-Public Function UnparseSelect() As String\r
- dim arSkipCols(-1) as string\r
- UnparseSelect=Unparse(arSkipCols)\r
-end Function\r
-\r
-\r
-Public function UnparseSelectSkip(arSkipCols)\r
- UnparseSelectSkip=Unparse(arSkipCols)\r
-end Function\r
-\r
-\r
-Public Function UnparseSelectDistinct() As String\r
- dim arSkipCols(-1) as string\r
- IsDistinct=true\r
- UnparseSelectDistinct=Unparse(arSkipCols)\r
-end Function\r
-\r
-\r
-' MS Access does not support ordering by column name\r
-Public Function UnparseDistinctColumnAccess(colnum as integer) As String\r
- dim sqltext As String\r
- sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " FROM " & FromClause\r
- if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
- Headings.Clear()\r
- Headings.Add(SelectList(colnum).name)\r
- UnparseDistinctColumnAccess=sqltext & " ORDER BY " & SelectList(colnum).sql\r
-end Function\r
-\r
-\r
-Public Function UnparseDistinctColumn(colnum as integer) As String\r
- dim sqltext As String\r
- sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " as col1 FROM " & FromClause\r
- if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause\r
- Headings.Clear()\r
- Headings.Add(SelectList(colnum).name)\r
- UnparseDistinctColumn=sqltext & " ORDER BY col1"\r
-end Function\r
-\r
-\r
-Public function UnparseColumnList() As String\r
- dim strSelectList As New ArrayList(), i as integer, sql as String\r
- Headings.Clear()\r
- for i=0 to SelectList.count-1\r
- strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)\r
- Headings.Add(SelectList(i).name)\r
- next\r
- UnparseColumnList=join(strSelectList.ToArray(),",")\r
-end Function\r
-\r
-\r
-Public function UnparseColumnListSkip(arSkipCols() as String) As String\r
- dim strSelectList As New ArrayList(), i as integer\r
- dim SkipIdx as integer=0, skip as boolean\r
- Headings.Clear()\r
- for i=0 to SelectList.count-1\r
- skip=false\r
- if SkipIdx < arSkipCols.Length then\r
- skip=CBool(arSkipCols(SkipIdx)=CStr(i))\r
- if skip then SkipIdx+=1\r
- end if\r
- if not skip then\r
- strSelectList.Add(SelectList(i).sql & " AS rico_col" & i)\r
- Headings.Add(SelectList(i).name)\r
- end if\r
- next\r
- UnparseColumnListSkip=join(strSelectList.ToArray(),",")\r
-end Function\r
-\r
-\r
-' returns a "windowed" select query\r
-' includeAS should be true for SQL Server 2005+ and false for Oracle\r
-Public function UnparseWithRowNumber(offset as Integer, numrows as Integer, includeAS as Boolean, arSkipCols() as String) as String\r
- dim unparseText as String\r
- if OrderBy.count = 0 then Throw New Exception("an OrderBy clause is required")\r
- unparseText="SELECT ROW_NUMBER() OVER (ORDER BY " & join(OrderBy.ToArray(),",") & ") AS rico_rownum," & UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause\r
- if not IsNothing(WhereClause) then unparseText &= " WHERE " & WhereClause\r
- if GroupBy.count > 0 then unparseText &= " GROUP BY " & join(GroupBy.ToArray(),",")\r
- if not IsNothing(HavingClause) then unparseText &= " HAVING " & HavingClause\r
- unparseText="SELECT * FROM (" & unparseText & ")"\r
- if includeAS then unparseText &= " AS rico_Main"\r
- unparseText &= " WHERE rico_rownum > " & offset & " AND rico_rownum <= " & CStr(offset+numrows)\r
- UnparseWithRowNumber=unparseText\r
-end Function\r
-\r
-\r
-Public sub Init()\r
- SelectList.Clear()\r
- GroupBy.Clear()\r
- OrderBy.Clear()\r
- FromClause=Nothing\r
- WhereClause=Nothing\r
- HavingClause=Nothing\r
- IsDistinct=false\r
-end sub\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Parse a SQL select statement into its major components\r
-' Does not handle:\r
-' 1) union queries\r
-' 2) select into\r
-' 3) more than one space between "group" and "by", or "order" and "by"\r
-' 4) stored procedures\r
-' -------------------------------------------------------------\r
-Public function ParseSelect(ByVal sqltext as String) As Boolean\r
- dim i As Integer, j As Integer, l As Integer, idx As Integer, parencnt As Integer\r
- dim clause As String, ch As String, curfield As String, nexttoken As String, inquote As Boolean, endquote As String\r
- Init()\r
- ParseSelect=false\r
- sqltext=replace(sqltext,vbLf," ")\r
- sqltext=" " & replace(sqltext,vbCr," ") & " SELECT " ' SELECT suffix forces last curfield to be saved\r
- 'response.write "<p>ParseSelect: " & sqltext & "</p>"\r
- l=len(sqltext)\r
- parencnt=0\r
- inquote=false\r
- i=1\r
- curfield=""\r
- while i<l\r
- ch=mid(sqltext,i,1)\r
- if inquote then\r
- if ch=endquote then\r
- if endquote="'" and mid(sqltext,i,2)="''" then\r
- curfield &= "'"\r
- i=i+1\r
- else\r
- inquote=false\r
- end if\r
- end if\r
- curfield &= ch\r
- elseif ch="'" or ch="""" or ch="`" then\r
- inquote=true\r
- endquote=ch\r
- curfield &= ch\r
- elseif ch="[" then\r
- inquote=true\r
- endquote="]"\r
- curfield &= ch\r
- elseif ch="(" then\r
- parencnt=parencnt+1\r
- curfield &= ch\r
- elseif ch=")" then\r
- if parencnt=0 then exit function ' sql statement has a syntax error\r
- parencnt=parencnt-1\r
- curfield &= ch\r
- elseif parencnt > 0 then\r
- curfield &= ch\r
- elseif ch="," then\r
- 'response.write "<p>" & clause & ": " & server.htmlencode(curfield) & "</p>"\r
- select case clause\r
- case "SELECT":\r
- AddColumn(curfield)\r
- curfield=""\r
- case "AS":\r
- SelectList(SelectList.count-1).name=curfield\r
- curfield=""\r
- clause="SELECT"\r
- case "GROUP BY": ArrayPush(GroupBy,curfield)\r
- case "ORDER BY": ArrayPush(OrderBy,curfield)\r
- case else: curfield &= ch\r
- end select\r
- elseif ch=" " then\r
- j=InStr(i+1,sqltext," ")\r
- if j<1 then\r
- curfield &= ch\r
- else\r
- if ucase(mid(sqltext,j+1,3))="BY " then j=j+3\r
- nexttoken=ucase(mid(sqltext,i+1,j-i-1))\r
- 'wscript.echo "'" & nexttoken & "'"\r
- 'response.write "<p>" & clause & " : " & nexttoken & " : " & server.htmlencode(curfield) & "</p>"\r
- select case nexttoken\r
- case "SELECT","INTO","FROM","WHERE","GROUP BY","HAVING","ORDER BY":\r
- select case clause\r
- case "SELECT":\r
- AddColumn(curfield)\r
- curfield=""\r
- case "AS":\r
- SelectList(SelectList.count-1).name=curfield\r
- curfield=""\r
- case "FROM": SetParseField(FromClause,curfield)\r
- case "WHERE": SetParseField(WhereClause,curfield)\r
- case "GROUP BY": ArrayPush(GroupBy,curfield)\r
- case "HAVING": SetParseField(HavingClause,curfield)\r
- case "ORDER BY": ArrayPush(OrderBy,curfield)\r
- end select\r
- clause=nexttoken\r
- i=j-1\r
-\r
- case "AS":\r
- if clause="SELECT" then\r
- AddColumn(curfield)\r
- curfield=""\r
- clause=nexttoken\r
- i=j\r
- elseif curfield<>"" then\r
- curfield &= ch\r
- end if\r
-\r
- case "DISTINCT":\r
- if clause="SELECT" then\r
- IsDistinct=true\r
- curfield=""\r
- i=j\r
- elseif curfield<>"" then\r
- curfield &= ch\r
- end if\r
-\r
- case else: if curfield<>"" then curfield &= ch\r
- end select\r
- end if\r
- else\r
- curfield &= ch\r
- end if\r
- i=i+1\r
- end while\r
- ParseSelect=true\r
-end function\r
-\r
-\r
-Private Sub ArrayPush(s as ArrayList, ByRef newvalue as string)\r
- s.add(newvalue)\r
- newvalue=""\r
-end sub\r
-\r
-Private Sub SetParseField(ByRef f as string, ByRef newvalue as string)\r
- f=newvalue\r
- newvalue=""\r
-end sub\r
-\r
-\r
-Public Sub AddColumn(sqlParm as String, Optional nameParm As String = "")\r
- SelectList.add(new sqlColumn(sqlParm,nameParm))\r
-End Sub\r
-\r
-\r
-' -------------------------------------------------------------\r
-' Add a join to the from clause\r
-' -------------------------------------------------------------\r
-Public Sub AddJoin(ByVal JoinClause As String)\r
- if InStr(FromClause," join ")>0 then FromClause="(" & FromClause & ")" ' required by Access\r
- FromClause=FromClause & " " & JoinClause\r
-end sub\r
-\r
-Private Sub SplitSortSpec(ByVal sortspec As String, ByRef sortcol As String, ByRef sortdir As String)\r
- sortspec=ucase(sortspec)\r
- if right(sortspec,3)="ASC" then\r
- sortcol=trim(left(sortspec,len(sortspec)-3))\r
- sortdir="ASC"\r
- elseif right(sortspec,4)="DESC" then\r
- sortcol=trim(left(sortspec,len(sortspec)-4))\r
- sortdir="DESC"\r
- else\r
- sortcol=trim(sortspec)\r
- sortdir=""\r
- end if\r
-End Sub\r
-\r
-Private Function FindSortColumn(ByVal sortspec As String) As Integer\r
- dim i As Integer, findcol As String, finddir As String, sortcol As String, sortdir As String\r
- FindSortColumn=-1\r
- SplitSortSpec(sortspec, findcol, finddir)\r
- for i=0 to OrderBy.count-1\r
- SplitSortSpec(OrderBy(i), sortcol, sortdir)\r
- if sortcol=findcol then\r
- FindSortColumn=i\r
- exit for\r
- end if\r
- next\r
-End Function\r
-\r
-' -------------------------------------------------------------\r
-' Add sort criteria to the beginning of the order by clause\r
-' -------------------------------------------------------------\r
-Public Sub AddSort(ByVal NewSort As String)\r
- dim i As Integer, colidx As Integer\r
- colidx=FindSortColumn(NewSort)\r
- if colidx>=0 then\r
- for i=colidx to 1 step -1\r
- OrderBy(i)=OrderBy(i-1)\r
- next\r
- OrderBy(0)=NewSort\r
- else\r
- OrderBy.insert(0,NewSort)\r
- end if\r
-end sub\r
-\r
-' -------------------------------------------------------------\r
-' Append sort criteria to the order by clause\r
-' -------------------------------------------------------------\r
-Public Sub AppendSort(ByVal NewSort As String)\r
- OrderBy.add(NewSort)\r
-end sub\r
-\r
-' -------------------------------------------------------------\r
-' Add a condition to the where clause\r
-' -------------------------------------------------------------\r
-Public Sub AddWhereCondition(ByVal NewCondition)\r
- AddCondition(WhereClause,NewCondition)\r
-end sub\r
-\r
-' -------------------------------------------------------------\r
-' Add a condition to the having clause\r
-' -------------------------------------------------------------\r
-Public Sub AddHavingCondition(ByVal NewCondition)\r
- AddCondition(HavingClause,NewCondition)\r
-end sub\r
-\r
-Private Sub AddCondition(ByRef Clause, ByVal NewCondition)\r
- if IsNothing(NewCondition) then exit sub\r
- If IsNothing(Clause) Then\r
- Clause="(" & NewCondition & ")"\r
- Else\r
- Clause &= " AND (" & NewCondition & ")"\r
- End If\r
-End Sub\r
-\r
-Public Sub DebugPrint(writer as object)\r
- dim i as integer\r
- writer.write("<p>Parse Result:")\r
- writer.write("<table border='1'>")\r
- if IsDistinct then writer.write("<tr valign='top'><td>DISTINCT<td> ")\r
- writer.write("<tr valign='top'><td>COLUMNS:<td><ol>")\r
- for i=0 to SelectList.count-1\r
- writer.write("<li>" & SelectList(i).Unparse)\r
- next\r
- writer.write("</ol><tr valign='top'><td>FROM:<td>" & FromClause)\r
- if not IsNothing(WhereClause) then writer.write("<tr valign='top'><td>WHERE:<td>" & WhereClause)\r
- if GroupBy.count > 0 then writer.write("<tr valign='top'><td>GROUP BY:<td>" & join(GroupBy.ToArray(),"<br>"))\r
- if not IsNothing(HavingClause) then writer.write("<tr valign='top'><td>HAVING:<td>" & HavingClause)\r
- if OrderBy.count > 0 then writer.write("<tr valign='top'><td>ORDER BY:<td>" & join(OrderBy.ToArray(),"<br>"))\r
- writer.write("</table>")\r
-End Sub\r
-\r
-End Class\r