From: Matt Brown Date: Sat, 14 May 2011 19:20:26 +0000 (+0000) Subject: git-svn-id: svn://svn.code.sf.net/p/openrico/code/trunk/rico3@79 53df2df2-7ab5-4331... X-Git-Url: https://git.infodrom.org/?p=infodrom%2Frico3;a=commitdiff_plain;h=7dcfc3b9911c39cec99fb5cbe214002c1e902c23 git-svn-id: svn://svn.code.sf.net/p/openrico/code/trunk/rico3@79 53df2df2-7ab5-4331-af62-ea79255fa4e2 --- diff --git a/plugins/dotnet/AltTable.ascx b/plugins/dotnet/AltTable.ascx deleted file mode 100644 index a5cb173..0000000 --- a/plugins/dotnet/AltTable.ascx +++ /dev/null @@ -1,3 +0,0 @@ -<%@ Control Language="VB" - CodeFile="AltTable.ascx.vb" - Inherits="AltTable" %> diff --git a/plugins/dotnet/AltTable.ascx.vb b/plugins/dotnet/AltTable.ascx.vb deleted file mode 100644 index 44deb38..0000000 --- a/plugins/dotnet/AltTable.ascx.vb +++ /dev/null @@ -1,76 +0,0 @@ -Partial Class AltTable -Inherits System.Web.UI.UserControl - -public TblName as string -public TblAlias as string -public Delim as string = "," -public altFields as New ArrayList() - -Public Property FieldList() As String - Get - dim i as Integer - dim result as String="" - for i=0 to altFields.Count-1 - if i > 0 then result &= Delim - result &= altFields(i).ColName - next - Return result - End Get - Set(ByVal Value As String) - dim i as Integer - dim arFields=Value.split(Delim) - for i=0 to ubound(arFields) - if i = altFields.Count then altFields.Add(New AltColumnClass()) - altFields(i).ColName=arFields(i) - next - End Set -End Property - -Public Property FieldData() As String - Get - dim i as Integer - dim result as String="" - for i=0 to altFields.Count-1 - if i > 0 then result &= Delim - result &= altFields(i).Data - next - Return result - End Get - Set(ByVal Value As String) - dim i as Integer - dim arData=Value.split(Delim) - for i=0 to ubound(arData) - if IsNothing(altFields(i)) then altFields(i)=New AltColumnClass() - altFields(i).Data=arData(i) - next - End Set -End Property - - -Public Function FieldCondition(FieldIdx as Integer, optional WithAlias as Boolean=true) As String - dim s as String = altFields(FieldIdx).ColName & "=" & altFields(FieldIdx).Data - if WithAlias then s = TblAlias & "." & s - FieldCondition=s -End Function - - -Public Function KeyCondition(optional WithAlias as Boolean=false) As String - dim i as Integer - dim k as String = "" - for i=0 to altFields.Count-1 - if altFields(i).isKey then - k &= " AND (" & FieldCondition(i,WithAlias) & ")" - end if - next - KeyCondition=k -End Function - - -Public Class AltColumnClass - public ColName as string - public Data as string - public isKey as boolean -End Class - - -End Class diff --git a/plugins/dotnet/GridColumn.ascx b/plugins/dotnet/GridColumn.ascx deleted file mode 100644 index 4e43909..0000000 --- a/plugins/dotnet/GridColumn.ascx +++ /dev/null @@ -1,3 +0,0 @@ -<%@ Control Language="VB" - CodeFile="GridColumn.ascx.vb" - Inherits="GridColumn" %> diff --git a/plugins/dotnet/GridColumn.ascx.vb b/plugins/dotnet/GridColumn.ascx.vb deleted file mode 100644 index 5413690..0000000 --- a/plugins/dotnet/GridColumn.ascx.vb +++ /dev/null @@ -1,179 +0,0 @@ -Partial Class GridColumn -Inherits System.Web.UI.UserControl - -' ---------------------------------------------------- -' Properties -' ---------------------------------------------------- - -Public DataType as String ' maps to LiveGrid column 'type' -Public canSort as Boolean = True -Public canDrag as Boolean = False -Public canFilter as Boolean = True -Public control as String ' javascript code to create control object -Public format as String -Public visible as Boolean = True -Public ClassName as String -Public filterUI as String -Public filterCol as Integer = -1 -Public spec as String - -' number formatting -Public multiplier as Double = 1.0 -Public decPlaces as Integer = 0 -Public decPoint as String -Public thouSep as String -Public negSign as String -Public prefix as String -Public suffix as String - -' date formatting -Public dateFmt as String - -' LiveGrid Forms -Public pattern as String -Public min as String -Public max as String -Public Help as String -Public TxtAreaRows as Integer = 4 -Public TxtAreaCols as Integer = 80 -Public ColName as String -Public ColData as String -Public SelectSql as String -Public SelectCtl as String -Public SelectFilter as String -Public SelectValues as String -Public SelectRows as Integer = -1 -Public Formula as String -Public TableIdx as Integer = 0 -Public FilterFlag as Boolean = False -Public FieldName as String -Public isNullable as Boolean = False -Public Writeable as Boolean = True -Public FixedLength as Boolean -Public isKey as Boolean = False -Public Length as Integer = -1 -Public TypeName as String ' .net type -Public panelIdx as Integer = -1 -Public ConfirmDeleteColumn as Boolean = False -Public InsertOnly as Boolean = False -Public UpdateOnly as Boolean = False -Public [ReadOnly] as Boolean = False -Public FormView as String -Public AddQuotes as Boolean = True -Public AltTable as String -Public MultiSelect as Boolean = False -Public DescriptionCol as String -Public DescriptionField as String ' should populate automatically based on DescriptionCol -Public noFormBreak as Boolean = False - -Private _EntryType as String -Private _colHeading As String -Private _width As Integer = -1 - - -Public Property Heading() As String - Get - Return _colHeading - End Get - Set - _colHeading = value - End Set -End Property - -Public Property Width() As Integer - Get - Return _width - End Get - Set - _width = value - End Set -End Property - -Public Function isLookupField() as Boolean - isLookupField=CBool(mid(_EntryType,2,1)="L") -End Function - -Public Property EntryType() As String - Get - Return _EntryType - End Get - Set - select case value - case "TA","tinyMCE","R","RL","S","SL","CL","N","B","T","I","F": - case "D": DataType="date" - case "DT": DataType="datetime" - case "H": visible=false - case else: Throw New Exception("Invalid EntryType") - end select - _EntryType = value - End Set -End Property - - -Private function FmtBool(b) - if b then FmtBool="true" else FmtBool="false" -end function - -Public ReadOnly Property script() As String - Get - if not IsNothing(Me.spec) then Return "'" & Me.spec & "'" - dim a as New ArrayList() - if not IsNothing(Me.DataType) then a.Add("type: '" & Me.DataType & "'") - if not IsNothing(Me.control) then a.Add("control: " & Me.control) - if not IsNothing(Me.pattern) then a.Add("pattern: '" & Me.pattern & "'") - if not IsNothing(Me.min) then a.Add("min: '" & Me.min & "'") - if not IsNothing(Me.max) then a.Add("max: '" & Me.max & "'") - if not IsNothing(Me.Help) then a.Add("Help: '" & Me.Help & "'") - if not IsNothing(Me.format) then a.Add("format: '" & Me.format & "'") - if not Me.canSort then a.Add("canSort: false") - if Me.canDrag then a.Add("canDrag: true") - if not Me.canFilter then a.Add("canFilter: false") - if not Me.visible then a.Add("visible: false") - if Me._width >= 0 then a.Add("width: " & Me._width) - if not IsNothing(Me.ColName) then a.Add("ColName: '" & Me.ColName & "'") - if not IsNothing(Me.FieldName) then a.Add("FieldName: '" & Me.FieldName & "'") - if not IsNothing(Me.ClassName) then a.Add("ClassName: '" & Me.ClassName & "'") - if not IsNothing(Me.filterUI) then a.Add("filterUI: '" & Me.filterUI & "'") - if Me.filterCol >= 0 then a.Add("filterCol: " & Me.filterCol) - - if Me.multiplier <> 1.0 then a.Add("multiplier: " & Me.multiplier) - if Me.decPlaces > 0 then a.Add("decPlaces: " & Me.decPlaces) - if not IsNothing(Me.decPoint) then a.Add("decPoint: '" & Me.decPoint & "'") - if not IsNothing(Me.thouSep) then a.Add("thouSep: '" & Me.thouSep & "'") - if not IsNothing(Me.negSign) then a.Add("negSign: '" & Me.negSign & "'") - if not IsNothing(Me.prefix) then a.Add("prefix: '" & Me.prefix & "'") - if not IsNothing(Me.suffix) then a.Add("suffix: '" & Me.suffix & "'") - if not IsNothing(Me.dateFmt) then a.Add("dateFmt: '" & Me.dateFmt & "'") - if Me.panelIdx >= 0 then a.Add("panelIdx: " & Me.panelIdx) - - if not IsNothing(Me.EntryType) then - a.Add("EntryType: '" & Me.EntryType & "'") - if Me.EntryType="D" and ucase(Me.ColData)="TODAY" then - a.Add("ColData: '" & DateTime.Today.ToString("s") & "'") - else - a.Add("ColData: '" & replace(Me.ColData,"'","\'") & "'") - end if - if Me.EntryType="TA" or Me.EntryType="tinyMCE" then - a.Add("TxtAreaRows: " & Me.TxtAreaRows) - a.Add("TxtAreaCols: " & Me.TxtAreaCols) - end if - if not IsNothing(Me.FormView) then a.Add("FormView: '" & Me.FormView & "'") - if not IsNothing(Me.SelectCtl) then a.Add("SelectCtl: '" & Me.SelectCtl & "'") - if not IsNothing(Me.SelectValues) then a.Add("SelectValues: '" & Me.SelectValues & "'") - if Me.Length >= 0 then a.Add("Length: " & Me.Length) - if Me.isNullable then a.Add("isNullable: true") - if Me.isKey then a.Add("isKey: true") - a.Add("Writeable: " & FmtBool(Me.Writeable)) - if Me.InsertOnly then a.Add("InsertOnly: true") - if Me.UpdateOnly or not IsNothing(AltTable) then a.Add("UpdateOnly: true") - if Me.ReadOnly then a.Add("ReadOnly: true") - if Me.MultiSelect then a.Add("MultiSelect: true") - if Me.noFormBreak then a.Add("noFormBreak: true") - if not IsNothing(Me.DescriptionField) then a.Add("DescriptionField: '" & Me.DescriptionField & "'") - if Me.SelectRows > 0 then a.Add("SelectRows: " & Me.SelectRows) - end if - Return " {" & String.Join("," & vbCrLf & " ", a.ToArray(Type.GetType("System.String"))) & " }" - End Get -End Property - -End Class diff --git a/plugins/dotnet/GridPanel.ascx b/plugins/dotnet/GridPanel.ascx deleted file mode 100644 index fee15e2..0000000 --- a/plugins/dotnet/GridPanel.ascx +++ /dev/null @@ -1,3 +0,0 @@ -<%@ Control Language="VB" - CodeFile="GridPanel.ascx.vb" - Inherits="GridPanel" %> diff --git a/plugins/dotnet/GridPanel.ascx.vb b/plugins/dotnet/GridPanel.ascx.vb deleted file mode 100644 index 5d6755d..0000000 --- a/plugins/dotnet/GridPanel.ascx.vb +++ /dev/null @@ -1,15 +0,0 @@ -Partial Class GridPanel -Inherits System.Web.UI.UserControl - -private _heading as string - -public property heading as string - get - return _heading - end get - set - _heading=value - end set -end property - -End Class diff --git a/plugins/dotnet/LiveGrid.ascx b/plugins/dotnet/LiveGrid.ascx deleted file mode 100644 index f5b4888..0000000 --- a/plugins/dotnet/LiveGrid.ascx +++ /dev/null @@ -1,25 +0,0 @@ -<%@ Control Language="VB" - CodeFile="LiveGrid.ascx.vb" - Inherits="LiveGrid" Debug="true" %> -<%@ Register TagPrefix="Rico" TagName="Column" Src="GridColumn.ascx" %> -<%@ Register TagPrefix="Rico" TagName="Panel" Src="GridPanel.ascx" %> -<%@ Register TagPrefix="Rico" TagName="AltTable" Src="AltTable.ascx" %> -<%@ Register TagPrefix="Rico" TagName="sqlParse" Src="sqlParse.ascx" %> -<%@ Register TagPrefix="Rico" TagName="sqlCompatibilty" Src="sqlCompatibilty.ascx" %> - - - -<%=Me.DebugString%> -<%=Me.Bookmark%> - - - - - - - - -
diff --git a/plugins/dotnet/LiveGrid.ascx.vb b/plugins/dotnet/LiveGrid.ascx.vb deleted file mode 100644 index 618c237..0000000 --- a/plugins/dotnet/LiveGrid.ascx.vb +++ /dev/null @@ -1,1093 +0,0 @@ -Imports System.Data - -Partial Class LiveGrid -Inherits System.Web.UI.UserControl - -' ---------------------------------------------------- -' Constants -' ---------------------------------------------------- - -Public Const sizeToWindow=-1 -Public Const sizeToData=-2 -Public Const sizeToBody=-3 -Public Const sizeToParent=-4 - -' ---------------------------------------------------- -' Private Properties -' ---------------------------------------------------- - -Private _rows As Integer = sizeToBody -Private _sqlFilters as New ArrayList() -Private _gridHeading As ITemplate = Nothing -Private _headingTop As ITemplate = Nothing -Private _headingBottom As ITemplate = Nothing -Protected globalInitScript as String = "" -Protected HdgContainer As New GridContainer() -Protected DebugString As String -Protected oSqlCompat as sqlCompatibilty -Protected oParseMain as New sqlParse() - - -' ---------------------------------------------------- -' Public Properties -' ---------------------------------------------------- -Public columns as New ArrayList() -Public dataProvider as String = "ricoQuery.aspx" -Public menuEvent as String = "dblclick" -Public frozenColumns as Integer = 0 -Public canSortDefault as Boolean = True -Public canHideDefault as Boolean = True -Public canFilterDefault as Boolean = True -Public allowColResize as Boolean = True -Public highlightElem as String = "menuRow" -Public highlightMethod as String -Public prefetchBuffer as Boolean = True -Public DisplayTimer as Boolean = True -Public DisplayBookmark as Boolean = True -Public Caption as String -Public click as String -Public dblclick as String -Public contextmenu as String -Public headingSort as String -Public beforeInit as String -Public afterInit as String -Public TableFilter as String -Public FilterLocation as Integer = -2 -Public FilterAllToken as String -Public FilterBoxMaxLen as Integer = -1 -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 -Public saveColumnFilter as Boolean = False -Public saveColumnSort as Boolean = False -Public cookieDays as Integer -Public DefaultSort as String -Public maxPrint as Integer = -1 -Public dndMgrIdx as Integer = -1 -Public UsingMinRico as Boolean = False ' using minified version of Rico? -Public sessions as Boolean = True -Public minPageRows as Integer = -1 -Public maxPageRows as Integer = -1 -Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels -Public debug as Boolean = False -Public LogSqlOnError as Boolean = false ' include sql statement in results if an error occurs (true/false) - -' ---------------------------------------------------- -' Public Properties for buffer -' ---------------------------------------------------- -Public BufferType as String = "AjaxSQL" ' can be overridden to AjaxXML -Public fmt as String = "xml" -Public largeBufferSize as Integer = -1 ' controls size of client buffer and AJAX fetch size -Public requestParameters as New Hashtable() - - -Public Property rows() As Integer - Get - Return _rows - End Get - Set(ByVal Value As Integer) - _rows=Value - End Set -End Property - - _ -Public Property GridColumns() As ITemplate - Get - Return _gridHeading - End Get - Set - _gridHeading = value - End Set -End Property - - _ -Public Property HeadingTop() As ITemplate - Get - Return _headingTop - End Get - Set - _headingTop = value - End Set -End Property - - _ -Public Property HeadingBottom() As ITemplate - Get - Return _headingBottom - End Get - Set - _headingBottom = value - End Set -End Property - -Public WriteOnly Property sqlQuery() As String - Set(ByVal SqlText As String) - if oParseMain.ParseSelect(SqlText) then - ' sync column headings - dim i as Integer - for i=0 to oParseMain.SelectList.Count-1 - if i < Me.columns.Count then - if IsNothing(Me.columns(i).Heading) then - Me.columns(i).Heading=oParseMain.SelectList(i).name - else - oParseMain.SelectList(i).name=Me.columns(i).Heading - end if - else - AddCalculatedField(oParseMain.SelectList(i).name, oParseMain.SelectList(i).sql) - end if - next - if sessions then session.contents(Me.UniqueId)=oParseMain - else - Throw New Exception("Invalid SQL statement") - end if - End Set -End Property - -Public ReadOnly Property ParseClone() As object - Get - return oParseMain.Clone() - End Get -End Property - -Protected ReadOnly Property TimerSpan() As String - Get - if Me.DisplayTimer then - Return " " - else - Return "" - end if - End Get -End Property - -Protected ReadOnly Property BookmarkSpan() As String - Get - if Me.DisplayBookmark then - Return " " - else - Return "" - end if - End Get -End Property - -Protected ReadOnly Property SaveMsgSpan() As String - Get - if Me.formView then - Return "" - else - Return "" - end if - End Get -End Property - -Protected ReadOnly Property CaptionSpan() As String - Get - if IsNothing(Caption) then - Return "" - else - Return "" & Me.Caption & "" - end if - End Get -End Property - -Protected ReadOnly Property FilterIcon() As String - Get - if FilterLocation >= -1 then - Return "" - else - Return "" - end if - End Get -End Property - -Protected ReadOnly Property Bookmark() As String - Get - if Me.DisplayBookmark or Me.DisplayTimer or not IsNothing(Caption) then - Return "

" & Me.CaptionSpan & Me.TimerSpan & Me.FilterIcon & Me.BookmarkSpan & Me.SaveMsgSpan & "

" - else - Return "" - end if - End Get -End Property - -Private function FmtBool(b) - if b then FmtBool="true" else FmtBool="false" -end function - -Protected ReadOnly Property init_Script() As String - Get - Dim script as New System.Text.StringBuilder(), confirmCol as Integer=0 - script.Append("var " & Me.UniqueId & " = {};" & vbCrLf) - script.Append("function " & Me.UniqueId & "_init" & "() {" & vbCrLf) - if not IsNothing(beforeInit) then script.Append(beforeInit & vbCrLf) - - ' grid options - - script.Append(" " & optionsVar & " = {" & vbCrLf) - script.Append(" visibleRows: " & Me.rows & "," & vbCrLf) - script.Append(" frozenColumns: " & frozenColumns & "," & vbCrLf) - script.Append(" canSortDefault: " & FmtBool(canSortDefault) & "," & vbCrLf) - script.Append(" canHideDefault: " & FmtBool(canHideDefault) & "," & vbCrLf) - script.Append(" canFilterDefault: " & FmtBool(canFilterDefault) & "," & vbCrLf) - script.Append(" allowColResize: " & FmtBool(allowColResize) & "," & vbCrLf) - script.Append(" highlightElem: '" & highlightElem & "'," & vbCrLf) - if not IsNothing(highlightMethod) then script.Append(" highlightMethod: '" & highlightMethod & "'," & vbCrLf) - script.Append(" prefetchBuffer: " & FmtBool(prefetchBuffer) & "," & vbCrLf) - script.Append(" menuEvent: '" & menuEvent & "'," & vbCrLf) - if not IsNothing(RecordName) then script.Append(" RecordName: '" & RecordName & "'," & vbCrLf) - script.Append(" saveColumnInfo: {width:" & FmtBool(saveColumnWidth) & ", filter:" & FmtBool(saveColumnFilter) & ", sort:" & FmtBool(saveColumnSort) & "}," & vbCrLf) - if not IsNothing(cookieDays) then script.Append(" cookieDays: " & cookieDays & "," & vbCrLf) - - if panels.count > 0 then - script.Append(" PanelNamesOnTabHdr: " & FmtBool(PanelNamesOnTabHdr) & "," & vbCrLf) - script.Append(" panels: ['" & join(panels.ToArray(),"','") & "']," & vbCrLf) - end if - if not IsNothing(headingSort) then script.Append(" headingSort: '" & headingSort & "'," & vbCrLf) - if not IsNothing(click) then script.Append(" click: " & click & "," & vbCrLf) - if not IsNothing(dblclick) then script.Append(" dblclick: " & dblclick & "," & vbCrLf) - if not IsNothing(contextmenu) then script.Append(" contextmenu: " & contextmenu & "," & vbCrLf) - if FilterLocation >= -1 then script.Append(" FilterLocation: " & FilterLocation & "," & vbCrLf) - if not IsNothing(FilterAllToken) then script.Append(" FilterAllToken: '" & FilterAllToken & "'," & vbCrLf) - if FilterBoxMaxLen >= 0 then script.Append(" FilterBoxMaxLen: " & FilterBoxMaxLen & "," & vbCrLf) - if FilterAnchorLeft then script.Append(" FilterAnchorLeft: " & FmtBool(FilterAnchorLeft) & "," & vbCrLf) - if maxPrint >= 0 then script.Append(" maxPrint: " & maxPrint & "," & vbCrLf) - if dndMgrIdx >= 0 then script.Append(" dndMgrIdx: " & dndMgrIdx & "," & vbCrLf) - if minPageRows >= 0 then script.Append(" minPageRows: " & minPageRows & "," & vbCrLf) - if maxPageRows >= 0 then script.Append(" maxPageRows: " & maxPageRows & "," & vbCrLf) - if defaultWidth > 0 then script.Append(" defaultWidth: " & defaultWidth & "," & vbCrLf) - - if formView then - script.Append(" canAdd: " & FmtBool(canAdd) & "," & vbCrLf) - script.Append(" canEdit: " & FmtBool(canEdit) & "," & vbCrLf) - script.Append(" canClone: " & FmtBool(canClone) & "," & vbCrLf) - script.Append(" canDelete: " & FmtBool(canDelete) & "," & vbCrLf) - script.Append(" ConfirmDelete: " & FmtBool(ConfirmDelete) & "," & vbCrLf) - script.Append(" TableSelectNew: '" & TableSelectNew & "'," & vbCrLf) - script.Append(" TableSelectNone: '" & TableSelectNone & "'," & vbCrLf) - if panelHeight > 0 then script.Append(" panelHeight: " & panelHeight & "," & vbCrLf) - if panelWidth > 0 then script.Append(" panelWidth: " & panelWidth & "," & vbCrLf) - if maxDisplayLen > 0 then script.Append(" maxDisplayLen: " & maxDisplayLen & "," & vbCrLf) - if not IsNothing(formOpen) then script.Append(" formOpen: " & formOpen & "," & vbCrLf) - if not IsNothing(formClose) then script.Append(" formClose: " & formClose & "," & vbCrLf) - if not IsNothing(formSubmit) then script.Append(" formSubmit: " & onSubmitResponse & "," & vbCrLf) - if not IsNothing(onSubmitResponse) then script.Append(" onSubmitResponse: " & onSubmitResponse & "," & vbCrLf) - if not IsNothing(showSaveMsg) then script.Append(" showSaveMsg: '" & showSaveMsg & "'," & vbCrLf) - end if - script.Append(" columnSpecs : [" & vbCrLf) - Dim c as Integer - for c=0 to columns.count-1 - if c > 0 then script.Append("," & vbCrLf) - script.Append(CType(columns(c),GridColumn).script) - if columns(c).ConfirmDeleteColumn then confirmCol=c - next - script.Append("]") - if formView then script.Append("," & vbCrLf & "ConfirmDeleteCol: " & confirmCol) - script.Append(vbCrLf & " }" & vbCrLf) - - ' buffer - - dim a as New ArrayList() - script.Append(" " & bufferOptVar & " = {") - if requestParameters.Count > 0 then - Dim param As DictionaryEntry - For Each param In requestParameters - a.Add(vbCrLf & " {name:'" & param.Key & "',value:'" & param.Value & "'}") - Next - script.Append(vbCrLf & " requestParameters: [" & String.Join(",", a.ToArray(Type.GetType("System.String"))) & vbCrLf & " ]") - end if - if BufferType="AjaxSQL" then - if a.Count > 0 then script.Append(",") - script.Append(vbCrLf & " TimeOut: " & Session.Timeout & ",") - if largeBufferSize > 0 then script.Append(vbCrLf & " largeBufferSize: " & largeBufferSize & ",") - script.Append(vbCrLf & " fmt: '" & fmt & "'") - end if - script.Append(vbCrLf & " }" & vbCrLf) - script.Append(" " & bufferVar & " = new Rico.Buffer." & BufferType & "('" & dataProvider & "', " & bufferOptVar & ");" & vbCrLf) - - ' grid - - script.Append(" " & gridVar & " = new Rico.LiveGrid ('" & Me.UniqueId & "', " & bufferVar & ", " & optionsVar & ");" & vbCrLf) - if not IsNothing(menuEvent) then - script.Append(" " & gridVar & ".menu = new Rico.GridMenu();" & vbCrLf) - end if - - ' form - - if formView then - script.Append(" if(typeof " & Me.UniqueId & "_FormInit=='function') " & Me.UniqueId & "_FormInit();" & vbCrLf) - script.Append(" " & formVar & "=new Rico.TableEdit(" & gridVar & ");" & vbCrLf) - end if - - script.Append(" if(typeof " & Me.UniqueId & "_InitComplete=='function') " & Me.UniqueId & "_InitComplete();" & vbCrLf) - if not IsNothing(afterInit) then script.Append(afterInit & vbCrLf) - - script.Append("}" & vbCrLf) - Return script.ToString - End Get -End Property - - -' ---------------------------------------------------- -' Properties for LiveGridForms -' ---------------------------------------------------- - -Public dbConnection as object -Public formView as Boolean = false -Public TableSelectNew as String = "___new___" -Public TableSelectNone as String = "" -Public canAdd as Boolean = true -Public canEdit as Boolean = true -Public canClone as Boolean = false -Public canDelete as Boolean = true -Public ConfirmDelete as Boolean = true -Public RecordName as String -Public PanelNamesOnTabHdr as Boolean = true -Public showSaveMsg as String -Public dbDialect as String -Public panels as New ArrayList() -Public panelHeight as Integer = -1 -Public panelWidth as Integer = -1 -Public maxDisplayLen as Integer = -1 - -' events -Public formOpen as String -Public formClose as String -Public formSubmit as String -Public onSubmitResponse as String - -Public gridVar as String -Public formVar as String -Public bufferVar as String -Public bufferOptVar as String ' name of buffer options js var -Public optionsVar as String ' name of grid options js var - -Protected Tables as New ArrayList() -Protected _action As String -Protected MainTbl as Integer = -1 - - -Public Property TableName() As String - Get - if MainTbl >= 0 then - Return Tables(MainTbl).TblName - else - Return Nothing - end if - End Get - Set - MainTbl=Tables.Count - dim tab as new AltTable() - tab.TblName=value - tab.TblAlias="t" - AddTable(tab) - End Set -End Property - -Public Function AddTable(t as AltTable) as Integer - AddTable=Tables.Count - if IsNothing(t.TblAlias) then t.TblAlias="a" & Tables.Count - Tables.Add(t) -End Function - -Public ReadOnly Property action() As String - Get - Return _action - End Get -End Property - -Public ReadOnly Property CurrentField() As GridColumn - Get - Return columns(columns.count-1) - End Get -End Property - - -' ---------------------------------------------------- -' Methods -' ---------------------------------------------------- -Sub Page_Init() - formVar=Me.UniqueId & "['edit']" - gridVar=Me.UniqueId & "['grid']" - bufferVar=Me.UniqueId & "['buffer']" - bufferOptVar=Me.UniqueId & "['bufferopt']" - optionsVar=Me.UniqueId & "['options']" - dim actionparm as String="_action_" & Me.UniqueId - _action=trim(Request.QueryString(actionparm)) - if _action="" then _action=trim(Request.Form(actionparm)) - if _action="" then _action="table" else _action=lcase(_action) - - If Not (_gridHeading Is Nothing) Then - _gridHeading.InstantiateIn(HdgContainer) - For Each ctrl As Control In HdgContainer.Controls - If TypeOf(ctrl) is GridColumn then - AddColumn(CType(ctrl,GridColumn)) - ElseIf TypeOf(ctrl) is GridPanel then - panels.Add(CType(ctrl,GridPanel).heading) - ElseIf TypeOf(ctrl) is AltTable then - AddTable(ctrl) - end if - Next - End If - - If Not (_headingTop Is Nothing) Then - Dim container As New GridContainer() - _headingTop.InstantiateIn(container) - LiveGridHeadingsTop.Controls.Add(container) - End If - - If Not (_headingBottom Is Nothing) Then - Dim container As New GridContainer() - _headingBottom.InstantiateIn(container) - LiveGridHeadingsBottom.Controls.Add(container) - End If -End Sub - - -' ------------------------------------------------------------- -' Adds a new column to grid, returns column index -' ------------------------------------------------------------- -Public Function AddColumn(ColumnObj as GridColumn) as integer - if ColumnObj.isLookupField() then - - ' this items get applied to the lookup field instead of the code field - dim Hdg as string = ColumnObj.Heading - dim width as integer = ColumnObj.Width - dim filterUI as string = ColumnObj.filterUI - dim ConfirmDelete as boolean = ColumnObj.ConfirmDeleteColumn - dim DescriptionCol as String = ColumnObj.DescriptionCol - - ColumnObj.Heading=Hdg & " Code" - ColumnObj.panelIdx=panels.count-1 - ColumnObj.FieldName=ExtFieldId(columns.count) - ColumnObj.filterUI=Nothing - ColumnObj.Width=-1 - ColumnObj.visible=false - ColumnObj.ConfirmDeleteColumn=false - if not IsNothing(DescriptionCol) then - ColumnObj.DescriptionField=ExtFieldId(columns.count+1) - end if - columns.Add(ColumnObj) - - ColumnObj=new GridColumn() - ColumnObj.filterUI=filterUI - ColumnObj.Width=width - ColumnObj.Heading=Hdg - ColumnObj.ConfirmDeleteColumn=ConfirmDelete - if IsNothing(DescriptionCol) then - ColumnObj.Formula="" ' to be filled in by FormSqlQuery() - else - ColumnObj.ColName=DescriptionCol - ColumnObj.FormView="hidden" - ColumnObj.EntryType="T" - end if - end if - ColumnObj.panelIdx=panels.count-1 - ColumnObj.FieldName=ExtFieldId(columns.count) - AddColumn=columns.count - columns.Add(ColumnObj) -End Function - - -' ------------------------------------------------------------- -' Adds a new column to grid, returns column index -' ------------------------------------------------------------- -Public Function AddCalculatedField(Heading as string, ColumnFormula as string, optional width as integer = -1, optional ClassName as string = "") as GridColumn - Dim ColumnObj as New GridColumn() - if left(ColumnFormula,1) <> "(" then ColumnFormula="(" & ColumnFormula & ")" - ColumnObj.ColName="Calc_" & columns.count - ColumnObj.Formula=ColumnFormula - ColumnObj.Heading=Heading - if width >= 0 then ColumnObj.Width=width - if ClassName <> "" then ColumnObj.ClassName=ClassName - AddColumn(ColumnObj) - AddCalculatedField=ColumnObj -End Function - - -Private Function IsFieldName(s) as boolean - dim i as integer, c as string - i=1 - IsFieldName=false - while i <= len(s) - c=mid(s,i,1) - if (c >= "0" and c <= "9" and i > 1) or (c >= "A" and c <= "Z") or (c >= "a" and c <= "z") or (c = "_") then - i=i+1 - else - exit function - end if - end while - IsFieldName=(i > 1) -End Function - - -' name used external to this script -Private function ExtFieldId(i) as string - ExtFieldId=Me.UniqueId & "_" & i -end function - - -Private function FormatValue(v as String, ByVal ColIdx as Integer) as String - dim addquotes as Boolean = columns(ColIdx).AddQuotes - select case left(columns(ColIdx).EntryType,1) - case "I","F": - addquotes=false - if not IsNumeric(v) then v="" - case "N": - if v=TableSelectNew then - v=trim(Request.Form("textnew__" & ExtFieldId(ColIdx))) - elseif v=TableSelectNone then - v="" - end if - case "S","R": - if v=TableSelectNone then v="" - end select - if v="" and columns(ColIdx).isNullable then - FormatValue="NULL" - elseif addquotes then - FormatValue=oSqlCompat.addQuotes(v) - else - FormatValue=v - end if -end function - - -Private function FormatFormValue(idx as Integer) as String - dim v as String - if IsNothing(columns(idx).EntryType) then exit function - if columns(idx).EntryType="H" or columns(idx).FormView="exclude" then - v=columns(idx).ColData - else - v=trim(Request.Form(ExtFieldId(idx))) - end if - FormatFormValue=FormatValue(v,idx) -end function - - -Private Function AltTableJoinClause(AltTabIdx as Integer) as String - dim i as Integer, Condition as String - for i=0 to columns.Count-1 - if columns(i).TableIdx=MainTbl and columns(i).isKey then - AddCondition(Condition, Tables(MainTbl).TblAlias & "." & columns(i).ColName & "=" & Tables(AltTabIdx).TblAlias & "." & columns(i).ColName) - end if - next - for i=0 to Tables(AltTabIdx).altFields.Count-1 - if Tables(AltTabIdx).altFields(i).isKey then - AddCondition(Condition, Tables(AltTabIdx).FieldCondition(i)) - end if - next - AltTableJoinClause=Condition -End Function - - -' ------------------------------------------------------------- -' Add a condition to a where or having clause -' ------------------------------------------------------------- -Public Sub AddCondition(ByRef WhereClause as String, ByVal NewCondition as String) - if IsNothing(NewCondition) then exit sub - If IsNothing(WhereClause) Then - WhereClause = "(" & NewCondition & ")" - Else - WhereClause &= " AND (" & NewCondition & ")" - End If -End Sub - - -' ------------------------------------------------------------- -' Return the form value of a column based on its column name (insert or update action) -' ------------------------------------------------------------- -Public Function FormValue(ByVal SearchName as String) as String - Dim idx as Integer = ColIndex(SearchName) - if idx < 0 then - FormValue = "" - else - FormValue = FormatFormValue(idx) - end if -End Function - - -' ------------------------------------------------------------- -' Return the index of a column based on its column name, or -1 if not found -' ------------------------------------------------------------- -Public Function ColIndex(ByVal SearchName as String) as Integer - dim i as Integer - ColIndex=-1 - for i=0 to columns.Count-1 - if columns(i).ColName=SearchName then - ColIndex=i - Exit Function - end if - next -End Function - - -' ------------------------------------------------------------- -' Return the a column object based on its column name, or Nothing if not found -' ------------------------------------------------------------- -Public Function getColumnByName(ByVal SearchName as String) as GridColumn - dim i as Integer - for i=0 to columns.Count-1 - if columns(i).ColName=SearchName then - getColumnByName=columns(i) - Exit Function - end if - next -End Function - - -' ------------------------------------------------------------- -' Return the a column object based on its heading, or Nothing if not found -' ------------------------------------------------------------- -Public Function getColumnByHeading(ByVal SearchName as String) as GridColumn - dim i as Integer - for i=0 to columns.Count-1 - if columns(i).Heading=SearchName then - getColumnByHeading=columns(i) - Exit Function - end if - next -End Function - - -' ------------------------------------------------------------- -' Return the index of a table based on its table name -' ------------------------------------------------------------- -Public Function TabIndex(ByVal SearchName as String) - dim i as Integer - for i=0 to Tables.Count-1 - if Tables(i).TblName=SearchName then - TabIndex=i - Exit Function - end if - next -End Function - - -' ------------------------------------------------------------- -' Return the index of the new filter -' ------------------------------------------------------------- -Public Function AddFilter(ByVal newfilter as String) - AddFilter=_sqlFilters.Count - _sqlFilters.Add(newfilter) -End Function - - -' form where clause based on table's primary key -Public function MainTableKeyWhereClause() as String - MainTableKeyWhereClause = TableKeyWhereClause(MainTbl) -End Function - - -' form where clause based on table's primary key -Private function TableKeyWhereClause(TabIdx as Integer) as String - dim i as Integer, w as String - for i=0 to columns.Count-1 - if (columns(i).TableIdx=MainTbl or columns(i).TableIdx=TabIdx) and IsNothing(columns(i).Formula) and columns(i).isKey then - AddCondition(w, columns(i).ColName & "=" & FormatValue(trim(Request.Form("_k" & i)),i)) - end if - next - if IsNothing(w) then - 'Throw New Exception("no key value") - else - TableKeyWhereClause=" WHERE " & w - end if -end function - - -Protected Sub GetColumnInfo() - dim t as Integer, r as Integer, c as Integer, colname as String, schemaTable As DataTable - Dim restrictions(3) As String - if IsNothing(Me.dbConnection) then exit sub - for t=0 to Tables.Count-1 - if debug then DebugString &= "

Table: " & Tables(t).TblName & " tblidx=" & t & " colcnt=" & columns.Count - - Dim command = Me.dbConnection.CreateCommand() - command.CommandText = "select * from " & Tables(t).TblName - dim rdr = command.ExecuteReader(CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly) - schemaTable = rdr.GetSchemaTable() - For Each colinfo As DataRow In schemaTable.Rows - colname = colinfo("ColumnName").ToString - for c=0 to columns.Count-1 - if t=columns(c).TableIdx and colname=columns(c).ColName then - with columns(c) - .isNullable=CBool(colinfo("AllowDBNull")) - .TypeName=replace(colinfo("DataType").ToString(),"System.","") - if .TypeName<>"String" AndAlso not IsDBNull(colinfo("NumericPrecision")) AndAlso colinfo("NumericPrecision")<>0 then - .Length=colinfo("NumericPrecision") - elseif not IsDBNull(colinfo("ColumnSize")) then - .Length=colinfo("ColumnSize") - end if - .Writeable=not colinfo("IsReadOnly") - .isKey=colinfo("IsKey") - 'columns(c).FixedLength=((colinfo("COLUMN_FLAGS") and &H0000010) <> 0) - if debug then DebugString &= "
Column: " & colname & " type=" & .TypeName & " len=" & .Length & " nullable=" & .isNullable & " isKey=" & .isKey - end with - exit for - end if - next - - for c=0 to Tables(t).altFields.Count-1 - if colname=Tables(t).altFields(c).ColName then - Tables(t).altFields(c).isKey=colinfo("IsKey") - exit for - end if - next - Next - rdr.Close() - - ' AllowDBNull is not accurate when using Jet driver - if InStr(Me.dbConnection.ConnectionString,"Microsoft.Jet") > 0 then - restrictions(2)=Tables(t).TblName - schemaTable = Me.dbConnection.GetSchema("Columns",restrictions) - For Each colinfo As DataRow In schemaTable.Rows - colname = colinfo("column_name").ToString - for c=0 to columns.Count-1 - if t=columns(c).TableIdx and colname=columns(c).ColName then - with columns(c) - .isNullable=CBool(colinfo("is_nullable")) - if debug then DebugString &= "
Column: " & colname & " nullable=" & .isNullable - end with - exit for - end if - next - Next - end if - Next -End Sub - - -Protected Function UpdateDatabase(sqltext as String, actiontxt as String) as String - dim cnt as Integer - if IsNothing(Me.dbConnection) then - UpdateDatabase="

ERROR: no database connection

" - else - Try - Dim command = Me.dbConnection.CreateCommand() - command.CommandText = sqltext - cnt=command.ExecuteNonQuery() - UpdateDatabase="

" - Catch ex As Exception - dim msg="

ERROR: unable to update database - " & server.HTMLencode(ex.Message.ToString()) & "

" - if LogSqlOnError then msg &= "
" & server.HTMLencode(sqltext) - UpdateDatabase=msg - End Try - 'if debug then msg &= " - " & sqltext & " - Records affected: " & cnt - end if -End Function - - -Public Sub DeleteRecord(writer as HTMLTextWriter) - dim sqltext as String = "DELETE FROM " & Tables(MainTbl).TblName & TableKeyWhereClause(MainTbl) - writer.WriteLine(UpdateDatabase(sqltext, "deleted")) -End Sub - - -Public Sub InsertRecord(writer as HTMLTextWriter) - dim i as Integer, keyIdx as Integer - dim keyCnt as Integer=0 - dim sqlcol as String="" - dim sqlval as String="" - for i=0 to columns.Count-1 - if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).UpdateOnly=false then - if columns(i).isKey then - keyCnt=keyCnt+1 - keyIdx=i - end if - if columns(i).Writeable then - sqlcol &= "," & columns(i).ColName - sqlval &= "," & FormatFormValue(i) - end if - end if - next - if IsNothing(sqlcol) then - writer.WriteLine("

Nothing to add

") - else - dim sqltext as String="insert into " & Tables(MainTbl).TblName & " (" & mid(sqlcol,2) & ") values (" & mid(sqlval,2) & ")" - dim updateMsg as String = UpdateDatabase(sqltext, "added") - writer.WriteLine(updateMsg) - end if -End Sub - - -Public Sub UpdateRecord(writer as HTMLTextWriter) - dim i as Integer, sqltext as String, errmsg as String="" - for i=0 to Tables.Count-1 - if i<>MainTbl then errmsg &= UpdateAltTableRecords(i) - next - if errmsg<>"" then - writer.WriteLine("

" & errmsg & "

") - exit sub - end if - for i=0 to columns.Count-1 - if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).Writeable and columns(i).InsertOnly=false then - sqltext &= "," & columns(i).ColName & "=" & FormatFormValue(i) - end if - next - if not IsNothing(sqltext) then - sqltext="UPDATE " & Tables(MainTbl).TblName & " SET " & mid(sqltext,2) & TableKeyWhereClause(MainTbl) - writer.WriteLine(UpdateDatabase(sqltext, "updated")) - elseif Tables.Count > 1 then - ' only updated altTable records - writer.WriteLine("

") - else - writer.WriteLine("

Nothing to update

") - end if -End Sub - - -Private function UpdateAltTableRecords(tabidx as Integer) - dim j as Integer, cnt as Integer - dim sqltext as String, colnames as String, coldata as String - dim whereClause as String, errmsg as String - - ' check for existing record - - whereClause = TableKeyWhereClause(tabidx) & Tables(tabidx).KeyCondition() - sqltext="select count(*) from " & Tables(tabidx).TblName & " " & Tables(tabidx).TblAlias & whereClause - Try - Dim command = Me.dbConnection.CreateCommand() - command.CommandText = sqltext - cnt=command.ExecuteScalar() - Catch ex As Exception - errmsg = "UpdateAltTableRecords Count
ERROR: " & server.HTMLencode(ex.Message.ToString()) - if LogSqlOnError then errmsg &= "
" & sqltext - UpdateAltTableRecords = errmsg - exit function - End Try - - if cnt = 0 then - - ' insert new record - - colnames="" - coldata="" - for j=0 to columns.Count-1 - if (columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType)) or columns(j).isKey then - colnames &= "," & columns(j).ColName - coldata &= "," & FormatFormValue(j) - end if - next - for j=0 to Tables(tabidx).altFields.Count-1 - colnames &= "," & Tables(tabidx).altFields(j).ColName - coldata &= "," & Tables(tabidx).altFields(j).Data - next - Try - Dim command = Me.dbConnection.CreateCommand() - sqltext="insert into " & Tables(tabidx).TblName & " (" & mid(colnames,2) & ") values (" & mid(coldata,2) & ")" - command.CommandText = sqltext - cnt=command.ExecuteNonQuery() - Catch ex As Exception - errmsg="UpdateAltTableRecords Insert
ERROR: " & server.HTMLencode(ex.Message.ToString()) - if LogSqlOnError then errmsg &= "
" & sqltext - UpdateAltTableRecords = errmsg - End Try - - else - - ' update record - - sqltext="" - for j=0 to columns.Count-1 - if columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType) then - sqltext &= "," & columns(j).ColName & "=" & FormatFormValue(j) - end if - next - for j=0 to Tables(tabidx).altFields.Count-1 - sqltext &= "," & Tables(tabidx).altFields(j).ColName & "=" & Tables(tabidx).altFields(j).Data - next - if sqltext <> "" then - Try - Dim command = Me.dbConnection.CreateCommand() - sqltext="update " & Tables(tabidx).TblName & " set " & mid(sqltext,2) & whereClause - command.CommandText = sqltext - cnt=command.ExecuteNonQuery() - Catch ex As Exception - errmsg="UpdateAltTableRecords Update
ERROR: " & server.HTMLencode(ex.Message.ToString()) - if LogSqlOnError then errmsg &= "
" & sqltext - UpdateAltTableRecords = errmsg - End Try - end if - end if -end function - - -' ------------------------------------- -' form main sql query to populate the grid -' ------------------------------------- -Protected Sub FormSqlQuery() - Dim oParseLookup=new sqlParse - Dim oParseSubQry=new sqlParse - Dim i as Integer - Dim j as Integer - Dim s as String - Dim tabidx as Integer - Dim csvPrimaryKey as String - if debug then DebugString &= "

FormSqlQuery" - oParseMain.FromClause=Tables(MainTbl).TblName & " t" - for i=0 to Tables.Count-1 - if i<>MainTbl then - s="left join " & Tables(i).TblName & " " & Tables(i).TblAlias & " ON " & AltTableJoinClause(i) - oParseMain.AddJoin(s) - end if - next - oParseMain.AddWhereCondition(TableFilter) - - ' build sql for each column - - for i=0 to columns.Count-1 - if columns(i).TableIdx>=0 then tabidx=columns(i).TableIdx - if columns(i).FilterFlag then - ' add any column filters to where clause - oParseMain.AddWhereCondition(Tables(tabidx).TblAlias & "." & columns(i).ColName & "='" & columns(i).ColData & "'") - end if - - if not IsNothing(columns(i).Formula) then - - ' computed column - - oParseMain.AddColumn("(" & columns(i).Formula & ")", columns(i).Heading) - - elseif tabidx=MainTbl then - - ' column from main table - avoid subqueries to make it compatible with MS Access & MySQL < v4.1 - - if columns(i).isKey then - if not IsNothing(csvPrimaryKey) then csvPrimaryKey &= "," - csvPrimaryKey &= Tables(tabidx).TblAlias & "." & columns(i).ColName - end if - if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then - Dim TblAlias as String="t" & CStr(i) - s=replace(columns(i).SelectSql,"%alias%",TblAlias & ".") - oParseLookup.ParseSelect(s) - if oParseLookup.SelectList.count=2 then - Dim codeField as String=oParseLookup.SelectList(0).sql - Dim descField as String=oParseLookup.SelectList(1).sql - If IsFieldName(descField) Then - descField=TblAlias & "." & descField - Else - descField=replace(replace(descField,"%alias%",TblAlias & "."),"%aliasmain%","t.") - End If - s="left join " & oParseLookup.FromClause & " " & TblAlias & " on t." & columns(i).ColName & "=" & TblAlias & "." & replace(replace(codeField,"%alias%",""),"%aliasmain%","") - if not IsNothing(oParseLookup.WhereClause) then s &= " and " & replace(oParseLookup.WhereClause,"%alias%",TblAlias & ".") - oParseMain.AddJoin(s) - oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName) - if IsNothing(columns(i).DescriptionCol) then - columns(i+1).Formula=descField - end if - - else - Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")") - end if - else - oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading) - end if - - else - - ' column from alt table - no avoiding subqueries here - - if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then - oParseLookup.ParseSelect(columns(i).SelectSql) - if oParseLookup.SelectList.count=2 then - Dim descQuery as String="select " & oParseLookup.SelectList(1).sql & " from " & oParseLookup.FromClause & " where " & _ - oParseLookup.SelectList(0).sql & "=" & Tables(tabidx).TblAlias & "." & columns(i).ColName - if not IsNothing(oParseLookup.WhereClause) then descQuery=descQuery & " and " & oParseLookup.WhereClause - oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName) - columns(i+1).Formula="(" & descQuery & ")" - else - Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")") - end if - else - oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading) - end if - - end if - - if not IsNothing(columns(i).EntryType) then - Dim SessionColId as String = ExtFieldId(i) - if InStr("CSNR",left(columns(i).EntryType,1)) > 0 then - if not IsNothing(columns(i).SelectSql) then - s=columns(i).SelectSql - if not IsNothing(columns(i).SelectFilter) then - oParseLookup.ParseSelect(s) - oParseLookup.AddWhereCondition(columns(i).SelectFilter) - s=oParseLookup.UnparseSelect - end if - oParseMain.SelectList(i).LookupQuery=replace(replace(s,"%alias%",""),"%aliasmain%","") - else - oParseMain.SelectList(i).LookupQuery="select distinct " & columns(i).ColName & " from " & Tables(tabidx).TblName & " where " & columns(i).ColName & " is not null" - end if - end if - end if - next - if not IsNothing(DefaultSort) then - oParseMain.AddSort(DefaultSort) - elseif not IsNothing(csvPrimaryKey) then - oParseMain.AddSort(csvPrimaryKey) - end if -End Sub - - -Protected Overrides Sub OnPreRender(ByVal e As System.EventArgs) - MyBase.OnPreRender(e) - if not IsNothing(dbConnection) then - oSqlCompat=New sqlCompatibilty(dbDialect) - end if - - ' Create headings - Dim i as Integer - for i=0 to columns.Count-1 - if not IsNothing(columns(i).AltTable) then - columns(i).TableIdx=TabIndex(columns(i).AltTable) - else - columns(i).TableIdx=MainTbl - end if - Dim cell as New TableHeaderCell() - cell.Text=columns(i).Heading - if i 0 then - Me.GetColumnInfo() - if _action="table" and sessions then - Me.FormSqlQuery() - session.contents(Me.UniqueId)=oParseMain - session.contents(Me.UniqueId & ".filters")=Me._sqlFilters - elseif _action="query" then - Me.FormSqlQuery() - end if - end if - - ' populate globalInitScript - Dim FixedGridScript as String = "" - Dim VarGridScript as String = "" - Dim gblFormView as Boolean = false - If Not Page.IsStartupScriptRegistered("LiveGridInit") Then - For Each ctrl As Control In Page.Controls - If TypeOf(ctrl) is LiveGrid then - if CType(ctrl,LiveGrid).Rows >= 0 then - FixedGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf - else - VarGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf - end if - if CType(ctrl,LiveGrid).formView then gblFormView=true - End If - Next - 'globalInitScript = "Rico.acceptLanguage('" & Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") & "');" & vbCrLf - if not UsingMinRico then - globalInitScript &= "Rico.loadModule('LiveGridAjax','LiveGridMenu');" & vbCrLf - if gblFormView then globalInitScript &= "Rico.loadModule('LiveGridForms');" & vbCrLf - end if - globalInitScript &= "Rico.onLoad( function() {" & vbCrLf - globalInitScript &= FixedGridScript ' initialize grids with fixed # of rows first - globalInitScript &= VarGridScript & "});" & vbCrLf ' then initialize grids with variable # of rows - Page.RegisterStartupScript("LiveGridInit", "") - End If -End Sub - - -Public Class GridContainer - Inherits Control - Implements INamingContainer -End Class - -End Class diff --git a/plugins/dotnet/SimpleGrid.ascx b/plugins/dotnet/SimpleGrid.ascx deleted file mode 100644 index f913d14..0000000 --- a/plugins/dotnet/SimpleGrid.ascx +++ /dev/null @@ -1,5 +0,0 @@ -<%@ Control Language="VB" - CodeFile="SimpleGrid.ascx.vb" - Inherits="SimpleGrid" Debug="true" %> -<%@ Register TagPrefix="Rico" TagName="Column" Src="GridColumn.ascx" %> - diff --git a/plugins/dotnet/SimpleGrid.ascx.vb b/plugins/dotnet/SimpleGrid.ascx.vb deleted file mode 100644 index e3d5eb4..0000000 --- a/plugins/dotnet/SimpleGrid.ascx.vb +++ /dev/null @@ -1,368 +0,0 @@ -Imports System.Data - -Partial Class SimpleGrid -Inherits System.Web.UI.UserControl - -Private _gridHeading As ITemplate = Nothing -Protected HdgContainer As New GridContainer() - -Public columns as New ArrayList() -Public gridVar as String -Public optionsVar as String ' name of grid options js var -Public FilterLocation as Integer = -2 -Public FilterAllToken as String -Public FilterBoxMaxLen as Integer = -1 -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? -Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels -Public allowColResize as Boolean = True -Public menuEvent as String -public rows as New ArrayList() -public FrozenCols as Integer -private LastRow,LastHeadingRow,ResizeRowIdx - - - -Sub Page_Init() - gridVar=Me.UniqueId & "['grid']" - optionsVar=Me.UniqueId & "['options']" - - If Not (_gridHeading Is Nothing) Then - _gridHeading.InstantiateIn(HdgContainer) - For Each ctrl As Control In HdgContainer.Controls - If TypeOf(ctrl) is GridColumn then - columns.Add(ctrl) - end if - Next - End If -End Sub - - -Public Class HeadingCellClass - Public content As String, span As Integer - - Public Sub New(Optional contentParm As String = "", Optional spanParm As Integer = 1) - content=contentParm - span=spanParm - End Sub -End Class - -class SimpleGridCell - public content as String - private attr As New Hashtable() - - Public Function HeadingCell() as object - Dim s as String, span as Integer - s="

" & DataCell("") & "
" - dim result() as object = {content,span} - HeadingCell = result - End Function - - Public Function DataCell(rowclass as String) as String - dim s as String, k as String - s = ""colspan" Then s=s & " " & k & "='" & attr(k) & "'" - next - s=s & ">" & content & "" - DataCell=s - End Function - - Public Function HtmlCell() - dim s as String="", k as String - for each k in attr.keys - s &= " " & k & "='" & attr(k) & "'" - next - HtmlCell="" & content & "" - End Function - - Public Sub SetAttr(name as String, value as String) - attr(name)=value - End Sub -End class - - -class SimpleGridRow - public cells as New ArrayList() - private attr As New Hashtable() - private CurrentCell as SimpleGridCell - - Public Sub AddCell(ByVal content as String) - CurrentCell=new SimpleGridCell() - cells.Add(CurrentCell) - CurrentCell.content=content - End Sub - - Public Function HeadingRow(ByVal c1 as Integer, ByVal c2 as Integer) as String - dim s as String, a - dim cellidx as Integer=0 - dim colidx as Integer=0 - while colidx < c1 and cellidx < cells.count - a=cells(cellidx).HeadingCell() - colidx+=CInt(a(1)) - cellidx+=1 - end while - while (colidx <= c2 or c2=-1) and cellidx < cells.count - a=cells(cellidx).HeadingCell() - s &= a(0) - colidx+=CInt(a(1)) - cellidx+=1 - end while - HeadingRow = s - End Function - - Public Function HeadingClass() - HeadingClass=trim("ricoLG_hdg " & attr("class")) - End Function - - Public Function CellCount() - CellCount=cells.count - End Function - - Public Function GetRowAttr(ByVal name) - GetRowAttr=attr(name) - End Function - - Public Sub SetRowAttr(ByVal name, ByVal value) - attr(name)=value - End Sub - - Public Sub SetCellAttr(ByVal name, ByVal value) - CurrentCell.SetAttr(name,value) - End Sub -end class - - -Public Function AddHeadingRow(ResizeRowFlag as Boolean) - LastHeadingRow=AddDataRow() - if ResizeRowFlag then ResizeRowIdx=LastHeadingRow - AddHeadingRow=LastHeadingRow -End Function - -Public Function AddDataRow() - rows.Add(new SimpleGridRow()) - LastRow=rows.count-1 - AddDataRow=LastRow -End Function - -Public Function HeadingRowCount() - if IsNothing(LastHeadingRow) then - HeadingRowCount=0 - else - HeadingRowCount=LastHeadingRow+1 - end if -End Function - -Public Function DataRowCount() - if IsNothing(LastRow) then - DataRowCount=0 - else - DataRowCount=LastRow+1-HeadingRowCount() - end if -End Function - -' returns # of cells in the current row -Public Function CellCount() - CellCount=rows(LastRow).CellCount -End Function - -Public Sub AddCell(ByVal content as String) - rows(LastRow).AddCell(content) -End Sub - -Public Sub AddCellToRow(ByVal RowIdx as Integer, ByVal content as String) - LastRow=RowIdx - AddCell(content) -End Sub - -Public Sub SetRowAttr(ByVal name as String, ByVal value as String) - rows(LastRow).SetRowAttr(name,value) -End Sub - -Public Sub SetCellAttr(ByVal name as String, ByVal value as String) - rows(LastRow).SetCellAttr(name,value) -End Sub - -Private Sub RenderColumns(writer as HTMLTextWriter, c1 as Integer, c2 as Integer) - dim r as Integer, c as Integer - for c=c1 to c2 - writer.Write("
") - for r=LastHeadingRow+1 to rows.count-1 - writer.Write(rows(r).cells(c).DataCell(rows(r).GetRowAttr("class"))) - next - writer.WriteLine("
") - next -End Sub - - _ -Public Property GridColumns() As ITemplate - Get - Return _gridHeading - End Get - Set - _gridHeading = value - End Set -End Property - -' returns true if there is a valid heading -Private Sub CheckHeading() - if not IsNothing(ResizeRowIdx) then exit sub - rows.Insert(0, new SimpleGridRow()) - LastHeadingRow=0 - ResizeRowIdx=0 - Dim c as Integer - for c=0 to columns.count-1 - rows(0).AddCell( CType(columns(c),GridColumn).heading ) - next -End Sub - -Private function FmtBool(b) - if b then FmtBool="true" else FmtBool="false" -end function - -Protected Overrides Sub Render(writer as HTMLTextWriter) - dim colcnt as Integer, r as Integer, c as Integer - CheckHeading - colcnt=rows(ResizeRowIdx).CellCount - - writer.WriteLine("") - - writer.Write("
") - - - '------------------- - ' scrolling columns - '------------------- - - ' upper right - writer.Write("") - - ' lower right - writer.Write("") - - writer.WriteLine("
") - - '------------------- - ' frozen columns - '------------------- - - ' upper left - writer.WriteLine("") - for r=0 to LastHeadingRow - writer.Write("") - if FrozenCols > 0 then writer.Write(rows(r).HeadingRow(0,FrozenCols-1)) - writer.Write("") - next - writer.WriteLine("
") - - writer.WriteLine("
") - - ' lower left - writer.Write("") - writer.WriteLine("") - if FrozenCols > 0 then RenderColumns(writer,0,FrozenCols-1) - writer.Write("") - writer.WriteLine("
") - - writer.WriteLine("
") - 'writer.Write("
") - writer.WriteLine("") - for r=0 to LastHeadingRow - writer.Write("") - writer.Write(rows(r).HeadingRow(FrozenCols,-1)) - writer.Write("") - next - writer.Write("
") - 'writer.Write("
") - writer.WriteLine("
") - writer.Write("") - writer.WriteLine("") - RenderColumns(writer,FrozenCols,colcnt-1) - writer.Write("") - writer.Write("
") - writer.Write("
") -End Sub - -' Response.Buffer must be true -Public Sub RenderExcel(fileName) - Dim r as Integer, c as Integer - Dim sw As New System.IO.StringWriter - - HttpContext.Current.Response.Clear() - if fileName<>"" then HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & fileName) - HttpContext.Current.Response.ContentType = "application/ms-excel" - - CheckHeading - sw.WriteLine("") - for r=0 to rows.count-1 - sw.WriteLine("") - for c=0 to rows(r).CellCount()-1 - sw.Write(rows(r).cells(c).HtmlCell()) - next - sw.WriteLine("") - next - sw.WriteLine("
") - HttpContext.Current.Response.Write(sw.ToString) - HttpContext.Current.Response.End() -End Sub - -' Response.Buffer must be true -Public Sub RenderDelimited(fileName,delim,SubstituteChar) - Dim r as Integer, c as Integer - Dim sw As New System.IO.StringWriter - - HttpContext.Current.Response.Clear() - if fileName<>"" then HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & fileName) - HttpContext.Current.Response.ContentType = "text/csv" - - CheckHeading - for r=0 to rows.count-1 - for c=0 to rows(r).CellCount()-1 - if c > 0 then sw.Write(delim) - sw.Write(replace(rows(r).cells(c).content,delim,SubstituteChar)) - next - sw.WriteLine("") - next - HttpContext.Current.Response.Write(sw.ToString) - HttpContext.Current.Response.End() -End Sub - -Public Class GridContainer - Inherits Control - Implements INamingContainer -End Class - -End Class diff --git a/plugins/dotnet/ricoResponse.ascx b/plugins/dotnet/ricoResponse.ascx deleted file mode 100644 index dc4e965..0000000 --- a/plugins/dotnet/ricoResponse.ascx +++ /dev/null @@ -1,612 +0,0 @@ -<%@ Control Language="vb" debug="true" ClassName="ricoResponse" %> -<%@ Register TagPrefix="Rico" TagName="sqlParse" Src="sqlParse.ascx" %> -<%@ Import Namespace="System.Data" %> - diff --git a/plugins/dotnet/sqlCompatibilty.ascx b/plugins/dotnet/sqlCompatibilty.ascx deleted file mode 100644 index 8e34e52..0000000 --- a/plugins/dotnet/sqlCompatibilty.ascx +++ /dev/null @@ -1,3 +0,0 @@ -<%@ Control Language="VB" - CodeFile="sqlCompatibilty.ascx.vb" - Inherits="sqlCompatibilty" %> diff --git a/plugins/dotnet/sqlCompatibilty.ascx.vb b/plugins/dotnet/sqlCompatibilty.ascx.vb deleted file mode 100644 index 5d8ba46..0000000 --- a/plugins/dotnet/sqlCompatibilty.ascx.vb +++ /dev/null @@ -1,104 +0,0 @@ -Partial Class sqlCompatibilty -Inherits System.Web.UI.UserControl - -' --------------------------------------------------------------------------- -' Functions to allow cross-db compatibility within Rico -' -' SQL dialect possible values: Access, Oracle, TSQL (MS SQL Server) -' --------------------------------------------------------------------------- - -Protected Dialect as String - -Sub New(dbDialect as String) - Dialect=dbDialect -End Sub - -Sub New() -End Sub - -Sub SetDialectFromProvider(Provider as String) - Provider=ucase(Provider) - if Provider="SQLOLEDB" then - Dialect="TSQL" - elseif InStr(Provider,"ORACLE")>0 then - Dialect="Oracle" - elseif InStr(Provider,"JET")>0 then - Dialect="Access" - else - Throw New Exception("Unknown ADO provider") - end if -End Sub - -Public function CurrentTime() as String - select case Dialect - case "TSQL","DB2": CurrentTime="CURRENT_TIMESTAMP" - case "Access": CurrentTime="Now()" - case else: CurrentTime="LOCALTIMESTAMP" - end select -end function - -Public function Convert2Char(s as String) as String - select case Dialect - case "TSQL" : Convert2Char="cast(" & s & " as varchar)" - case "Access": Convert2Char="CStr(" & s & ")" - case "DB2" : Convert2Char="CHAR(" & s & ")" - case "Oracle": Convert2Char="cast(" & s & " as varchar2(20))" - case else: Convert2Char=s ' implicit conversion (MySQL) - end select -end function - -Public function Wildcard() as String - Wildcard="%" -end function - -Public function SqlDay(s as String) as String - select case Dialect - case "Oracle": SqlDay="to_char(" & s & ",'DD')" - case "MySQL": SqlDay="dayofmonth(" & s & ")" - case else: SqlDay="day(" & s & ")" - end select -end function - -Public function SqlMonth(s as String) as String - select case Dialect - case "Oracle": SqlMonth="to_char(" & s & ",'MM')" - case else: SqlMonth="month(" & s & ")" - end select -end function - -Public function SqlYear(s as String) as String - select case Dialect - case "Oracle": SqlYear="to_char(" & s & ",'YYYY')" - case else: SqlYear="year(" & s & ")" - end select -end function - -Public function addQuotes(s as String) as String - select case Dialect - case "Access": - if IsDate(s) then - addQuotes="#" & s & "#" - else - addQuotes="""" & replace(s,"""","""""") & """" - end if - case "MySQL": addQuotes="'" & replace(replace(s,"\","\\"),"'","\'") & "'" - case else: addQuotes="'" & replace(s,"'","''") & "'" - end select -end function - -Public function Concat(arStrings() as String, addQuotes as Boolean) as String - dim i as Integer - if addQuotes then - For Each i in arStrings - 'arStrings(i)=addQuotes(arStrings(i)) - next - end if - select case Dialect - case "TSQL": Concat=join(arStrings,"+") - case "Access": Concat=join(arStrings," & ") - case "MySQL": Concat="concat(" & join(arStrings,",") & ")" - case else: Concat=join(arStrings," || ") - end select -end function - -End Class diff --git a/plugins/dotnet/sqlParse.ascx b/plugins/dotnet/sqlParse.ascx deleted file mode 100644 index 20e6a77..0000000 --- a/plugins/dotnet/sqlParse.ascx +++ /dev/null @@ -1,5 +0,0 @@ -<%@ Control Language="VB" - CodeFile="sqlParse.ascx.vb" - Inherits="sqlParse" %> -<%@ Import Namespace="System.Data" %> -<%@ Import Namespace="System.Data.OleDb" %> diff --git a/plugins/dotnet/sqlParse.ascx.vb b/plugins/dotnet/sqlParse.ascx.vb deleted file mode 100644 index 819eeb6..0000000 --- a/plugins/dotnet/sqlParse.ascx.vb +++ /dev/null @@ -1,393 +0,0 @@ -Partial Class sqlParse -Inherits System.Web.UI.UserControl -Implements ICloneable - -Public Class sqlColumn - Public sql As String, name As String - Public LookupQuery As String ' query to populate column - - Public Sub New(Optional sqlParm As String = "", Optional nameParm As String = "") - if sqlParm<>"" then sql=sqlParm - if nameParm<>"" then name=nameParm - End Sub - - Public function Unparse() - dim s As String=sql - if not IsNothing(name) then - s &= " AS " & name - end if - Unparse=s - end Function -End Class - - - -'******************************************************************************************************** -' Parse SQL a statement -'******************************************************************************************************** - -Public IsDistinct As Boolean -Public SelectList As New ArrayList() -Public GroupBy As New ArrayList() -Public OrderBy As New ArrayList() -Public FromClause As String, WhereClause As String, HavingClause As String -Public Headings As New ArrayList() ' set after an any unparse* call - -Public Function Clone As Object Implements ICloneable.Clone - Dim NewObj as object = Me.MemberwiseClone, item as String - ' shallow copy of OrderBy is insufficient because it may be modified by ricoResponse.ascx - NewObj.OrderBy = New ArrayList() - for each item in Me.OrderBy - NewObj.OrderBy.Add(item) - next - Return NewObj -End Function - -' ------------------------------------------------------------- -' Rebuilds a SQL select statement that was parsed by ParseSelect -' ------------------------------------------------------------- -Private Function Unparse(arSkipCols) As String - dim sqltext As String = "SELECT " - if IsDistinct then sqltext &= "DISTINCT " - sqltext &= UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause - if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause - if GroupBy.count > 0 then sqltext &= " GROUP BY " & join(GroupBy.ToArray(),",") - if not IsNothing(HavingClause) then sqltext &= " HAVING " & HavingClause - if OrderBy.count > 0 then sqltext &= " ORDER BY " & join(OrderBy.ToArray(),",") - Unparse=sqltext -end Function - - -Public Function UnparseSelect() As String - dim arSkipCols(-1) as string - UnparseSelect=Unparse(arSkipCols) -end Function - - -Public function UnparseSelectSkip(arSkipCols) - UnparseSelectSkip=Unparse(arSkipCols) -end Function - - -Public Function UnparseSelectDistinct() As String - dim arSkipCols(-1) as string - IsDistinct=true - UnparseSelectDistinct=Unparse(arSkipCols) -end Function - - -' MS Access does not support ordering by column name -Public Function UnparseDistinctColumnAccess(colnum as integer) As String - dim sqltext As String - sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " FROM " & FromClause - if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause - Headings.Clear() - Headings.Add(SelectList(colnum).name) - UnparseDistinctColumnAccess=sqltext & " ORDER BY " & SelectList(colnum).sql -end Function - - -Public Function UnparseDistinctColumn(colnum as integer) As String - dim sqltext As String - sqltext="SELECT DISTINCT " & SelectList(colnum).sql & " as col1 FROM " & FromClause - if not IsNothing(WhereClause) then sqltext &= " WHERE " & WhereClause - Headings.Clear() - Headings.Add(SelectList(colnum).name) - UnparseDistinctColumn=sqltext & " ORDER BY col1" -end Function - - -Public function UnparseColumnList() As String - dim strSelectList As New ArrayList(), i as integer, sql as String - Headings.Clear() - for i=0 to SelectList.count-1 - strSelectList.Add(SelectList(i).sql & " AS rico_col" & i) - Headings.Add(SelectList(i).name) - next - UnparseColumnList=join(strSelectList.ToArray(),",") -end Function - - -Public function UnparseColumnListSkip(arSkipCols() as String) As String - dim strSelectList As New ArrayList(), i as integer - dim SkipIdx as integer=0, skip as boolean - Headings.Clear() - for i=0 to SelectList.count-1 - skip=false - if SkipIdx < arSkipCols.Length then - skip=CBool(arSkipCols(SkipIdx)=CStr(i)) - if skip then SkipIdx+=1 - end if - if not skip then - strSelectList.Add(SelectList(i).sql & " AS rico_col" & i) - Headings.Add(SelectList(i).name) - end if - next - UnparseColumnListSkip=join(strSelectList.ToArray(),",") -end Function - - -' returns a "windowed" select query -' includeAS should be true for SQL Server 2005+ and false for Oracle -Public function UnparseWithRowNumber(offset as Integer, numrows as Integer, includeAS as Boolean, arSkipCols() as String) as String - dim unparseText as String - if OrderBy.count = 0 then Throw New Exception("an OrderBy clause is required") - unparseText="SELECT ROW_NUMBER() OVER (ORDER BY " & join(OrderBy.ToArray(),",") & ") AS rico_rownum," & UnparseColumnListSkip(arSkipCols) & " FROM " & FromClause - if not IsNothing(WhereClause) then unparseText &= " WHERE " & WhereClause - if GroupBy.count > 0 then unparseText &= " GROUP BY " & join(GroupBy.ToArray(),",") - if not IsNothing(HavingClause) then unparseText &= " HAVING " & HavingClause - unparseText="SELECT * FROM (" & unparseText & ")" - if includeAS then unparseText &= " AS rico_Main" - unparseText &= " WHERE rico_rownum > " & offset & " AND rico_rownum <= " & CStr(offset+numrows) - UnparseWithRowNumber=unparseText -end Function - - -Public sub Init() - SelectList.Clear() - GroupBy.Clear() - OrderBy.Clear() - FromClause=Nothing - WhereClause=Nothing - HavingClause=Nothing - IsDistinct=false -end sub - - -' ------------------------------------------------------------- -' Parse a SQL select statement into its major components -' Does not handle: -' 1) union queries -' 2) select into -' 3) more than one space between "group" and "by", or "order" and "by" -' 4) stored procedures -' ------------------------------------------------------------- -Public function ParseSelect(ByVal sqltext as String) As Boolean - dim i As Integer, j As Integer, l As Integer, idx As Integer, parencnt As Integer - dim clause As String, ch As String, curfield As String, nexttoken As String, inquote As Boolean, endquote As String - Init() - ParseSelect=false - sqltext=replace(sqltext,vbLf," ") - sqltext=" " & replace(sqltext,vbCr," ") & " SELECT " ' SELECT suffix forces last curfield to be saved - 'response.write "

ParseSelect: " & sqltext & "

" - l=len(sqltext) - parencnt=0 - inquote=false - i=1 - curfield="" - while i 0 then - curfield &= ch - elseif ch="," then - 'response.write "

" & clause & ": " & server.htmlencode(curfield) & "

" - select case clause - case "SELECT": - AddColumn(curfield) - curfield="" - case "AS": - SelectList(SelectList.count-1).name=curfield - curfield="" - clause="SELECT" - case "GROUP BY": ArrayPush(GroupBy,curfield) - case "ORDER BY": ArrayPush(OrderBy,curfield) - case else: curfield &= ch - end select - elseif ch=" " then - j=InStr(i+1,sqltext," ") - if j<1 then - curfield &= ch - else - if ucase(mid(sqltext,j+1,3))="BY " then j=j+3 - nexttoken=ucase(mid(sqltext,i+1,j-i-1)) - 'wscript.echo "'" & nexttoken & "'" - 'response.write "

" & clause & " : " & nexttoken & " : " & server.htmlencode(curfield) & "

" - select case nexttoken - case "SELECT","INTO","FROM","WHERE","GROUP BY","HAVING","ORDER BY": - select case clause - case "SELECT": - AddColumn(curfield) - curfield="" - case "AS": - SelectList(SelectList.count-1).name=curfield - curfield="" - case "FROM": SetParseField(FromClause,curfield) - case "WHERE": SetParseField(WhereClause,curfield) - case "GROUP BY": ArrayPush(GroupBy,curfield) - case "HAVING": SetParseField(HavingClause,curfield) - case "ORDER BY": ArrayPush(OrderBy,curfield) - end select - clause=nexttoken - i=j-1 - - case "AS": - if clause="SELECT" then - AddColumn(curfield) - curfield="" - clause=nexttoken - i=j - elseif curfield<>"" then - curfield &= ch - end if - - case "DISTINCT": - if clause="SELECT" then - IsDistinct=true - curfield="" - i=j - elseif curfield<>"" then - curfield &= ch - end if - - case else: if curfield<>"" then curfield &= ch - end select - end if - else - curfield &= ch - end if - i=i+1 - end while - ParseSelect=true -end function - - -Private Sub ArrayPush(s as ArrayList, ByRef newvalue as string) - s.add(newvalue) - newvalue="" -end sub - -Private Sub SetParseField(ByRef f as string, ByRef newvalue as string) - f=newvalue - newvalue="" -end sub - - -Public Sub AddColumn(sqlParm as String, Optional nameParm As String = "") - SelectList.add(new sqlColumn(sqlParm,nameParm)) -End Sub - - -' ------------------------------------------------------------- -' Add a join to the from clause -' ------------------------------------------------------------- -Public Sub AddJoin(ByVal JoinClause As String) - if InStr(FromClause," join ")>0 then FromClause="(" & FromClause & ")" ' required by Access - FromClause=FromClause & " " & JoinClause -end sub - -Private Sub SplitSortSpec(ByVal sortspec As String, ByRef sortcol As String, ByRef sortdir As String) - sortspec=ucase(sortspec) - if right(sortspec,3)="ASC" then - sortcol=trim(left(sortspec,len(sortspec)-3)) - sortdir="ASC" - elseif right(sortspec,4)="DESC" then - sortcol=trim(left(sortspec,len(sortspec)-4)) - sortdir="DESC" - else - sortcol=trim(sortspec) - sortdir="" - end if -End Sub - -Private Function FindSortColumn(ByVal sortspec As String) As Integer - dim i As Integer, findcol As String, finddir As String, sortcol As String, sortdir As String - FindSortColumn=-1 - SplitSortSpec(sortspec, findcol, finddir) - for i=0 to OrderBy.count-1 - SplitSortSpec(OrderBy(i), sortcol, sortdir) - if sortcol=findcol then - FindSortColumn=i - exit for - end if - next -End Function - -' ------------------------------------------------------------- -' Add sort criteria to the beginning of the order by clause -' ------------------------------------------------------------- -Public Sub AddSort(ByVal NewSort As String) - dim i As Integer, colidx As Integer - colidx=FindSortColumn(NewSort) - if colidx>=0 then - for i=colidx to 1 step -1 - OrderBy(i)=OrderBy(i-1) - next - OrderBy(0)=NewSort - else - OrderBy.insert(0,NewSort) - end if -end sub - -' ------------------------------------------------------------- -' Append sort criteria to the order by clause -' ------------------------------------------------------------- -Public Sub AppendSort(ByVal NewSort As String) - OrderBy.add(NewSort) -end sub - -' ------------------------------------------------------------- -' Add a condition to the where clause -' ------------------------------------------------------------- -Public Sub AddWhereCondition(ByVal NewCondition) - AddCondition(WhereClause,NewCondition) -end sub - -' ------------------------------------------------------------- -' Add a condition to the having clause -' ------------------------------------------------------------- -Public Sub AddHavingCondition(ByVal NewCondition) - AddCondition(HavingClause,NewCondition) -end sub - -Private Sub AddCondition(ByRef Clause, ByVal NewCondition) - if IsNothing(NewCondition) then exit sub - If IsNothing(Clause) Then - Clause="(" & NewCondition & ")" - Else - Clause &= " AND (" & NewCondition & ")" - End If -End Sub - -Public Sub DebugPrint(writer as object) - dim i as integer - writer.write("

Parse Result:") - writer.write("") - if IsDistinct then writer.write("
DISTINCT ") - writer.write("
COLUMNS:
    ") - for i=0 to SelectList.count-1 - writer.write("
  1. " & SelectList(i).Unparse) - next - writer.write("
FROM:" & FromClause) - if not IsNothing(WhereClause) then writer.write("
WHERE:" & WhereClause) - if GroupBy.count > 0 then writer.write("
GROUP BY:" & join(GroupBy.ToArray(),"
")) - if not IsNothing(HavingClause) then writer.write("
HAVING:" & HavingClause) - if OrderBy.count > 0 then writer.write("
ORDER BY:" & join(OrderBy.ToArray(),"
")) - writer.write("
") -End Sub - -End Class