Domino R5中动态查询是一件麻烦事,尤其是在生成动态报表时,更是棘手。大多查询是固定条件,不可变更的View。虽然,Domino 有Search, FTSearch功能,但是Search, FTSearch检索的结果处理,如格式化输出,按某个关键字排序等仍然很麻烦。
通常是采用Form中嵌入View来生成报表,这样的报表都是固定格式的,随着用户的需求增加,用户数量增多,就对报表产生了不同需求,希望报表能够按需动态生成。为此采取以下方案:
一、 概述
用Form中嵌入View作为基础,在Form中添加列选项功能,选中的列以XMLHTTP Post 方式发送给后台的Agent,Agent将查询列的数据返回,Client端得到Agent返回的数据,用JavaScript插入到View中。如下图:
Server 端处理:
二、 创建存储数据Form
我们需要创建一个Test.nsf,在Form工作区创建两个Form,一个存储数据Test Form,别名frm_Test,拥有10个域,fld_FirstName、fld_MiddleNsme、fld_LastName、fld_ID、fld_Country、fld_Birthday、fld_Age、fld_Sex、fld_Phone、fld_JoinDate。
三、 创建View
创建View—vw_ReportTest,条件:Select Form=”frm_Test”,做成列:ID –fld_ID、FirstName –fld_ FirstName、MiddleNsme –fld_ MiddleNsme、LastName –fld_ LastName、Age –fld_ Age、Sex –fld_ Sex。还要在最后追加一个Title为空,公式为:"[<input type=hidden name=_HiddenDocID value=" + @Text(@DocumentUniqueID) + ">]"的隐藏列。
四、创建表示用Form
一个表示用frm_DisplayTest,拥有一个隐藏域SelectItem,一个Button—选择显示列,一个<Div id=view></Div>,一个嵌入View—vw_ReportTest,将嵌入View放在Div中。
五、创建TestMapping.xml
<?xml version="1.0"?>
<NSF name='Test'>
<Form name= “Test Form” alias=” frm_Test”>
<Item name=” Country” > fld_Country </Item>
<Item name=” Birthday” > fld_Birthday </Item>
<Item name= “Age “> fld_Age </Item>
<Item name= “Phone “> fld_Phone </Item>
<Item name= “JoinDate “> fld_JoinDate </Item>
<Item name= “Created “> Created </Item>
<Item name= “Updated “>LastModified </Item>
</Form>
</NSF>
六、创建Agent
创建一个名为XMLHTTPTest的共享的、通过Command执行的Agent,内容如下:
Const FORMNAME="frm_Test"
%Include “nsfmapping.lss
Sub Initialize
On Error Goto bottom
Dim se As New NotesSession
Dim db As Notesdatabase
Dim doc As NotesDocument
Dim xmlText As String
Dim strRTN As String
Dim fn As String
Set db=se.CurrentDatabase
Set doc = se.DocumentContext
xmlText = Cstr(doc.Request_Content(0))
Dim xml
Set xml = CreateObject("Msxml2.DOMDocument")
xml.loadXML(xmlText)
Dim root
Set root = xml.documentElement
Dim node
Dim nodeList
' Get All Items
fn = se.GetEnvironmentString("Directory",True) & "/" & Strleft(db.FilePath, db.FileName) & Strleft(db.FileName, ".") & "Mapping.xml"
Set nodeList = root.getElementsByTagName("Item")
Call GetMapping(fn, FORMNAME, nodeList)
' Get All Documents
Set nodeList = root.getElementsByTagName("Doc")
Dim docID
Dim ItemValue
Print |<Root>|
strRTN =""
For i = 1 To nodeList.length
Set node = nodeList.Item(i-1)
docID = node.Attributes.getNamedItem("id").Text
Let strRTN = strRTN + {<Doc id="} + docID + {">}
Set doc = db.GetDocumentByUNID(docID)
For j=1 To Ubound(SelectList)
If SelectList(j).ItemName<>"" Then
ItemValue = Cstr(doc.GetFirstItem(SelectList(j).DBName).Values(0))
Let strRTN = strRTN + {<Item name="} + SelectList(j).ItemName + {" value="} + ItemValue + {"/>}
End If
Next
Let strRTN = strRTN + {</Doc>}
If (i Mod 50=0) Then
Print strRTN
strRTN = ""
End If
Next
Print strRTN
Print |</Root>|
bottom:
Exit Sub
End Sub
Include 文件用于Mapping处理,将显示的Title转换为Form中的域名,如下:
《nsfmapping.lss》
Public Type ListItem
ItemName As String
DBName As String
End Type
Public SelectList() As ListItem
Sub GetMapping(xmlPath As String, FormName As String, ItemList As Variant)
On Error Goto bottom
Dim index As Integer
index =1
Dim xml
Set xml = CreateObject("Msxml2.DOMDocument")
xml.Load (xmlPath)
Dim root
Set root = xml.documentElement
Dim node
Dim nodeList
Set nodeList = root.getElementsByTagName("Form")
Dim tempName As String
Dim tempAlias As String
Dim i, j
Dim tempItem1 As String
Dim tempItem2 As String
Redim SelectList(ItemList.length)
For i = 1 To nodeList.length
Set node = nodeList.Item(i-1)
If (node.nodeType = 1 And node.hasChildNodes()) Then
tempName = Ucase(node.Attributes.getNamedItem("name").Text)
tempAlias = Ucase(node.Attributes.getNamedItem("alias").Text)
Msgbox("tempAlias = " & tempAlias)
If (tempName = Ucase(FormName) Or tempAlias = Ucase(FormName)) Then
Set nodeList = node.getElementsByTagName("Item")
Forall Item In ItemList
tempItem2 = Item.Attributes.getNamedItem("name").Text
For j=1 To nodeList.length
Set node = nodeList.Item(j-1)
tempItem1 = Ucase(node.Attributes.getNamedItem("name").Text)
If tempItem1=Ucase(tempItem2) Then
SelectList(index).ItemName = tempItem2
SelectList(index).DBName = node.Text
index = index + 1
Exit For
End If
Next
End Forall
Exit For
End If
End If
Next
bottom:
Exit Sub
End Sub
Client 端处理
七、JavaScript
<script language="javascript">
function SetParam() {
var colArr = null;
var seletItem = document.all.SelectItem.value;
if (seletItem==null || seletItem=="") return;
colArr = seletItem.split(";");
// Create request XML
var reqxml = "<Root>";
for (var index=0; index < colArr.length; index++) {
if (colArr[index] !="")
reqxml += "<Item name='" + colArr[index] + "'/>";
}
var DocArr = document.getElementsByName("_HiddenDocID");
for (i=0; i <DocArr.length; i++) {
if (DocArr[i].value !="")
reqxml += "<Doc id='" + DocArr[i].value + "'/>";
}
reqxml +="</Root>";
var strURL = "" + window.location;
strURL = strURL.substr(0, strURL.toUpperCase().indexOf(".NSF")+4) + "/XMLHTTPTest?OpenAgent";
// request and response
var resXML = SendXML(strURL, reqxml);
// Parse XML and get root Element
ParseXML(resXML);
// Add Columns
for(var index=0; index < colArr.length; index++) {
AddTabColumn(colArr[index]);
}
}
function SendXML(strURL,xml) {
var strRTN = ""
try {
var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
xmlhttp.Open("POST",strURL,false);
xmlhttp.Send(xml);
strRTN = xmlhttp.responseText;
} catch(E) { }
return strRTN;
}
function AddTabColumn(colTitle) {
var view = document.all.view;
var tab = null;
var rowCnt = 0;
var colCnt = 0;
var colCnt2 = 0;
var newObj = null;
var th = null;
var tr = null;
var td = null;
var DocID = "";
if (view.hasChildNodes()) {
tab = view.getElementsByTagName("table")(0);
rowCnt = tab.rows.length;
colCnt = tab.rows(0).cells.length;
for (row =0; row < rowCnt; row++) {
tr = tab.rows(row)
if (row==0) {
newObj = document.createElement("TH");
th = tr.cells(colCnt-1).insertAdjacentElement("BeforeBegin",newObj);
th.noWrap = true;
th.innerText = colTitle;
} else {
colCnt2 = tab.rows(row).cells.length;
if (colCnt == colCnt2) {
cell = tab.rows(row).cells(colCnt-1);
if (cell.hasChildNodes()) {
DocID = cell.childNodes(0).value;
td = tr.insertCell(colCnt-1);
td.noWrap = true;
td.innerText = GetItemValue(DocID, colTitle);
} else {
td = tr.insertCell(colCnt-1);
td.noWrap = true;
}
} else {
tab.rows(row).cells(0).colSpan = tab.rows(row).cells(0).colSpan + 1;
}
}
}
}
}
/ Parse XML /
var root = null;
function ParseXML(XMLText) {
var oXMLDom = new ActiveXObject("Microsoft.XMLDOM");
oXMLDom.async = false;
if (XMLText !=null && XMLText != "") {
oXMLDom.loadXML(XMLText);
}
var errCode = oXMLDom.parseError
if (errCode != 0) {
alert("Parse ResXML Error:/n Line: " + oXMLDom.parseError.line + " Column: " + oXMLDom.parseError.linepos + "/n Reason: " + oXMLDom.parseError.reason);
return null;
}
var nodeList = oXMLDom.getElementsByTagName("Root");
root = nodeList.item(0);
return root;
}
function FindSpecificDoc(DocID){
if (root==null) return null;
var nodeList = root.getElementsByTagName("Doc");
var tmpID = "";
var Doc = null;
for (i=0; i<nodeList.length; i++) {
Doc = nodeList.item(i);
tmpID = Doc.getAttributeNode("id").nodeValue;
if (tmpID.toUpperCase()==DocID.toUpperCase()) return Doc;
}
return null;
}
function GetItemValue(DocID, ItemName){
var Doc = FindSpecificDoc(DocID);
var item = null;
var tmpName = "";
var strRTN = "";
if (Doc !== null) {
var items = Doc.getElementsByTagName("Item");
for (j=0; j< items.length; j++) {
item = items.item(j);
tmpName = item.getAttributeNode("name").nodeValue;
if (tmpName.toUpperCase()==ItemName.toUpperCase()) {
strRTN = item.getAttributeNode("value").nodeValue;
}
}
}
return strRTN;
}
</script>
八、Request、Response、Mapping文件格式
(1) ReqXML格式
一个跟元素<Root>,包含<Item>和<Doc>,元素<Item>和<Doc>都可以是0到多个。元素<Item>有属性name;元素< Doc >有属性id,name和id都不要为空。
<Root>
<Item name=’XX’/>
<Item name=’XX’/>
.......
<Doc id=’YY’/>
<Doc id=’YY’/>
<Doc id=’YY’/>
<Doc id=’YY’/>
.........
</Root>
(2) Res格式
一个跟元素<Root>,包含0到多个<Doc>元素,每个<Doc>元素都包含1到多个<Item>元素。元素<Item>有属性name、value;元素< Doc >有属性id,name和id都不要为空。
<Root>
<Doc id='XX'>
<Item name='YY' value='VVVVV'/>
<Item name='ZZ' value='VVVVV'/>
................
</Doc>
<Doc id='XX'>
<Item name='YY' value='VVVVV'/>
<Item name='ZZ' value='VVVVV'/>
................
</Doc>
....................
</Root>
(3) Mapping格式
一个NSF文件只有一个Mapping文件与之对应,Mapping文件名为NSF文件名+”Mapping.xml”,位于同一目录下。
<?xml version="1.0"?>
<NSF name='dbname'>
<Form name='f1' alias='f11'>
<Item name='t1'>dbfu1</Item>
<Item name='t2'>dbfu2</Item>
<Item name='t3'>dbfu3</Item>
........
</Form>
<Form name='f2' alias='f22'>
<Item name='t1'>dbfu1</Item>
<Item name='t2'>dbfu2</Item>
<Item name='t3'>dbfu3</Item>
........
</Form>
................
</NSF>