3 Partial Class LiveGrid
\r
4 Inherits System.Web.UI.UserControl
\r
6 ' ----------------------------------------------------
\r
8 ' ----------------------------------------------------
\r
10 Public Const sizeToWindow=-1
\r
11 Public Const sizeToData=-2
\r
12 Public Const sizeToBody=-3
\r
13 Public Const sizeToParent=-4
\r
15 ' ----------------------------------------------------
\r
16 ' Private Properties
\r
17 ' ----------------------------------------------------
\r
19 Private _rows As Integer = sizeToWindow
\r
20 Private _sqlFilters as New ArrayList()
\r
21 Private _gridHeading As ITemplate = Nothing
\r
22 Private _headingTop As ITemplate = Nothing
\r
23 Private _headingBottom As ITemplate = Nothing
\r
24 Protected globalInitScript as String = ""
\r
25 Protected HdgContainer As New GridContainer()
\r
26 Protected DebugString As String
\r
27 Protected oSqlCompat as sqlCompatibilty
\r
28 Protected oParseMain as New sqlParse()
\r
31 ' ----------------------------------------------------
\r
33 ' ----------------------------------------------------
\r
34 Public columns as New ArrayList()
\r
35 Public dataProvider as String = "ricoQuery.aspx"
\r
36 Public menuEvent as String = "dblclick"
\r
37 Public frozenColumns as Integer = 0
\r
38 Public canSortDefault as Boolean = True
\r
39 Public canHideDefault as Boolean = True
\r
40 Public canFilterDefault as Boolean = True
\r
41 Public allowColResize as Boolean = True
\r
42 Public highlightElem as String = "menuRow"
\r
43 Public highlightMethod as String
\r
44 Public prefetchBuffer as Boolean = True
\r
45 Public DisplayTimer as Boolean = True
\r
46 Public DisplayBookmark as Boolean = True
\r
47 Public Caption as String
\r
48 Public click as String
\r
49 Public dblclick as String
\r
50 Public contextmenu as String
\r
51 Public headingSort as String
\r
52 Public beforeInit as String
\r
53 Public afterInit as String
\r
54 Public TableFilter as String
\r
55 Public FilterLocation as Integer = -2
\r
56 Public FilterAllToken as String
\r
57 Public FilterBoxMaxLen as Integer = -1
\r
58 Public FilterAnchorLeft as Boolean = false ' when matching text box values, should they match beginning of string (true) or anywhere in string (false)?
59 Public requestParameters as New Hashtable()
\r
60 Public saveColumnWidth as Boolean = True
\r
61 Public saveColumnFilter as Boolean = False
\r
62 Public saveColumnSort as Boolean = False
\r
63 Public cookieDays as Integer
\r
64 Public DefaultSort as String
\r
65 Public BufferType as String = "AjaxSQL" ' can be overridden to AjaxXML
\r
66 Public maxPrint as Integer = -1
\r
67 Public dndMgrIdx as Integer = -1
\r
68 Public fmt as String = "xml"
\r
69 Public UsingMinRico as Boolean = False ' using minified version of Rico?
\r
70 Public sessions as Boolean = True
\r
71 Public minPageRows as Integer = -1
\r
72 Public maxPageRows as Integer = -1
\r
73 Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels
\r
74 Public debug as Boolean = False
\r
75 Public LogSqlOnError as Boolean = false ' include sql statement in results if an error occurs (true/false)
\r
79 Public Property rows() As Integer
\r
83 Set(ByVal Value As Integer)
\r
88 <TemplateContainer(GetType(GridContainer))> _
\r
89 Public Property GridColumns() As ITemplate
\r
94 _gridHeading = value
\r
98 <TemplateContainer(GetType(GridContainer))> _
\r
99 Public Property HeadingTop() As ITemplate
\r
104 _headingTop = value
\r
108 <TemplateContainer(GetType(GridContainer))> _
\r
109 Public Property HeadingBottom() As ITemplate
\r
111 Return _headingBottom
\r
114 _headingBottom = value
\r
118 Public WriteOnly Property sqlQuery() As String
\r
119 Set(ByVal SqlText As String)
\r
120 if oParseMain.ParseSelect(SqlText) then
\r
121 ' sync column headings
\r
123 for i=0 to oParseMain.SelectList.Count-1
\r
124 if i < Me.columns.Count then
\r
125 if IsNothing(Me.columns(i).Heading) then
\r
126 Me.columns(i).Heading=oParseMain.SelectList(i).name
\r
128 oParseMain.SelectList(i).name=Me.columns(i).Heading
\r
131 AddCalculatedField(oParseMain.SelectList(i).name, oParseMain.SelectList(i).sql)
\r
134 if sessions then session.contents(Me.UniqueId)=oParseMain
\r
136 Throw New Exception("Invalid SQL statement")
\r
141 Public ReadOnly Property ParseClone() As object
\r
143 return oParseMain.Clone()
\r
147 Protected ReadOnly Property TimerSpan() As String
\r
149 if Me.DisplayTimer then
\r
150 Return "<span id='" & Me.UniqueId & "_timer' class='ricoSessionTimer'> </span>"
\r
157 Protected ReadOnly Property BookmarkSpan() As String
\r
159 if Me.DisplayBookmark then
\r
160 Return "<span id='" & Me.UniqueId & "_bookmark'> </span>"
\r
167 Protected ReadOnly Property SaveMsgSpan() As String
\r
169 if Me.formView then
\r
170 Return "<span id='" & Me.UniqueId & "_savemsg' class='ricoSaveMsg'></span>"
\r
177 Protected ReadOnly Property CaptionSpan() As String
\r
179 if IsNothing(Caption) then
\r
182 Return "<span id='" & Me.UniqueId & "_caption' class='ricoCaption'>" & Me.Caption & "</span>"
\r
187 Protected ReadOnly Property FilterIcon() As String
\r
189 if FilterLocation >= -1 then
\r
190 Return "<a id='ex3_filterLink' href='#' style='margin-right:1em;'></a>"
\r
197 Protected ReadOnly Property Bookmark() As String
\r
199 if Me.DisplayBookmark or Me.DisplayTimer or not IsNothing(Caption) then
\r
200 Return "<p class='ricoBookmark'>" & Me.CaptionSpan & Me.TimerSpan & Me.FilterIcon & Me.BookmarkSpan & Me.SaveMsgSpan & "</p>"
\r
207 Private function FmtBool(b)
\r
208 if b then FmtBool="true" else FmtBool="false"
\r
211 Protected ReadOnly Property init_Script() As String
\r
213 Dim script as New System.Text.StringBuilder(), confirmCol as Integer=0
\r
214 script.Append("var " & Me.UniqueId & " = {};" & vbCrLf)
\r
215 script.Append("function " & Me.UniqueId & "_init" & "() {" & vbCrLf)
\r
216 if not IsNothing(beforeInit) then script.Append(beforeInit & vbCrLf)
\r
220 script.Append(" " & optionsVar & " = {" & vbCrLf)
\r
221 script.Append(" visibleRows: " & Me.rows & "," & vbCrLf)
\r
222 script.Append(" frozenColumns: " & frozenColumns & "," & vbCrLf)
\r
223 script.Append(" canSortDefault: " & FmtBool(canSortDefault) & "," & vbCrLf)
\r
224 script.Append(" canHideDefault: " & FmtBool(canHideDefault) & "," & vbCrLf)
\r
225 script.Append(" canFilterDefault: " & FmtBool(canFilterDefault) & "," & vbCrLf)
\r
226 script.Append(" allowColResize: " & FmtBool(allowColResize) & "," & vbCrLf)
\r
227 script.Append(" highlightElem: '" & highlightElem & "'," & vbCrLf)
\r
228 if not IsNothing(highlightMethod) then script.Append(" highlightMethod: '" & highlightMethod & "'," & vbCrLf)
\r
229 script.Append(" prefetchBuffer: " & FmtBool(prefetchBuffer) & "," & vbCrLf)
\r
230 script.Append(" menuEvent: '" & menuEvent & "'," & vbCrLf)
\r
231 if not IsNothing(RecordName) then script.Append(" RecordName: '" & RecordName & "'," & vbCrLf)
\r
232 script.Append(" saveColumnInfo: {width:" & FmtBool(saveColumnWidth) & ", filter:" & FmtBool(saveColumnFilter) & ", sort:" & FmtBool(saveColumnSort) & "}," & vbCrLf)
\r
233 if not IsNothing(cookieDays) then script.Append(" cookieDays: " & cookieDays & "," & vbCrLf)
\r
235 if panels.count > 0 then
\r
236 script.Append(" PanelNamesOnTabHdr: " & FmtBool(PanelNamesOnTabHdr) & "," & vbCrLf)
\r
237 script.Append(" panels: ['" & join(panels.ToArray(),"','") & "']," & vbCrLf)
\r
239 if not IsNothing(headingSort) then script.Append(" headingSort: '" & headingSort & "'," & vbCrLf)
\r
240 if not IsNothing(click) then script.Append(" click: " & click & "," & vbCrLf)
\r
241 if not IsNothing(dblclick) then script.Append(" dblclick: " & dblclick & "," & vbCrLf)
\r
242 if not IsNothing(contextmenu) then script.Append(" contextmenu: " & contextmenu & "," & vbCrLf)
\r
243 if FilterLocation >= -1 then script.Append(" FilterLocation: " & FilterLocation & "," & vbCrLf)
\r
244 if not IsNothing(FilterAllToken) then script.Append(" FilterAllToken: '" & FilterAllToken & "'," & vbCrLf)
\r
245 if FilterBoxMaxLen >= 0 then script.Append(" FilterBoxMaxLen: " & FilterBoxMaxLen & "," & vbCrLf)
\r
246 if FilterAnchorLeft then script.Append(" FilterAnchorLeft: " & FmtBool(FilterAnchorLeft) & "," & vbCrLf)
\r
247 if maxPrint >= 0 then script.Append(" maxPrint: " & maxPrint & "," & vbCrLf)
\r
248 if dndMgrIdx >= 0 then script.Append(" dndMgrIdx: " & dndMgrIdx & "," & vbCrLf)
\r
249 if minPageRows >= 0 then script.Append(" minPageRows: " & minPageRows & "," & vbCrLf)
\r
250 if maxPageRows >= 0 then script.Append(" maxPageRows: " & maxPageRows & "," & vbCrLf)
\r
251 if defaultWidth > 0 then script.Append(" defaultWidth: " & defaultWidth & "," & vbCrLf)
\r
254 script.Append(" canAdd: " & FmtBool(canAdd) & "," & vbCrLf)
\r
255 script.Append(" canEdit: " & FmtBool(canEdit) & "," & vbCrLf)
\r
256 script.Append(" canClone: " & FmtBool(canClone) & "," & vbCrLf)
\r
257 script.Append(" canDelete: " & FmtBool(canDelete) & "," & vbCrLf)
\r
258 script.Append(" ConfirmDelete: " & FmtBool(ConfirmDelete) & "," & vbCrLf)
\r
259 script.Append(" TableSelectNew: '" & TableSelectNew & "'," & vbCrLf)
\r
260 script.Append(" TableSelectNone: '" & TableSelectNone & "'," & vbCrLf)
\r
261 if panelHeight > 0 then script.Append(" panelHeight: " & panelHeight & "," & vbCrLf)
\r
262 if panelWidth > 0 then script.Append(" panelWidth: " & panelWidth & "," & vbCrLf)
\r
263 if maxDisplayLen > 0 then script.Append(" maxDisplayLen: " & maxDisplayLen & "," & vbCrLf)
\r
264 if not IsNothing(formOpen) then script.Append(" formOpen: " & formOpen & "," & vbCrLf)
\r
265 if not IsNothing(formClose) then script.Append(" formClose: " & formClose & "," & vbCrLf)
\r
266 if not IsNothing(formSubmit) then script.Append(" formSubmit: " & onSubmitResponse & "," & vbCrLf)
\r
267 if not IsNothing(onSubmitResponse) then script.Append(" onSubmitResponse: " & onSubmitResponse & "," & vbCrLf)
\r
268 if not IsNothing(showSaveMsg) then script.Append(" showSaveMsg: '" & showSaveMsg & "'," & vbCrLf)
\r
270 script.Append(" columnSpecs : [" & vbCrLf)
\r
272 for c=0 to columns.count-1
\r
273 if c > 0 then script.Append("," & vbCrLf)
\r
274 script.Append(CType(columns(c),GridColumn).script)
\r
275 if columns(c).ConfirmDeleteColumn then confirmCol=c
\r
278 if formView then script.Append("," & vbCrLf & "ConfirmDeleteCol: " & confirmCol)
\r
279 script.Append(vbCrLf & " }" & vbCrLf)
\r
283 dim a as New ArrayList()
\r
284 script.Append(" " & bufferOptVar & " = {")
\r
285 if requestParameters.Count > 0 then
\r
286 Dim param As DictionaryEntry
\r
287 For Each param In requestParameters
\r
288 a.Add(vbCrLf & " {name:'" & param.Key & "',value:'" & param.Value & "'}")
\r
290 script.Append(vbCrLf & " requestParameters: [" & String.Join(",", a.ToArray(Type.GetType("System.String"))) & vbCrLf & " ]")
\r
292 if BufferType="AjaxSQL" then
\r
293 if a.Count > 0 then script.Append(",")
\r
294 script.Append(vbCrLf & " TimeOut: " & Session.Timeout & ",")
\r
295 script.Append(vbCrLf & " fmt: '" & fmt & "'")
\r
297 script.Append(vbCrLf & " }" & vbCrLf)
\r
298 script.Append(" " & bufferVar & " = new Rico.Buffer." & BufferType & "('" & dataProvider & "', " & bufferOptVar & ");" & vbCrLf)
\r
302 script.Append(" " & gridVar & " = new Rico.LiveGrid ('" & Me.UniqueId & "', " & bufferVar & ", " & optionsVar & ");" & vbCrLf)
\r
303 if not IsNothing(menuEvent) then
\r
304 script.Append(" " & gridVar & ".menu = new Rico.GridMenu();" & vbCrLf)
\r
310 script.Append(" if(typeof " & Me.UniqueId & "_FormInit=='function') " & Me.UniqueId & "_FormInit();" & vbCrLf)
\r
311 script.Append(" " & formVar & "=new Rico.TableEdit(" & gridVar & ");" & vbCrLf)
\r
314 script.Append(" if(typeof " & Me.UniqueId & "_InitComplete=='function') " & Me.UniqueId & "_InitComplete();" & vbCrLf)
\r
315 if not IsNothing(afterInit) then script.Append(afterInit & vbCrLf)
\r
317 script.Append("}" & vbCrLf)
\r
318 Return script.ToString
\r
323 ' ----------------------------------------------------
\r
324 ' Properties for LiveGridForms
\r
325 ' ----------------------------------------------------
\r
327 Public dbConnection as object
\r
328 Public formView as Boolean = false
\r
329 Public TableSelectNew as String = "___new___"
\r
330 Public TableSelectNone as String = ""
\r
331 Public canAdd as Boolean = true
\r
332 Public canEdit as Boolean = true
\r
333 Public canClone as Boolean = false
\r
334 Public canDelete as Boolean = true
\r
335 Public ConfirmDelete as Boolean = true
\r
336 Public RecordName as String
\r
337 Public PanelNamesOnTabHdr as Boolean = true
\r
338 Public showSaveMsg as String
\r
339 Public dbDialect as String
\r
340 Public panels as New ArrayList()
\r
341 Public panelHeight as Integer = -1
\r
342 Public panelWidth as Integer = -1
\r
343 Public maxDisplayLen as Integer = -1
\r
346 Public formOpen as String
\r
347 Public formClose as String
\r
348 Public formSubmit as String
\r
349 Public onSubmitResponse as String
\r
351 Public gridVar as String
\r
352 Public formVar as String
\r
353 Public bufferVar as String
\r
354 Public bufferOptVar as String ' name of buffer options js var
\r
355 Public optionsVar as String ' name of grid options js var
\r
357 Protected Tables as New ArrayList()
\r
358 Protected _action As String
\r
359 Protected MainTbl as Integer = -1
\r
362 Public Property TableName() As String
\r
364 if MainTbl >= 0 then
\r
365 Return Tables(MainTbl).TblName
\r
371 MainTbl=Tables.Count
\r
372 dim tab as new AltTable()
\r
379 Public Function AddTable(t as AltTable) as Integer
\r
380 AddTable=Tables.Count
\r
381 if IsNothing(t.TblAlias) then t.TblAlias="a" & Tables.Count
\r
385 Public ReadOnly Property action() As String
\r
391 Public ReadOnly Property CurrentField() As GridColumn
\r
393 Return columns(columns.count-1)
\r
398 ' ----------------------------------------------------
\r
400 ' ----------------------------------------------------
\r
402 formVar=Me.UniqueId & "['edit']"
\r
403 gridVar=Me.UniqueId & "['grid']"
\r
404 bufferVar=Me.UniqueId & "['buffer']"
\r
405 bufferOptVar=Me.UniqueId & "['bufferopt']"
\r
406 optionsVar=Me.UniqueId & "['options']"
\r
407 dim actionparm as String="_action_" & Me.UniqueId
\r
408 _action=trim(Request.QueryString(actionparm))
\r
409 if _action="" then _action=trim(Request.Form(actionparm))
\r
410 if _action="" then _action="table" else _action=lcase(_action)
\r
412 If Not (_gridHeading Is Nothing) Then
\r
413 _gridHeading.InstantiateIn(HdgContainer)
\r
414 For Each ctrl As Control In HdgContainer.Controls
\r
415 If TypeOf(ctrl) is GridColumn then
\r
416 AddColumn(CType(ctrl,GridColumn))
\r
417 ElseIf TypeOf(ctrl) is GridPanel then
\r
418 panels.Add(CType(ctrl,GridPanel).heading)
\r
419 ElseIf TypeOf(ctrl) is AltTable then
\r
425 If Not (_headingTop Is Nothing) Then
\r
426 Dim container As New GridContainer()
\r
427 _headingTop.InstantiateIn(container)
\r
428 LiveGridHeadingsTop.Controls.Add(container)
\r
431 If Not (_headingBottom Is Nothing) Then
\r
432 Dim container As New GridContainer()
\r
433 _headingBottom.InstantiateIn(container)
\r
434 LiveGridHeadingsBottom.Controls.Add(container)
\r
439 ' -------------------------------------------------------------
\r
440 ' Adds a new column to grid, returns column index
\r
441 ' -------------------------------------------------------------
\r
442 Public Function AddColumn(ColumnObj as GridColumn) as integer
\r
443 if ColumnObj.isLookupField() then
\r
445 ' this items get applied to the lookup field instead of the code field
\r
446 dim Hdg as string = ColumnObj.Heading
\r
447 dim width as integer = ColumnObj.Width
\r
448 dim filterUI as string = ColumnObj.filterUI
\r
449 dim ConfirmDelete as boolean = ColumnObj.ConfirmDeleteColumn
\r
450 dim DescriptionCol as String = ColumnObj.DescriptionCol
\r
452 ColumnObj.Heading=Hdg & " Code"
\r
453 ColumnObj.panelIdx=panels.count-1
\r
454 ColumnObj.FieldName=ExtFieldId(columns.count)
\r
455 ColumnObj.filterUI=Nothing
\r
457 ColumnObj.visible=false
\r
458 ColumnObj.ConfirmDeleteColumn=false
\r
459 if not IsNothing(DescriptionCol) then
\r
460 ColumnObj.DescriptionField=ExtFieldId(columns.count+1)
\r
462 columns.Add(ColumnObj)
\r
464 ColumnObj=new GridColumn()
\r
465 ColumnObj.filterUI=filterUI
\r
466 ColumnObj.Width=width
\r
467 ColumnObj.Heading=Hdg
\r
468 ColumnObj.ConfirmDeleteColumn=ConfirmDelete
\r
469 if IsNothing(DescriptionCol) then
\r
470 ColumnObj.Formula="" ' to be filled in by FormSqlQuery()
\r
472 ColumnObj.ColName=DescriptionCol
\r
473 ColumnObj.FormView="hidden"
\r
474 ColumnObj.EntryType="T"
\r
477 ColumnObj.panelIdx=panels.count-1
\r
478 ColumnObj.FieldName=ExtFieldId(columns.count)
\r
479 AddColumn=columns.count
\r
480 columns.Add(ColumnObj)
\r
484 ' -------------------------------------------------------------
\r
485 ' Adds a new column to grid, returns column index
\r
486 ' -------------------------------------------------------------
\r
487 Public Function AddCalculatedField(Heading as string, ColumnFormula as string, optional width as integer = -1, optional ClassName as string = "") as GridColumn
\r
488 Dim ColumnObj as New GridColumn()
\r
489 if left(ColumnFormula,1) <> "(" then ColumnFormula="(" & ColumnFormula & ")"
\r
490 ColumnObj.ColName="Calc_" & columns.count
\r
491 ColumnObj.Formula=ColumnFormula
\r
492 ColumnObj.Heading=Heading
\r
493 if width >= 0 then ColumnObj.Width=width
\r
494 if ClassName <> "" then ColumnObj.ClassName=ClassName
\r
495 AddColumn(ColumnObj)
\r
496 AddCalculatedField=ColumnObj
\r
500 Private Function IsFieldName(s) as boolean
\r
501 dim i as integer, c as string
\r
506 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
512 IsFieldName=(i > 1)
\r
516 ' name used external to this script
\r
517 Private function ExtFieldId(i) as string
\r
518 ExtFieldId=Me.UniqueId & "_" & i
\r
522 Private function FormatValue(v as String, ByVal ColIdx as Integer) as String
\r
523 dim addquotes as Boolean = columns(ColIdx).AddQuotes
\r
524 select case left(columns(ColIdx).EntryType,1)
\r
527 if not IsNumeric(v) then v=""
\r
529 if v=TableSelectNew then
\r
530 v=trim(Request.Form("textnew__" & ExtFieldId(ColIdx)))
\r
531 elseif v=TableSelectNone then
\r
535 if v=TableSelectNone then v=""
\r
537 if v="" and columns(ColIdx).isNullable then
\r
539 elseif addquotes then
\r
540 FormatValue=oSqlCompat.addQuotes(v)
\r
547 Private function FormatFormValue(idx as Integer) as String
\r
549 if IsNothing(columns(idx).EntryType) then exit function
\r
550 if columns(idx).EntryType="H" or columns(idx).FormView="exclude" then
\r
551 v=columns(idx).ColData
\r
553 v=trim(Request.Form(ExtFieldId(idx)))
\r
555 FormatFormValue=FormatValue(v,idx)
\r
559 Private Function AltTableJoinClause(AltTabIdx as Integer) as String
\r
560 dim i as Integer, Condition as String
\r
561 for i=0 to columns.Count-1
\r
562 if columns(i).TableIdx=MainTbl and columns(i).isKey then
\r
563 AddCondition(Condition, Tables(MainTbl).TblAlias & "." & columns(i).ColName & "=" & Tables(AltTabIdx).TblAlias & "." & columns(i).ColName)
\r
566 for i=0 to Tables(AltTabIdx).altFields.Count-1
\r
567 if Tables(AltTabIdx).altFields(i).isKey then
\r
568 AddCondition(Condition, Tables(AltTabIdx).FieldCondition(i))
\r
571 AltTableJoinClause=Condition
\r
575 ' -------------------------------------------------------------
\r
576 ' Add a condition to a where or having clause
\r
577 ' -------------------------------------------------------------
\r
578 Public Sub AddCondition(ByRef WhereClause as String, ByVal NewCondition as String)
\r
579 if IsNothing(NewCondition) then exit sub
\r
580 If IsNothing(WhereClause) Then
\r
581 WhereClause = "(" & NewCondition & ")"
\r
583 WhereClause &= " AND (" & NewCondition & ")"
\r
588 ' -------------------------------------------------------------
\r
589 ' Return the index of a column based on its column name, or -1 if not found
\r
590 ' -------------------------------------------------------------
\r
591 Public Function ColIndex(ByVal SearchName as String) as Integer
\r
594 for i=0 to columns.Count-1
\r
595 if columns(i).ColName=SearchName then
\r
603 ' -------------------------------------------------------------
\r
604 ' Return the a column object based on its column name, or Nothing if not found
\r
605 ' -------------------------------------------------------------
\r
606 Public Function getColumnByName(ByVal SearchName as String) as GridColumn
\r
608 for i=0 to columns.Count-1
\r
609 if columns(i).ColName=SearchName then
\r
610 getColumnByName=columns(i)
\r
617 ' -------------------------------------------------------------
\r
618 ' Return the index of a table based on its table name
\r
619 ' -------------------------------------------------------------
\r
620 Public Function TabIndex(ByVal SearchName as String)
\r
622 for i=0 to Tables.Count-1
\r
623 if Tables(i).TblName=SearchName then
\r
631 ' -------------------------------------------------------------
\r
632 ' Return the index of the new filter
\r
633 ' -------------------------------------------------------------
\r
634 Public Function AddFilter(ByVal newfilter as String)
\r
635 AddFilter=_sqlFilters.Count
\r
636 _sqlFilters.Add(newfilter)
\r
640 ' form where clause based on table's primary key
\r
641 Private function TableKeyWhereClause(TabIdx as Integer) as String
\r
642 dim i as Integer, w as String
\r
643 for i=0 to columns.Count-1
\r
644 if (columns(i).TableIdx=MainTbl or columns(i).TableIdx=TabIdx) and IsNothing(columns(i).Formula) and columns(i).isKey then
\r
645 AddCondition(w, columns(i).ColName & "=" & FormatValue(trim(Request.Form("_k" & i)),i))
\r
648 if IsNothing(w) then
\r
649 'Throw New Exception("no key value")
\r
651 TableKeyWhereClause=" WHERE " & w
\r
656 Protected Sub GetColumnInfo()
\r
657 dim t as Integer, r as Integer, c as Integer, colname as String, schemaTable As DataTable
\r
658 Dim restrictions(3) As String
\r
659 if IsNothing(Me.dbConnection) then exit sub
\r
660 for t=0 to Tables.Count-1
\r
661 if debug then DebugString &= "<p>Table: " & Tables(t).TblName & " tblidx=" & t & " colcnt=" & columns.Count
\r
663 Dim command = Me.dbConnection.CreateCommand()
\r
664 command.CommandText = "select * from " & Tables(t).TblName
\r
665 dim rdr = command.ExecuteReader(CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly)
\r
666 schemaTable = rdr.GetSchemaTable()
\r
667 For Each colinfo As DataRow In schemaTable.Rows
\r
668 colname = colinfo("ColumnName").ToString
\r
669 for c=0 to columns.Count-1
\r
670 if t=columns(c).TableIdx and colname=columns(c).ColName then
\r
672 .isNullable=CBool(colinfo("AllowDBNull"))
\r
673 .TypeName=replace(colinfo("DataType").ToString(),"System.","")
\r
674 if .TypeName<>"String" AndAlso not IsDBNull(colinfo("NumericPrecision")) AndAlso colinfo("NumericPrecision")<>0 then
\r
675 .Length=colinfo("NumericPrecision")
\r
676 elseif not IsDBNull(colinfo("ColumnSize")) then
\r
677 .Length=colinfo("ColumnSize")
\r
679 .Writeable=not colinfo("IsReadOnly")
\r
680 .isKey=colinfo("IsKey")
\r
681 'columns(c).FixedLength=((colinfo("COLUMN_FLAGS") and &H0000010) <> 0)
\r
682 if debug then DebugString &= "<br> Column: " & colname & " type=" & .TypeName & " len=" & .Length & " nullable=" & .isNullable & " isKey=" & .isKey
\r
688 for c=0 to Tables(t).altFields.Count-1
\r
689 if colname=Tables(t).altFields(c).ColName then
\r
690 Tables(t).altFields(c).isKey=colinfo("IsKey")
\r
697 ' AllowDBNull is not accurate when using Jet driver
\r
698 if InStr(Me.dbConnection.ConnectionString,"Microsoft.Jet") > 0 then
\r
699 restrictions(2)=Tables(t).TblName
\r
700 schemaTable = Me.dbConnection.GetSchema("Columns",restrictions)
\r
701 For Each colinfo As DataRow In schemaTable.Rows
\r
702 colname = colinfo("column_name").ToString
\r
703 for c=0 to columns.Count-1
\r
704 if t=columns(c).TableIdx and colname=columns(c).ColName then
\r
706 .isNullable=CBool(colinfo("is_nullable"))
\r
707 if debug then DebugString &= "<br> Column: " & colname & " nullable=" & .isNullable
\r
718 Protected Function UpdateDatabase(sqltext as String, actiontxt as String) as String
\r
720 if IsNothing(Me.dbConnection) then
\r
721 UpdateDatabase="<p>ERROR: no database connection</p>"
\r
724 Dim command = Me.dbConnection.CreateCommand()
\r
725 command.CommandText = sqltext
\r
726 cnt=command.ExecuteNonQuery()
\r
727 UpdateDatabase="<p class='ricoFormResponse " & actiontxt & "Successfully'></p>"
\r
728 Catch ex As Exception
\r
729 dim msg="<p>ERROR: unable to update database - " & server.HTMLencode(ex.Message.ToString()) & "</p>"
\r
730 if LogSqlOnError then msg &= "<br>" & server.HTMLencode(sqltext)
\r
733 'if debug then msg &= " - " & sqltext & " - Records affected: " & cnt
\r
738 Public Sub DeleteRecord(writer as HTMLTextWriter)
\r
739 dim sqltext as String = "DELETE FROM " & Tables(MainTbl).TblName & TableKeyWhereClause(MainTbl)
\r
740 writer.WriteLine(UpdateDatabase(sqltext, "deleted"))
\r
744 Public Sub InsertRecord(writer as HTMLTextWriter)
\r
745 dim i as Integer, keyIdx as Integer
\r
746 dim keyCnt as Integer=0
\r
747 dim sqlcol as String=""
\r
748 dim sqlval as String=""
\r
749 for i=0 to columns.Count-1
\r
750 if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).UpdateOnly=false then
\r
751 if columns(i).isKey then
\r
755 if columns(i).Writeable then
\r
756 sqlcol &= "," & columns(i).ColName
\r
757 sqlval &= "," & FormatFormValue(i)
\r
761 if IsNothing(sqlcol) then
\r
762 writer.WriteLine("<p>Nothing to add</p>")
\r
764 dim sqltext as String="insert into " & Tables(MainTbl).TblName & " (" & mid(sqlcol,2) & ") values (" & mid(sqlval,2) & ")"
\r
765 dim updateMsg as String = UpdateDatabase(sqltext, "added")
\r
766 writer.WriteLine(updateMsg)
\r
771 Public Sub UpdateRecord(writer as HTMLTextWriter)
\r
772 dim i as Integer, sqltext as String, errmsg as String=""
\r
773 for i=0 to Tables.Count-1
\r
774 if i<>MainTbl then errmsg &= UpdateAltTableRecords(i)
\r
777 writer.WriteLine("<p>" & errmsg & "</p>")
\r
780 for i=0 to columns.Count-1
\r
781 if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).Writeable and columns(i).InsertOnly=false then
\r
782 sqltext &= "," & columns(i).ColName & "=" & FormatFormValue(i)
\r
785 if not IsNothing(sqltext) then
\r
786 sqltext="UPDATE " & Tables(MainTbl).TblName & " SET " & mid(sqltext,2) & TableKeyWhereClause(MainTbl)
\r
787 writer.WriteLine(UpdateDatabase(sqltext, "updated"))
\r
788 elseif Tables.Count > 1 then
\r
789 ' only updated altTable records
\r
790 writer.WriteLine("<p class='ricoFormResponse updatedSuccessfully'></p>")
\r
792 writer.WriteLine("<p>Nothing to update</p>")
\r
797 Private function UpdateAltTableRecords(tabidx as Integer)
\r
798 dim j as Integer, cnt as Integer
\r
799 dim sqltext as String, colnames as String, coldata as String
\r
800 dim whereClause as String, errmsg as String
\r
802 ' check for existing record
\r
804 whereClause = TableKeyWhereClause(tabidx) & Tables(tabidx).KeyCondition()
\r
805 sqltext="select count(*) from " & Tables(tabidx).TblName & " " & Tables(tabidx).TblAlias & whereClause
\r
807 Dim command = Me.dbConnection.CreateCommand()
\r
808 command.CommandText = sqltext
\r
809 cnt=command.ExecuteScalar()
\r
810 Catch ex As Exception
\r
811 errmsg = "UpdateAltTableRecords Count<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
812 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
813 UpdateAltTableRecords = errmsg
\r
819 ' insert new record
\r
823 for j=0 to columns.Count-1
\r
824 if (columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType)) or columns(j).isKey then
\r
825 colnames &= "," & columns(j).ColName
\r
826 coldata &= "," & FormatFormValue(j)
\r
829 for j=0 to Tables(tabidx).altFields.Count-1
\r
830 colnames &= "," & Tables(tabidx).altFields(j).ColName
\r
831 coldata &= "," & Tables(tabidx).altFields(j).Data
\r
834 Dim command = Me.dbConnection.CreateCommand()
\r
835 sqltext="insert into " & Tables(tabidx).TblName & " (" & mid(colnames,2) & ") values (" & mid(coldata,2) & ")"
\r
836 command.CommandText = sqltext
\r
837 cnt=command.ExecuteNonQuery()
\r
838 Catch ex As Exception
\r
839 errmsg="UpdateAltTableRecords Insert<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
840 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
841 UpdateAltTableRecords = errmsg
\r
849 for j=0 to columns.Count-1
\r
850 if columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType) then
\r
851 sqltext &= "," & columns(j).ColName & "=" & FormatFormValue(j)
\r
854 for j=0 to Tables(tabidx).altFields.Count-1
\r
855 sqltext &= "," & Tables(tabidx).altFields(j).ColName & "=" & Tables(tabidx).altFields(j).Data
\r
857 if sqltext <> "" then
\r
859 Dim command = Me.dbConnection.CreateCommand()
\r
860 sqltext="update " & Tables(tabidx).TblName & " set " & mid(sqltext,2) & whereClause
\r
861 command.CommandText = sqltext
\r
862 cnt=command.ExecuteNonQuery()
\r
863 Catch ex As Exception
\r
864 errmsg="UpdateAltTableRecords Update<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
865 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
866 UpdateAltTableRecords = errmsg
\r
873 ' -------------------------------------
\r
874 ' form main sql query to populate the grid
\r
875 ' -------------------------------------
\r
876 Protected Sub FormSqlQuery()
\r
877 Dim oParseLookup=new sqlParse
\r
878 Dim oParseSubQry=new sqlParse
\r
882 Dim tabidx as Integer
\r
883 Dim csvPrimaryKey as String
\r
884 if debug then DebugString &= "<p>FormSqlQuery"
\r
885 oParseMain.FromClause=Tables(MainTbl).TblName & " t"
\r
886 for i=0 to Tables.Count-1
\r
888 s="left join " & Tables(i).TblName & " " & Tables(i).TblAlias & " ON " & AltTableJoinClause(i)
\r
889 oParseMain.AddJoin(s)
\r
892 oParseMain.AddWhereCondition(TableFilter)
\r
894 ' build sql for each column
\r
896 for i=0 to columns.Count-1
\r
897 if columns(i).TableIdx>=0 then tabidx=columns(i).TableIdx
\r
898 if columns(i).FilterFlag then
\r
899 ' add any column filters to where clause
\r
900 oParseMain.AddWhereCondition(Tables(tabidx).TblAlias & "." & columns(i).ColName & "='" & columns(i).ColData & "'")
\r
903 if not IsNothing(columns(i).Formula) then
\r
907 oParseMain.AddColumn("(" & columns(i).Formula & ")", columns(i).Heading)
\r
909 elseif tabidx=MainTbl then
\r
911 ' column from main table - avoid subqueries to make it compatible with MS Access & MySQL < v4.1
\r
913 if columns(i).isKey then
\r
914 if not IsNothing(csvPrimaryKey) then csvPrimaryKey &= ","
\r
915 csvPrimaryKey &= Tables(tabidx).TblAlias & "." & columns(i).ColName
\r
917 if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then
\r
918 Dim TblAlias as String="t" & CStr(i)
\r
919 s=replace(columns(i).SelectSql,"%alias%",TblAlias & ".")
\r
920 oParseLookup.ParseSelect(s)
\r
921 if oParseLookup.SelectList.count=2 then
\r
922 Dim codeField as String=oParseLookup.SelectList(0).sql
\r
923 Dim descField as String=oParseLookup.SelectList(1).sql
\r
924 If IsFieldName(descField) Then
\r
925 descField=TblAlias & "." & descField
\r
927 descField=replace(replace(descField,"%alias%",TblAlias & "."),"%aliasmain%","t.")
\r
929 s="left join " & oParseLookup.FromClause & " " & TblAlias & " on t." & columns(i).ColName & "=" & TblAlias & "." & replace(replace(codeField,"%alias%",""),"%aliasmain%","")
\r
930 if not IsNothing(oParseLookup.WhereClause) then s &= " and " & replace(oParseLookup.WhereClause,"%alias%",TblAlias & ".")
\r
931 oParseMain.AddJoin(s)
\r
932 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)
\r
933 if IsNothing(columns(i).DescriptionCol) then
\r
934 columns(i+1).Formula=descField
\r
938 Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")
\r
941 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)
\r
946 ' column from alt table - no avoiding subqueries here
\r
948 if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then
\r
949 oParseLookup.ParseSelect(columns(i).SelectSql)
\r
950 if oParseLookup.SelectList.count=2 then
\r
951 Dim descQuery as String="select " & oParseLookup.SelectList(1).sql & " from " & oParseLookup.FromClause & " where " & _
\r
952 oParseLookup.SelectList(0).sql & "=" & Tables(tabidx).TblAlias & "." & columns(i).ColName
\r
953 if not IsNothing(oParseLookup.WhereClause) then descQuery=descQuery & " and " & oParseLookup.WhereClause
\r
954 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)
\r
955 columns(i+1).Formula="(" & descQuery & ")"
\r
957 Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")
\r
960 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)
\r
965 if not IsNothing(columns(i).EntryType) then
\r
966 Dim SessionColId as String = ExtFieldId(i)
\r
967 if InStr("CSNR",left(columns(i).EntryType,1)) > 0 then
\r
968 if not IsNothing(columns(i).SelectSql) then
\r
969 s=columns(i).SelectSql
\r
970 if not IsNothing(columns(i).SelectFilter) then
\r
971 oParseLookup.ParseSelect(s)
\r
972 oParseLookup.AddWhereCondition(columns(i).SelectFilter)
\r
973 s=oParseLookup.UnparseSelect
\r
975 oParseMain.SelectList(i).LookupQuery=replace(replace(s,"%alias%",""),"%aliasmain%","")
\r
977 oParseMain.SelectList(i).LookupQuery="select distinct " & columns(i).ColName & " from " & Tables(tabidx).TblName & " where " & columns(i).ColName & " is not null"
\r
982 if not IsNothing(DefaultSort) then
\r
983 oParseMain.AddSort(DefaultSort)
\r
984 elseif not IsNothing(csvPrimaryKey) then
\r
985 oParseMain.AddSort(csvPrimaryKey)
\r
990 Protected Overrides Sub OnPreRender(ByVal e As System.EventArgs)
\r
991 MyBase.OnPreRender(e)
\r
992 if not IsNothing(dbConnection) then
\r
993 oSqlCompat=New sqlCompatibilty(dbDialect)
\r
998 for i=0 to columns.Count-1
\r
999 if not IsNothing(columns(i).AltTable) then
\r
1000 columns(i).TableIdx=TabIndex(columns(i).AltTable)
\r
1002 columns(i).TableIdx=MainTbl
\r
1004 Dim cell as New TableHeaderCell()
\r
1005 cell.Text=columns(i).Heading
\r
1006 if i<frozenColumns then cell.CssClass="ricoFrozen"
\r
1007 LiveGridHeadingsMain.Controls.Add(cell)
\r
1010 if BufferType="AjaxXML" or not sessions then Me.DisplayTimer=false
\r
1011 if Tables.count > 0 then
\r
1012 Me.GetColumnInfo()
\r
1013 if _action="table" and sessions then
\r
1015 session.contents(Me.UniqueId)=oParseMain
\r
1016 session.contents(Me.UniqueId & ".filters")=Me._sqlFilters
\r
1017 elseif _action="query" then
\r
1022 ' populate globalInitScript
\r
1023 Dim FixedGridScript as String = ""
\r
1024 Dim VarGridScript as String = ""
\r
1025 Dim gblFormView as Boolean = false
\r
1026 If Not Page.IsStartupScriptRegistered("LiveGridInit") Then
\r
1027 For Each ctrl As Control In Page.Controls
\r
1028 If TypeOf(ctrl) is LiveGrid then
\r
1029 if CType(ctrl,LiveGrid).Rows >= 0 then
\r
1030 FixedGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf
\r
1032 VarGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf
\r
1034 if CType(ctrl,LiveGrid).formView then gblFormView=true
\r
1037 globalInitScript = "Rico.acceptLanguage('" & Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") & "');" & vbCrLf
\r
1038 if not UsingMinRico then
\r
1039 globalInitScript &= "Rico.loadModule('LiveGridAjax','LiveGridMenu');" & vbCrLf
\r
1040 if gblFormView then globalInitScript &= "Rico.loadModule('LiveGridForms');" & vbCrLf
\r
1042 globalInitScript &= "Rico.onLoad( function() {" & vbCrLf
\r
1043 globalInitScript &= FixedGridScript ' initialize grids with fixed # of rows first
\r
1044 globalInitScript &= VarGridScript & "});" & vbCrLf ' then initialize grids with variable # of rows
\r
1045 Page.RegisterStartupScript("LiveGridInit", "")
\r
1050 Public Class GridContainer
\r
1052 Implements INamingContainer
\r