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 = sizeToBody
\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 saveColumnWidth as Boolean = True
\r
60 Public saveColumnFilter as Boolean = False
\r
61 Public saveColumnSort as Boolean = False
\r
62 Public cookieDays as Integer
\r
63 Public DefaultSort as String
\r
64 Public maxPrint as Integer = -1
\r
65 Public dndMgrIdx as Integer = -1
\r
66 Public UsingMinRico as Boolean = False ' using minified version of Rico?
\r
67 Public sessions as Boolean = True
\r
68 Public minPageRows as Integer = -1
\r
69 Public maxPageRows as Integer = -1
\r
70 Public defaultWidth as Integer = -1 ' if -1, then use unformatted column width, otherwise this is the default width in pixels
\r
71 Public debug as Boolean = False
\r
72 Public LogSqlOnError as Boolean = false ' include sql statement in results if an error occurs (true/false)
\r
74 ' ----------------------------------------------------
\r
75 ' Public Properties for buffer
\r
76 ' ----------------------------------------------------
\r
77 Public BufferType as String = "AjaxSQL" ' can be overridden to AjaxXML
\r
78 Public fmt as String = "xml"
\r
79 Public largeBufferSize as Integer = -1 ' controls size of client buffer and AJAX fetch size
\r
80 Public requestParameters as New Hashtable()
\r
83 Public Property rows() As Integer
\r
87 Set(ByVal Value As Integer)
\r
92 <TemplateContainer(GetType(GridContainer))> _
\r
93 Public Property GridColumns() As ITemplate
\r
98 _gridHeading = value
\r
102 <TemplateContainer(GetType(GridContainer))> _
\r
103 Public Property HeadingTop() As ITemplate
\r
108 _headingTop = value
\r
112 <TemplateContainer(GetType(GridContainer))> _
\r
113 Public Property HeadingBottom() As ITemplate
\r
115 Return _headingBottom
\r
118 _headingBottom = value
\r
122 Public WriteOnly Property sqlQuery() As String
\r
123 Set(ByVal SqlText As String)
\r
124 if oParseMain.ParseSelect(SqlText) then
\r
125 ' sync column headings
\r
127 for i=0 to oParseMain.SelectList.Count-1
\r
128 if i < Me.columns.Count then
\r
129 if IsNothing(Me.columns(i).Heading) then
\r
130 Me.columns(i).Heading=oParseMain.SelectList(i).name
\r
132 oParseMain.SelectList(i).name=Me.columns(i).Heading
\r
135 AddCalculatedField(oParseMain.SelectList(i).name, oParseMain.SelectList(i).sql)
\r
138 if sessions then session.contents(Me.UniqueId)=oParseMain
\r
140 Throw New Exception("Invalid SQL statement")
\r
145 Public ReadOnly Property ParseClone() As object
\r
147 return oParseMain.Clone()
\r
151 Protected ReadOnly Property TimerSpan() As String
\r
153 if Me.DisplayTimer then
\r
154 Return "<span id='" & Me.UniqueId & "_timer' class='ricoSessionTimer'> </span>"
\r
161 Protected ReadOnly Property BookmarkSpan() As String
\r
163 if Me.DisplayBookmark then
\r
164 Return "<span id='" & Me.UniqueId & "_bookmark'> </span>"
\r
171 Protected ReadOnly Property SaveMsgSpan() As String
\r
173 if Me.formView then
\r
174 Return "<span id='" & Me.UniqueId & "_savemsg' class='ricoSaveMsg'></span>"
\r
181 Protected ReadOnly Property CaptionSpan() As String
\r
183 if IsNothing(Caption) then
\r
186 Return "<span id='" & Me.UniqueId & "_caption' class='ricoCaption'>" & Me.Caption & "</span>"
\r
191 Protected ReadOnly Property FilterIcon() As String
\r
193 if FilterLocation >= -1 then
\r
194 Return "<a id='ex3_filterLink' href='#' style='margin-right:1em;'></a>"
\r
201 Protected ReadOnly Property Bookmark() As String
\r
203 if Me.DisplayBookmark or Me.DisplayTimer or not IsNothing(Caption) then
\r
204 Return "<p class='ricoBookmark'>" & Me.CaptionSpan & Me.TimerSpan & Me.FilterIcon & Me.BookmarkSpan & Me.SaveMsgSpan & "</p>"
\r
211 Private function FmtBool(b)
\r
212 if b then FmtBool="true" else FmtBool="false"
\r
215 Protected ReadOnly Property init_Script() As String
\r
217 Dim script as New System.Text.StringBuilder(), confirmCol as Integer=0
\r
218 script.Append("var " & Me.UniqueId & " = {};" & vbCrLf)
\r
219 script.Append("function " & Me.UniqueId & "_init" & "() {" & vbCrLf)
\r
220 if not IsNothing(beforeInit) then script.Append(beforeInit & vbCrLf)
\r
224 script.Append(" " & optionsVar & " = {" & vbCrLf)
\r
225 script.Append(" visibleRows: " & Me.rows & "," & vbCrLf)
\r
226 script.Append(" frozenColumns: " & frozenColumns & "," & vbCrLf)
\r
227 script.Append(" canSortDefault: " & FmtBool(canSortDefault) & "," & vbCrLf)
\r
228 script.Append(" canHideDefault: " & FmtBool(canHideDefault) & "," & vbCrLf)
\r
229 script.Append(" canFilterDefault: " & FmtBool(canFilterDefault) & "," & vbCrLf)
\r
230 script.Append(" allowColResize: " & FmtBool(allowColResize) & "," & vbCrLf)
\r
231 script.Append(" highlightElem: '" & highlightElem & "'," & vbCrLf)
\r
232 if not IsNothing(highlightMethod) then script.Append(" highlightMethod: '" & highlightMethod & "'," & vbCrLf)
\r
233 script.Append(" prefetchBuffer: " & FmtBool(prefetchBuffer) & "," & vbCrLf)
\r
234 script.Append(" menuEvent: '" & menuEvent & "'," & vbCrLf)
\r
235 if not IsNothing(RecordName) then script.Append(" RecordName: '" & RecordName & "'," & vbCrLf)
\r
236 script.Append(" saveColumnInfo: {width:" & FmtBool(saveColumnWidth) & ", filter:" & FmtBool(saveColumnFilter) & ", sort:" & FmtBool(saveColumnSort) & "}," & vbCrLf)
\r
237 if not IsNothing(cookieDays) then script.Append(" cookieDays: " & cookieDays & "," & vbCrLf)
\r
239 if panels.count > 0 then
\r
240 script.Append(" PanelNamesOnTabHdr: " & FmtBool(PanelNamesOnTabHdr) & "," & vbCrLf)
\r
241 script.Append(" panels: ['" & join(panels.ToArray(),"','") & "']," & vbCrLf)
\r
243 if not IsNothing(headingSort) then script.Append(" headingSort: '" & headingSort & "'," & vbCrLf)
\r
244 if not IsNothing(click) then script.Append(" click: " & click & "," & vbCrLf)
\r
245 if not IsNothing(dblclick) then script.Append(" dblclick: " & dblclick & "," & vbCrLf)
\r
246 if not IsNothing(contextmenu) then script.Append(" contextmenu: " & contextmenu & "," & vbCrLf)
\r
247 if FilterLocation >= -1 then script.Append(" FilterLocation: " & FilterLocation & "," & vbCrLf)
\r
248 if not IsNothing(FilterAllToken) then script.Append(" FilterAllToken: '" & FilterAllToken & "'," & vbCrLf)
\r
249 if FilterBoxMaxLen >= 0 then script.Append(" FilterBoxMaxLen: " & FilterBoxMaxLen & "," & vbCrLf)
\r
250 if FilterAnchorLeft then script.Append(" FilterAnchorLeft: " & FmtBool(FilterAnchorLeft) & "," & vbCrLf)
\r
251 if maxPrint >= 0 then script.Append(" maxPrint: " & maxPrint & "," & vbCrLf)
\r
252 if dndMgrIdx >= 0 then script.Append(" dndMgrIdx: " & dndMgrIdx & "," & vbCrLf)
\r
253 if minPageRows >= 0 then script.Append(" minPageRows: " & minPageRows & "," & vbCrLf)
\r
254 if maxPageRows >= 0 then script.Append(" maxPageRows: " & maxPageRows & "," & vbCrLf)
\r
255 if defaultWidth > 0 then script.Append(" defaultWidth: " & defaultWidth & "," & vbCrLf)
\r
258 script.Append(" canAdd: " & FmtBool(canAdd) & "," & vbCrLf)
\r
259 script.Append(" canEdit: " & FmtBool(canEdit) & "," & vbCrLf)
\r
260 script.Append(" canClone: " & FmtBool(canClone) & "," & vbCrLf)
\r
261 script.Append(" canDelete: " & FmtBool(canDelete) & "," & vbCrLf)
\r
262 script.Append(" ConfirmDelete: " & FmtBool(ConfirmDelete) & "," & vbCrLf)
\r
263 script.Append(" TableSelectNew: '" & TableSelectNew & "'," & vbCrLf)
\r
264 script.Append(" TableSelectNone: '" & TableSelectNone & "'," & vbCrLf)
\r
265 if panelHeight > 0 then script.Append(" panelHeight: " & panelHeight & "," & vbCrLf)
\r
266 if panelWidth > 0 then script.Append(" panelWidth: " & panelWidth & "," & vbCrLf)
\r
267 if maxDisplayLen > 0 then script.Append(" maxDisplayLen: " & maxDisplayLen & "," & vbCrLf)
\r
268 if not IsNothing(formOpen) then script.Append(" formOpen: " & formOpen & "," & vbCrLf)
\r
269 if not IsNothing(formClose) then script.Append(" formClose: " & formClose & "," & vbCrLf)
\r
270 if not IsNothing(formSubmit) then script.Append(" formSubmit: " & onSubmitResponse & "," & vbCrLf)
\r
271 if not IsNothing(onSubmitResponse) then script.Append(" onSubmitResponse: " & onSubmitResponse & "," & vbCrLf)
\r
272 if not IsNothing(showSaveMsg) then script.Append(" showSaveMsg: '" & showSaveMsg & "'," & vbCrLf)
\r
274 script.Append(" columnSpecs : [" & vbCrLf)
\r
276 for c=0 to columns.count-1
\r
277 if c > 0 then script.Append("," & vbCrLf)
\r
278 script.Append(CType(columns(c),GridColumn).script)
\r
279 if columns(c).ConfirmDeleteColumn then confirmCol=c
\r
282 if formView then script.Append("," & vbCrLf & "ConfirmDeleteCol: " & confirmCol)
\r
283 script.Append(vbCrLf & " }" & vbCrLf)
\r
287 dim a as New ArrayList()
\r
288 script.Append(" " & bufferOptVar & " = {")
\r
289 if requestParameters.Count > 0 then
\r
290 Dim param As DictionaryEntry
\r
291 For Each param In requestParameters
\r
292 a.Add(vbCrLf & " {name:'" & param.Key & "',value:'" & param.Value & "'}")
\r
294 script.Append(vbCrLf & " requestParameters: [" & String.Join(",", a.ToArray(Type.GetType("System.String"))) & vbCrLf & " ]")
\r
296 if BufferType="AjaxSQL" then
\r
297 if a.Count > 0 then script.Append(",")
\r
298 script.Append(vbCrLf & " TimeOut: " & Session.Timeout & ",")
\r
299 if largeBufferSize > 0 then script.Append(vbCrLf & " largeBufferSize: " & largeBufferSize & ",")
\r
300 script.Append(vbCrLf & " fmt: '" & fmt & "'")
\r
302 script.Append(vbCrLf & " }" & vbCrLf)
\r
303 script.Append(" " & bufferVar & " = new Rico.Buffer." & BufferType & "('" & dataProvider & "', " & bufferOptVar & ");" & vbCrLf)
\r
307 script.Append(" " & gridVar & " = new Rico.LiveGrid ('" & Me.UniqueId & "', " & bufferVar & ", " & optionsVar & ");" & vbCrLf)
\r
308 if not IsNothing(menuEvent) then
\r
309 script.Append(" " & gridVar & ".menu = new Rico.GridMenu();" & vbCrLf)
\r
315 script.Append(" if(typeof " & Me.UniqueId & "_FormInit=='function') " & Me.UniqueId & "_FormInit();" & vbCrLf)
\r
316 script.Append(" " & formVar & "=new Rico.TableEdit(" & gridVar & ");" & vbCrLf)
\r
319 script.Append(" if(typeof " & Me.UniqueId & "_InitComplete=='function') " & Me.UniqueId & "_InitComplete();" & vbCrLf)
\r
320 if not IsNothing(afterInit) then script.Append(afterInit & vbCrLf)
\r
322 script.Append("}" & vbCrLf)
\r
323 Return script.ToString
\r
328 ' ----------------------------------------------------
\r
329 ' Properties for LiveGridForms
\r
330 ' ----------------------------------------------------
\r
332 Public dbConnection as object
\r
333 Public formView as Boolean = false
\r
334 Public TableSelectNew as String = "___new___"
\r
335 Public TableSelectNone as String = ""
\r
336 Public canAdd as Boolean = true
\r
337 Public canEdit as Boolean = true
\r
338 Public canClone as Boolean = false
\r
339 Public canDelete as Boolean = true
\r
340 Public ConfirmDelete as Boolean = true
\r
341 Public RecordName as String
\r
342 Public PanelNamesOnTabHdr as Boolean = true
\r
343 Public showSaveMsg as String
\r
344 Public dbDialect as String
\r
345 Public panels as New ArrayList()
\r
346 Public panelHeight as Integer = -1
\r
347 Public panelWidth as Integer = -1
\r
348 Public maxDisplayLen as Integer = -1
\r
351 Public formOpen as String
\r
352 Public formClose as String
\r
353 Public formSubmit as String
\r
354 Public onSubmitResponse as String
\r
356 Public gridVar as String
\r
357 Public formVar as String
\r
358 Public bufferVar as String
\r
359 Public bufferOptVar as String ' name of buffer options js var
\r
360 Public optionsVar as String ' name of grid options js var
\r
362 Protected Tables as New ArrayList()
\r
363 Protected _action As String
\r
364 Protected MainTbl as Integer = -1
\r
367 Public Property TableName() As String
\r
369 if MainTbl >= 0 then
\r
370 Return Tables(MainTbl).TblName
\r
376 MainTbl=Tables.Count
\r
377 dim tab as new AltTable()
\r
384 Public Function AddTable(t as AltTable) as Integer
\r
385 AddTable=Tables.Count
\r
386 if IsNothing(t.TblAlias) then t.TblAlias="a" & Tables.Count
\r
390 Public ReadOnly Property action() As String
\r
396 Public ReadOnly Property CurrentField() As GridColumn
\r
398 Return columns(columns.count-1)
\r
403 ' ----------------------------------------------------
\r
405 ' ----------------------------------------------------
\r
407 formVar=Me.UniqueId & "['edit']"
\r
408 gridVar=Me.UniqueId & "['grid']"
\r
409 bufferVar=Me.UniqueId & "['buffer']"
\r
410 bufferOptVar=Me.UniqueId & "['bufferopt']"
\r
411 optionsVar=Me.UniqueId & "['options']"
\r
412 dim actionparm as String="_action_" & Me.UniqueId
\r
413 _action=trim(Request.QueryString(actionparm))
\r
414 if _action="" then _action=trim(Request.Form(actionparm))
\r
415 if _action="" then _action="table" else _action=lcase(_action)
\r
417 If Not (_gridHeading Is Nothing) Then
\r
418 _gridHeading.InstantiateIn(HdgContainer)
\r
419 For Each ctrl As Control In HdgContainer.Controls
\r
420 If TypeOf(ctrl) is GridColumn then
\r
421 AddColumn(CType(ctrl,GridColumn))
\r
422 ElseIf TypeOf(ctrl) is GridPanel then
\r
423 panels.Add(CType(ctrl,GridPanel).heading)
\r
424 ElseIf TypeOf(ctrl) is AltTable then
\r
430 If Not (_headingTop Is Nothing) Then
\r
431 Dim container As New GridContainer()
\r
432 _headingTop.InstantiateIn(container)
\r
433 LiveGridHeadingsTop.Controls.Add(container)
\r
436 If Not (_headingBottom Is Nothing) Then
\r
437 Dim container As New GridContainer()
\r
438 _headingBottom.InstantiateIn(container)
\r
439 LiveGridHeadingsBottom.Controls.Add(container)
\r
444 ' -------------------------------------------------------------
\r
445 ' Adds a new column to grid, returns column index
\r
446 ' -------------------------------------------------------------
\r
447 Public Function AddColumn(ColumnObj as GridColumn) as integer
\r
448 if ColumnObj.isLookupField() then
\r
450 ' this items get applied to the lookup field instead of the code field
\r
451 dim Hdg as string = ColumnObj.Heading
\r
452 dim width as integer = ColumnObj.Width
\r
453 dim filterUI as string = ColumnObj.filterUI
\r
454 dim ConfirmDelete as boolean = ColumnObj.ConfirmDeleteColumn
\r
455 dim DescriptionCol as String = ColumnObj.DescriptionCol
\r
457 ColumnObj.Heading=Hdg & " Code"
\r
458 ColumnObj.panelIdx=panels.count-1
\r
459 ColumnObj.FieldName=ExtFieldId(columns.count)
\r
460 ColumnObj.filterUI=Nothing
\r
462 ColumnObj.visible=false
\r
463 ColumnObj.ConfirmDeleteColumn=false
\r
464 if not IsNothing(DescriptionCol) then
\r
465 ColumnObj.DescriptionField=ExtFieldId(columns.count+1)
\r
467 columns.Add(ColumnObj)
\r
469 ColumnObj=new GridColumn()
\r
470 ColumnObj.filterUI=filterUI
\r
471 ColumnObj.Width=width
\r
472 ColumnObj.Heading=Hdg
\r
473 ColumnObj.ConfirmDeleteColumn=ConfirmDelete
\r
474 if IsNothing(DescriptionCol) then
\r
475 ColumnObj.Formula="" ' to be filled in by FormSqlQuery()
\r
477 ColumnObj.ColName=DescriptionCol
\r
478 ColumnObj.FormView="hidden"
\r
479 ColumnObj.EntryType="T"
\r
482 ColumnObj.panelIdx=panels.count-1
\r
483 ColumnObj.FieldName=ExtFieldId(columns.count)
\r
484 AddColumn=columns.count
\r
485 columns.Add(ColumnObj)
\r
489 ' -------------------------------------------------------------
\r
490 ' Adds a new column to grid, returns column index
\r
491 ' -------------------------------------------------------------
\r
492 Public Function AddCalculatedField(Heading as string, ColumnFormula as string, optional width as integer = -1, optional ClassName as string = "") as GridColumn
\r
493 Dim ColumnObj as New GridColumn()
\r
494 if left(ColumnFormula,1) <> "(" then ColumnFormula="(" & ColumnFormula & ")"
\r
495 ColumnObj.ColName="Calc_" & columns.count
\r
496 ColumnObj.Formula=ColumnFormula
\r
497 ColumnObj.Heading=Heading
\r
498 if width >= 0 then ColumnObj.Width=width
\r
499 if ClassName <> "" then ColumnObj.ClassName=ClassName
\r
500 AddColumn(ColumnObj)
\r
501 AddCalculatedField=ColumnObj
\r
505 Private Function IsFieldName(s) as boolean
\r
506 dim i as integer, c as string
\r
511 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
517 IsFieldName=(i > 1)
\r
521 ' name used external to this script
\r
522 Private function ExtFieldId(i) as string
\r
523 ExtFieldId=Me.UniqueId & "_" & i
\r
527 Private function FormatValue(v as String, ByVal ColIdx as Integer) as String
\r
528 dim addquotes as Boolean = columns(ColIdx).AddQuotes
\r
529 select case left(columns(ColIdx).EntryType,1)
\r
532 if not IsNumeric(v) then v=""
\r
534 if v=TableSelectNew then
\r
535 v=trim(Request.Form("textnew__" & ExtFieldId(ColIdx)))
\r
536 elseif v=TableSelectNone then
\r
540 if v=TableSelectNone then v=""
\r
542 if v="" and columns(ColIdx).isNullable then
\r
544 elseif addquotes then
\r
545 FormatValue=oSqlCompat.addQuotes(v)
\r
552 Private function FormatFormValue(idx as Integer) as String
\r
554 if IsNothing(columns(idx).EntryType) then exit function
\r
555 if columns(idx).EntryType="H" or columns(idx).FormView="exclude" then
\r
556 v=columns(idx).ColData
\r
558 v=trim(Request.Form(ExtFieldId(idx)))
\r
560 FormatFormValue=FormatValue(v,idx)
\r
564 Private Function AltTableJoinClause(AltTabIdx as Integer) as String
\r
565 dim i as Integer, Condition as String
\r
566 for i=0 to columns.Count-1
\r
567 if columns(i).TableIdx=MainTbl and columns(i).isKey then
\r
568 AddCondition(Condition, Tables(MainTbl).TblAlias & "." & columns(i).ColName & "=" & Tables(AltTabIdx).TblAlias & "." & columns(i).ColName)
\r
571 for i=0 to Tables(AltTabIdx).altFields.Count-1
\r
572 if Tables(AltTabIdx).altFields(i).isKey then
\r
573 AddCondition(Condition, Tables(AltTabIdx).FieldCondition(i))
\r
576 AltTableJoinClause=Condition
\r
580 ' -------------------------------------------------------------
\r
581 ' Add a condition to a where or having clause
\r
582 ' -------------------------------------------------------------
\r
583 Public Sub AddCondition(ByRef WhereClause as String, ByVal NewCondition as String)
\r
584 if IsNothing(NewCondition) then exit sub
\r
585 If IsNothing(WhereClause) Then
\r
586 WhereClause = "(" & NewCondition & ")"
\r
588 WhereClause &= " AND (" & NewCondition & ")"
\r
593 ' -------------------------------------------------------------
\r
594 ' Return the form value of a column based on its column name (insert or update action)
\r
595 ' -------------------------------------------------------------
\r
596 Public Function FormValue(ByVal SearchName as String) as String
\r
597 Dim idx as Integer = ColIndex(SearchName)
\r
601 FormValue = FormatFormValue(idx)
\r
606 ' -------------------------------------------------------------
\r
607 ' Return the index of a column based on its column name, or -1 if not found
\r
608 ' -------------------------------------------------------------
\r
609 Public Function ColIndex(ByVal SearchName as String) as Integer
\r
612 for i=0 to columns.Count-1
\r
613 if columns(i).ColName=SearchName then
\r
621 ' -------------------------------------------------------------
\r
622 ' Return the a column object based on its column name, or Nothing if not found
\r
623 ' -------------------------------------------------------------
\r
624 Public Function getColumnByName(ByVal SearchName as String) as GridColumn
\r
626 for i=0 to columns.Count-1
\r
627 if columns(i).ColName=SearchName then
\r
628 getColumnByName=columns(i)
\r
635 ' -------------------------------------------------------------
\r
636 ' Return the a column object based on its heading, or Nothing if not found
\r
637 ' -------------------------------------------------------------
\r
638 Public Function getColumnByHeading(ByVal SearchName as String) as GridColumn
\r
640 for i=0 to columns.Count-1
\r
641 if columns(i).Heading=SearchName then
\r
642 getColumnByHeading=columns(i)
\r
649 ' -------------------------------------------------------------
\r
650 ' Return the index of a table based on its table name
\r
651 ' -------------------------------------------------------------
\r
652 Public Function TabIndex(ByVal SearchName as String)
\r
654 for i=0 to Tables.Count-1
\r
655 if Tables(i).TblName=SearchName then
\r
663 ' -------------------------------------------------------------
\r
664 ' Return the index of the new filter
\r
665 ' -------------------------------------------------------------
\r
666 Public Function AddFilter(ByVal newfilter as String)
\r
667 AddFilter=_sqlFilters.Count
\r
668 _sqlFilters.Add(newfilter)
\r
672 ' form where clause based on table's primary key
\r
673 Public function MainTableKeyWhereClause() as String
\r
674 MainTableKeyWhereClause = TableKeyWhereClause(MainTbl)
\r
678 ' form where clause based on table's primary key
\r
679 Private function TableKeyWhereClause(TabIdx as Integer) as String
\r
680 dim i as Integer, w as String
\r
681 for i=0 to columns.Count-1
\r
682 if (columns(i).TableIdx=MainTbl or columns(i).TableIdx=TabIdx) and IsNothing(columns(i).Formula) and columns(i).isKey then
\r
683 AddCondition(w, columns(i).ColName & "=" & FormatValue(trim(Request.Form("_k" & i)),i))
\r
686 if IsNothing(w) then
\r
687 'Throw New Exception("no key value")
\r
689 TableKeyWhereClause=" WHERE " & w
\r
694 Protected Sub GetColumnInfo()
\r
695 dim t as Integer, r as Integer, c as Integer, colname as String, schemaTable As DataTable
\r
696 Dim restrictions(3) As String
\r
697 if IsNothing(Me.dbConnection) then exit sub
\r
698 for t=0 to Tables.Count-1
\r
699 if debug then DebugString &= "<p>Table: " & Tables(t).TblName & " tblidx=" & t & " colcnt=" & columns.Count
\r
701 Dim command = Me.dbConnection.CreateCommand()
\r
702 command.CommandText = "select * from " & Tables(t).TblName
\r
703 dim rdr = command.ExecuteReader(CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly)
\r
704 schemaTable = rdr.GetSchemaTable()
\r
705 For Each colinfo As DataRow In schemaTable.Rows
\r
706 colname = colinfo("ColumnName").ToString
\r
707 for c=0 to columns.Count-1
\r
708 if t=columns(c).TableIdx and colname=columns(c).ColName then
\r
710 .isNullable=CBool(colinfo("AllowDBNull"))
\r
711 .TypeName=replace(colinfo("DataType").ToString(),"System.","")
\r
712 if .TypeName<>"String" AndAlso not IsDBNull(colinfo("NumericPrecision")) AndAlso colinfo("NumericPrecision")<>0 then
\r
713 .Length=colinfo("NumericPrecision")
\r
714 elseif not IsDBNull(colinfo("ColumnSize")) then
\r
715 .Length=colinfo("ColumnSize")
\r
717 .Writeable=not colinfo("IsReadOnly")
\r
718 .isKey=colinfo("IsKey")
\r
719 'columns(c).FixedLength=((colinfo("COLUMN_FLAGS") and &H0000010) <> 0)
\r
720 if debug then DebugString &= "<br> Column: " & colname & " type=" & .TypeName & " len=" & .Length & " nullable=" & .isNullable & " isKey=" & .isKey
\r
726 for c=0 to Tables(t).altFields.Count-1
\r
727 if colname=Tables(t).altFields(c).ColName then
\r
728 Tables(t).altFields(c).isKey=colinfo("IsKey")
\r
735 ' AllowDBNull is not accurate when using Jet driver
\r
736 if InStr(Me.dbConnection.ConnectionString,"Microsoft.Jet") > 0 then
\r
737 restrictions(2)=Tables(t).TblName
\r
738 schemaTable = Me.dbConnection.GetSchema("Columns",restrictions)
\r
739 For Each colinfo As DataRow In schemaTable.Rows
\r
740 colname = colinfo("column_name").ToString
\r
741 for c=0 to columns.Count-1
\r
742 if t=columns(c).TableIdx and colname=columns(c).ColName then
\r
744 .isNullable=CBool(colinfo("is_nullable"))
\r
745 if debug then DebugString &= "<br> Column: " & colname & " nullable=" & .isNullable
\r
756 Protected Function UpdateDatabase(sqltext as String, actiontxt as String) as String
\r
758 if IsNothing(Me.dbConnection) then
\r
759 UpdateDatabase="<p>ERROR: no database connection</p>"
\r
762 Dim command = Me.dbConnection.CreateCommand()
\r
763 command.CommandText = sqltext
\r
764 cnt=command.ExecuteNonQuery()
\r
765 UpdateDatabase="<p class='ricoFormResponse " & actiontxt & "Successfully'></p>"
\r
766 Catch ex As Exception
\r
767 dim msg="<p>ERROR: unable to update database - " & server.HTMLencode(ex.Message.ToString()) & "</p>"
\r
768 if LogSqlOnError then msg &= "<br>" & server.HTMLencode(sqltext)
\r
771 'if debug then msg &= " - " & sqltext & " - Records affected: " & cnt
\r
776 Public Sub DeleteRecord(writer as HTMLTextWriter)
\r
777 dim sqltext as String = "DELETE FROM " & Tables(MainTbl).TblName & TableKeyWhereClause(MainTbl)
\r
778 writer.WriteLine(UpdateDatabase(sqltext, "deleted"))
\r
782 Public Sub InsertRecord(writer as HTMLTextWriter)
\r
783 dim i as Integer, keyIdx as Integer
\r
784 dim keyCnt as Integer=0
\r
785 dim sqlcol as String=""
\r
786 dim sqlval as String=""
\r
787 for i=0 to columns.Count-1
\r
788 if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).UpdateOnly=false then
\r
789 if columns(i).isKey then
\r
793 if columns(i).Writeable then
\r
794 sqlcol &= "," & columns(i).ColName
\r
795 sqlval &= "," & FormatFormValue(i)
\r
799 if IsNothing(sqlcol) then
\r
800 writer.WriteLine("<p>Nothing to add</p>")
\r
802 dim sqltext as String="insert into " & Tables(MainTbl).TblName & " (" & mid(sqlcol,2) & ") values (" & mid(sqlval,2) & ")"
\r
803 dim updateMsg as String = UpdateDatabase(sqltext, "added")
\r
804 writer.WriteLine(updateMsg)
\r
809 Public Sub UpdateRecord(writer as HTMLTextWriter)
\r
810 dim i as Integer, sqltext as String, errmsg as String=""
\r
811 for i=0 to Tables.Count-1
\r
812 if i<>MainTbl then errmsg &= UpdateAltTableRecords(i)
\r
815 writer.WriteLine("<p>" & errmsg & "</p>")
\r
818 for i=0 to columns.Count-1
\r
819 if columns(i).TableIdx=MainTbl and not IsNothing(columns(i).EntryType) and columns(i).Writeable and columns(i).InsertOnly=false then
\r
820 sqltext &= "," & columns(i).ColName & "=" & FormatFormValue(i)
\r
823 if not IsNothing(sqltext) then
\r
824 sqltext="UPDATE " & Tables(MainTbl).TblName & " SET " & mid(sqltext,2) & TableKeyWhereClause(MainTbl)
\r
825 writer.WriteLine(UpdateDatabase(sqltext, "updated"))
\r
826 elseif Tables.Count > 1 then
\r
827 ' only updated altTable records
\r
828 writer.WriteLine("<p class='ricoFormResponse updatedSuccessfully'></p>")
\r
830 writer.WriteLine("<p>Nothing to update</p>")
\r
835 Private function UpdateAltTableRecords(tabidx as Integer)
\r
836 dim j as Integer, cnt as Integer
\r
837 dim sqltext as String, colnames as String, coldata as String
\r
838 dim whereClause as String, errmsg as String
\r
840 ' check for existing record
\r
842 whereClause = TableKeyWhereClause(tabidx) & Tables(tabidx).KeyCondition()
\r
843 sqltext="select count(*) from " & Tables(tabidx).TblName & " " & Tables(tabidx).TblAlias & whereClause
\r
845 Dim command = Me.dbConnection.CreateCommand()
\r
846 command.CommandText = sqltext
\r
847 cnt=command.ExecuteScalar()
\r
848 Catch ex As Exception
\r
849 errmsg = "UpdateAltTableRecords Count<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
850 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
851 UpdateAltTableRecords = errmsg
\r
857 ' insert new record
\r
861 for j=0 to columns.Count-1
\r
862 if (columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType)) or columns(j).isKey then
\r
863 colnames &= "," & columns(j).ColName
\r
864 coldata &= "," & FormatFormValue(j)
\r
867 for j=0 to Tables(tabidx).altFields.Count-1
\r
868 colnames &= "," & Tables(tabidx).altFields(j).ColName
\r
869 coldata &= "," & Tables(tabidx).altFields(j).Data
\r
872 Dim command = Me.dbConnection.CreateCommand()
\r
873 sqltext="insert into " & Tables(tabidx).TblName & " (" & mid(colnames,2) & ") values (" & mid(coldata,2) & ")"
\r
874 command.CommandText = sqltext
\r
875 cnt=command.ExecuteNonQuery()
\r
876 Catch ex As Exception
\r
877 errmsg="UpdateAltTableRecords Insert<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
878 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
879 UpdateAltTableRecords = errmsg
\r
887 for j=0 to columns.Count-1
\r
888 if columns(j).TableIdx=tabidx and not IsNothing(columns(j).EntryType) then
\r
889 sqltext &= "," & columns(j).ColName & "=" & FormatFormValue(j)
\r
892 for j=0 to Tables(tabidx).altFields.Count-1
\r
893 sqltext &= "," & Tables(tabidx).altFields(j).ColName & "=" & Tables(tabidx).altFields(j).Data
\r
895 if sqltext <> "" then
\r
897 Dim command = Me.dbConnection.CreateCommand()
\r
898 sqltext="update " & Tables(tabidx).TblName & " set " & mid(sqltext,2) & whereClause
\r
899 command.CommandText = sqltext
\r
900 cnt=command.ExecuteNonQuery()
\r
901 Catch ex As Exception
\r
902 errmsg="UpdateAltTableRecords Update<br>ERROR: " & server.HTMLencode(ex.Message.ToString())
\r
903 if LogSqlOnError then errmsg &= "<br>" & sqltext
\r
904 UpdateAltTableRecords = errmsg
\r
911 ' -------------------------------------
\r
912 ' form main sql query to populate the grid
\r
913 ' -------------------------------------
\r
914 Protected Sub FormSqlQuery()
\r
915 Dim oParseLookup=new sqlParse
\r
916 Dim oParseSubQry=new sqlParse
\r
920 Dim tabidx as Integer
\r
921 Dim csvPrimaryKey as String
\r
922 if debug then DebugString &= "<p>FormSqlQuery"
\r
923 oParseMain.FromClause=Tables(MainTbl).TblName & " t"
\r
924 for i=0 to Tables.Count-1
\r
926 s="left join " & Tables(i).TblName & " " & Tables(i).TblAlias & " ON " & AltTableJoinClause(i)
\r
927 oParseMain.AddJoin(s)
\r
930 oParseMain.AddWhereCondition(TableFilter)
\r
932 ' build sql for each column
\r
934 for i=0 to columns.Count-1
\r
935 if columns(i).TableIdx>=0 then tabidx=columns(i).TableIdx
\r
936 if columns(i).FilterFlag then
\r
937 ' add any column filters to where clause
\r
938 oParseMain.AddWhereCondition(Tables(tabidx).TblAlias & "." & columns(i).ColName & "='" & columns(i).ColData & "'")
\r
941 if not IsNothing(columns(i).Formula) then
\r
945 oParseMain.AddColumn("(" & columns(i).Formula & ")", columns(i).Heading)
\r
947 elseif tabidx=MainTbl then
\r
949 ' column from main table - avoid subqueries to make it compatible with MS Access & MySQL < v4.1
\r
951 if columns(i).isKey then
\r
952 if not IsNothing(csvPrimaryKey) then csvPrimaryKey &= ","
\r
953 csvPrimaryKey &= Tables(tabidx).TblAlias & "." & columns(i).ColName
\r
955 if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then
\r
956 Dim TblAlias as String="t" & CStr(i)
\r
957 s=replace(columns(i).SelectSql,"%alias%",TblAlias & ".")
\r
958 oParseLookup.ParseSelect(s)
\r
959 if oParseLookup.SelectList.count=2 then
\r
960 Dim codeField as String=oParseLookup.SelectList(0).sql
\r
961 Dim descField as String=oParseLookup.SelectList(1).sql
\r
962 If IsFieldName(descField) Then
\r
963 descField=TblAlias & "." & descField
\r
965 descField=replace(replace(descField,"%alias%",TblAlias & "."),"%aliasmain%","t.")
\r
967 s="left join " & oParseLookup.FromClause & " " & TblAlias & " on t." & columns(i).ColName & "=" & TblAlias & "." & replace(replace(codeField,"%alias%",""),"%aliasmain%","")
\r
968 if not IsNothing(oParseLookup.WhereClause) then s &= " and " & replace(oParseLookup.WhereClause,"%alias%",TblAlias & ".")
\r
969 oParseMain.AddJoin(s)
\r
970 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)
\r
971 if IsNothing(columns(i).DescriptionCol) then
\r
972 columns(i+1).Formula=descField
\r
976 Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")
\r
979 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)
\r
984 ' column from alt table - no avoiding subqueries here
\r
986 if columns(i).isLookupField() and not IsNothing(columns(i).SelectSql) then
\r
987 oParseLookup.ParseSelect(columns(i).SelectSql)
\r
988 if oParseLookup.SelectList.count=2 then
\r
989 Dim descQuery as String="select " & oParseLookup.SelectList(1).sql & " from " & oParseLookup.FromClause & " where " & _
\r
990 oParseLookup.SelectList(0).sql & "=" & Tables(tabidx).TblAlias & "." & columns(i).ColName
\r
991 if not IsNothing(oParseLookup.WhereClause) then descQuery=descQuery & " and " & oParseLookup.WhereClause
\r
992 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName)
\r
993 columns(i+1).Formula="(" & descQuery & ")"
\r
995 Throw New Exception("Invalid lookup query (" & columns(i).SelectSql & ")")
\r
998 oParseMain.AddColumn(Tables(tabidx).TblAlias & "." & columns(i).ColName, columns(i).Heading)
\r
1003 if not IsNothing(columns(i).EntryType) then
\r
1004 Dim SessionColId as String = ExtFieldId(i)
\r
1005 if InStr("CSNR",left(columns(i).EntryType,1)) > 0 then
\r
1006 if not IsNothing(columns(i).SelectSql) then
\r
1007 s=columns(i).SelectSql
\r
1008 if not IsNothing(columns(i).SelectFilter) then
\r
1009 oParseLookup.ParseSelect(s)
\r
1010 oParseLookup.AddWhereCondition(columns(i).SelectFilter)
\r
1011 s=oParseLookup.UnparseSelect
\r
1013 oParseMain.SelectList(i).LookupQuery=replace(replace(s,"%alias%",""),"%aliasmain%","")
\r
1015 oParseMain.SelectList(i).LookupQuery="select distinct " & columns(i).ColName & " from " & Tables(tabidx).TblName & " where " & columns(i).ColName & " is not null"
\r
1020 if not IsNothing(DefaultSort) then
\r
1021 oParseMain.AddSort(DefaultSort)
\r
1022 elseif not IsNothing(csvPrimaryKey) then
\r
1023 oParseMain.AddSort(csvPrimaryKey)
\r
1028 Protected Overrides Sub OnPreRender(ByVal e As System.EventArgs)
\r
1029 MyBase.OnPreRender(e)
\r
1030 if not IsNothing(dbConnection) then
\r
1031 oSqlCompat=New sqlCompatibilty(dbDialect)
\r
1036 for i=0 to columns.Count-1
\r
1037 if not IsNothing(columns(i).AltTable) then
\r
1038 columns(i).TableIdx=TabIndex(columns(i).AltTable)
\r
1040 columns(i).TableIdx=MainTbl
\r
1042 Dim cell as New TableHeaderCell()
\r
1043 cell.Text=columns(i).Heading
\r
1044 if i<frozenColumns then cell.CssClass="ricoFrozen"
\r
1045 LiveGridHeadingsMain.Controls.Add(cell)
\r
1048 if BufferType="AjaxXML" or not sessions then Me.DisplayTimer=false
\r
1049 if Tables.count > 0 then
\r
1050 Me.GetColumnInfo()
\r
1051 if _action="table" and sessions then
\r
1053 session.contents(Me.UniqueId)=oParseMain
\r
1054 session.contents(Me.UniqueId & ".filters")=Me._sqlFilters
\r
1055 elseif _action="query" then
\r
1060 ' populate globalInitScript
\r
1061 Dim FixedGridScript as String = ""
\r
1062 Dim VarGridScript as String = ""
\r
1063 Dim gblFormView as Boolean = false
\r
1064 If Not Page.IsStartupScriptRegistered("LiveGridInit") Then
\r
1065 For Each ctrl As Control In Page.Controls
\r
1066 If TypeOf(ctrl) is LiveGrid then
\r
1067 if CType(ctrl,LiveGrid).Rows >= 0 then
\r
1068 FixedGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf
\r
1070 VarGridScript &= " " & ctrl.UniqueId & "_init" & "();" & vbCrLf
\r
1072 if CType(ctrl,LiveGrid).formView then gblFormView=true
\r
1075 'globalInitScript = "Rico.acceptLanguage('" & Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") & "');" & vbCrLf
\r
1076 if not UsingMinRico then
\r
1077 globalInitScript &= "Rico.loadModule('LiveGridAjax','LiveGridMenu');" & vbCrLf
\r
1078 if gblFormView then globalInitScript &= "Rico.loadModule('LiveGridForms');" & vbCrLf
\r
1080 globalInitScript &= "Rico.onLoad( function() {" & vbCrLf
\r
1081 globalInitScript &= FixedGridScript ' initialize grids with fixed # of rows first
\r
1082 globalInitScript &= VarGridScript & "});" & vbCrLf ' then initialize grids with variable # of rows
\r
1083 Page.RegisterStartupScript("LiveGridInit", "")
\r
1088 Public Class GridContainer
\r
1090 Implements INamingContainer
\r