Actuate报表开发总结
如何测量性能
1 选择包含报表数据源的数据流组件。
2 以“双精度”数据类型的形式创建变量 Start_Time。
3 在 Start( ) 方法中键入以下代码:
Super::Start( )
ShowFactoryStatus(“Starting report”)
Start_Time = Now
End Function
4 在 Finish( ) 方法中键入以下代码:
Sub Finish( )
Dim Finish_Time as Double , Elaspsed_Time as Double
Dim Duration as String
Super::Finish( )
Finish_time = Now ( )
Elapsed_Time = Finish_Time - Start_Time
Elapsed_Time = Elapsed_Time * ( 24 * 3600 )
Elapsed_Time = CInt (Elapsed_Time)
Duration = CStr (Elapsed_Time)
ShowFactoryStatus(“Elapsed time (seconds): “ + Duration)
End Sub
查看检索的sql语句
在DataStream中修改ObtainSelectStatement( ) 函数
Function ObtainSelectStatement( ) As String
WhereClause = WhereClause & " AND ( " & where & " ) "
FromClause = parseSchema(FromClause)
ObtainSelectStatement = Super::ObtainSelectStatement( )
' Insert your code here
' showfactorystatus(ObtainSelectStatement )
End Function
操作数据库方面
首先在DataStream中建立变量如下:
aConnection AcDBConnection
aCursor AcDBCursor
aDataSource AcDatabaseSource
aRow AcDataRow
aStmt AcDBStatement
在DataStream->Start( ) 中加入以下代码(DataRow2 为当前DataStream对应的DataRow):
Function Start( ) As Boolean
Start = Super::Start( )
' Insert your code here
Set aConnection = new Connection
aConnection.connect()
Set aRow = New DataRow2
End Function
现测试,建立一个Function,检索数据
Function GetGccb(awoum as String ) As String
' Insert your code here
Dim str as String
str = " SELECT WPLABOR.LABORCODE from " & Schema & " .wplabor, " & Schema & " .labor "
str = str & " where wplabor.laborcode=labor.laborcode and wplabor.wonum=' " & awoum & " ' "
' ShowFactoryStatus(str)
set aStmt = aConnection.prepare(str)
if dbstmt is nothing then
exit function
end if
set aCursor = dbStmt.allocateCursor()
if aCursor is nothing then
exit function
end if
if Not aCursor.openCursor() then
aDataSource.getDBConnection().raiseError()
set aCursor = nothing
end if
aCursor.BindColumn( 1 , " NewReportApp::DataRow2 " , " namesum_wplabor " )
aCursor.Fetch(aRow)
GetGccb = aRow.GetValue( " namesum_wplabor " )
' ShowFactoryStatus(" "&getGccb)
End Function
或者建立两个function
Function SetSql( str As String ) As AcDBCursor
' Insert your code here
' 数据库操作
set aStmt = aConnection.prepare(str)
if aStmt is nothing then
exit function
end if
set aCursor = aStmt.allocateCursor()
if aCursor is nothing then
exit function
end if
if Not aCursor.openCursor() then
aDataSource.getDBConnection().raiseError()
set aCursor = nothing
end if
Set SetSql = aCursor
End Function
Function GetGccb(awoum as String ) As String
' Insert your code here
' 检索数据
Dim str as String
str = " SELECT WPLABOR.LABORCODE from " & Schema & " .wplabor, " & Schema & " .labor "
str = str & " where wplabor.laborcode=labor.laborcode and wplabor.wonum=' " & awoum & " ' "
' ShowFactoryStatus(str)
Set aCursor = SetSql(astr)
aCursor.BindColumn( 1 , " NewReportApp::DataRow2 " , " namesum_wplabor " )
aCursor.Fetch(aRow)
GetGccb = aRow.GetValue( " namesum_wplabor " )
' ShowFactoryStatus(" "&getGccb)
End Function
修改数据库代码如下:
Function UpdateMat( num as String ) As Double
Dim stmt1 As AcDBStatement
Dim stmt2 As AcDBStatement
Dim conn As AcDBConnection
Dim str as String
str = " UPDATE " & schema & " .matrectrans "
str = str & " SET matrectrans.it6 = 'Y' "
str = str & " WHERE ponum = ' " & num & " ' "
' ShowFactoryStatus(str)
Set conn = new Connection
conn.connect()
Set stmt1 = New AcDBStatement(conn)
stmt1.prepare(str)
stmt1.execute()
Set stmt2 = New AcDBStatement(conn)
stmt2.prepare( " commit " )
stmt2.execute()
End Function
每页统计数据
功能需求:每页显示四条记录,每页统计一次[MATRECTRANS.LINECOST]字段
解决如下:
在报表Tool->Parameters中定义变量:
Sum1 Double
在DataStream中建立变量如下:
aConnection AcDBConnection
aCursor AcDBCursor
aDataSource AcDatabaseSource
aRow AcDataRow
aStmt AcDBStatement
tmp1 Integer '记录总数与4取余的值
tmp2 Integer
i1 Integer
新建函数
Function SetSql( str As String ) As AcDBCursor
' Insert your code here
' 数据库操作
set aStmt = aConnection.prepare(str)
if aStmt is nothing then
exit function
end if
set aCursor = aStmt.allocateCursor()
if aCursor is nothing then
exit function
end if
if Not aCursor.openCursor() then
aDataSource.getDBConnection().raiseError()
set aCursor = nothing
end if
Set SetSql = aCursor
End Function
Function GetSum(code as String ,xend as Integer ) As Double
' Insert your code here
' 统计前xend的总数
Dim str as String
str = " select Sum(matrectrans.LINECOST) from " & Schema & " .matrectrans "
str = str & " where MATRECTRANS.ITIN1 = '工程采购' and matrectrans.ponum=' " & CODE & " ' "
str = str & " and rownum<= " & xend & " "
str = str & " and TRANSDATE >to_date(' " & aStart & " ','YYYY-MM-DD') "
str = str & " and TRANSDATE <to_date(' " & aEnd & " ','YYYY-MM-DD') "
' showfactorystatus(str)
Set aCursor = SetSql(str)
aCursor.BindColumn( 1 , " NewReportApp::DataRow3 " , " sum_1 " )
aCursor.Fetch(aRow)
GetSum = aRow.GetValue( " sum_1 " )
End Function
Function GetCount( code as String ) As Integer
' Insert your code here
Dim str as String
str = " select count(*) from " & Schema & " .matrectrans "
str = str & " where MATRECTRANS.ITIN1 = '工程采购' and matrectrans.ponum=' " & CODE & " ' "
str = str & " and TRANSDATE >to_date(' " & aStart & " ','YYYY-MM-DD') "
str = str & " and TRANSDATE <to_date(' " & aEnd & " ','YYYY-MM-DD') "
' showfactorystatus(str)
Set aCursor = SetSql(str)
aCursor.BindColumn( 1 , " NewReportApp::DataRow3 " , " count_1 " )
aCursor.Fetch(aRow)
GetCount = aRow.GetValue( " count_1 " )
End Function
修改Start( )
Function Start( ) As Boolean
Start = Super::Start( )
' Insert your code here
Dim count1,i as Integer
Set aConnection = new Connection
aConnection.connect()
Set aRow = New DataRow3
count1 = GetCount(code) ' 求列数
if (count1 mod 4 ) >= 1 then
tmp1 = 4 - (count1 mod 4 )
tmp2 = 1
else
tmp2 = 5
end if
i1 = 0
' showfactorystatus("tmp1 "&tmp1)
’此处赋最后一页的总数
if count1 < 4 then
sum3 = GetSum(code, 4 )
' showfactorystatus("sum3 "&sum3)
elseif (count1 mod 4 ) <> 0 then
i = count1 mod 4
sum3 = GetSum(code,count1) - GetSum(code,count1 - i)
' showfactorystatus("sum3 "&sum3)
elseif (count1 mod 4 ) = 0 then
sum3 = GetSum(code,count1) - GetSum(code,count1 - 4 )
end if
End Function
修改Fetch( )
Function Fetch( ) As AcDataRow
' Set Fetch = Super::Fetch( )
' Insert your code here
Dim myrow as DataRow3
Dim ponum as String
Dim i2 as Integer
Dim count1,i as Integer
Set myrow = Super::Fetch()
if ( not myrow is nothing ) then
ponum = myrow.GetValue( " PO_PONUM " )
' 计算总计数,每页总计
if i1 < 4 then
i1 = i1 + 1
' showfactorystatus("**i1 "&i1 &"RowNumber "&myrow.GetValue("RowNumber"))
elseif i1 = 4 then
i2 = myrow.GetValue( " RowNumber " )
sum1 = GetSum(ponum,i2 - 1 ) - GetSum(ponum,i2 - 5 )
i1 = 1
' showfactorystatus("sum1 "&sum1)
' showfactorystatus("i1 "&i1 &"RowNumber "&myrow.GetValue("RowNumber"))
end if
elseif (myrow is nothing ) and tmp2 <= tmp1 then
Set myrow = new DataRow3
' showfactorystatus(code)
myrow.SetValue( " PO_PONUM " ,code)
myrow.SetValue( " MATRECTRANS_QUANTITY " , null )
myrow.SetValue( " MATRECTRANS_ACTUALCOST " , null )
myrow.SetValue( " MATRECTRANS_LINECOST " , null )
myrow.SetValue( " deptment_wappr " , "" )
myrow.SetValue( " name_wappr " , "" )
myrow.SetValue( " name_appr " , "" )
myrow.SetValue( " name_enterby " , "" )
tmp2 = tmp2 + 1
end if
Set Fetch = myrow