banreport.cls

金算盘软件代码

   Set rstChoosed = gclsBase.BaseDB.OpenResultset(strSql & strOrder, rdOpenStatic)
   With rstChoosed
       If .EOF Then
          GetReportTdSet = ""
          Exit Function
       End If
       .MoveLast
       .MoveFirst
       
       Columns = .RowCount

       mvarFixColumns = 0
       mvarGridTop = !intGridTop
       For intCount = 1 To .RowCount

            mvarColumnDesc(intCount) = !strReportFieldDesc
            mvarColumnFieldName(intCount) = IIf(IsNull(!strFieldName), "", !strFieldName)
            mstrColumnGroup(intCount) = !strGroup
            lngWidth = !lngWidth
            mvarColumnWidth(intCount) = lngWidth
            mvarColumnOrderType(intCount) = !bytsort
            mvarColumnIsFix(intCount) = !blnIsFixed
            mvarColumnIsMust(intCount) = !blnIsMust
            If !blnIsFixed Then
                 mvarFixColumns = mvarFixColumns + 1
            End If
            mvarColumnIsFind(intCount) = !blnIsFind
            mvarColumnFieldID(intCount) = .rdoColumns("lngViewFieldID")

            mvarColumnFieldType(intCount) = !strFieldType
            mvarColumnFieldSize(intCount) = !bytFieldSize
            mvarColumnCombine(intCount) = IIf(IsNull(!strCombine), "", !strCombine)
            
            .MoveNext
       Next intCount
   End With
End Function

'取当前报表设置
Public Function GetReportSet(ByVal lngReportID As Long, ByVal ReportViewID As Long, Optional bytCurType As Byte = 1) As String
   Dim rstChoosed As rdoResultset
   Dim strSql As String
   Dim strCond As String, strOrder As String, strCondVersion As String
   Dim intCount As Integer, lngWidth As Long
   
   strCondVersion = GetVersionCond
   
   strSql = "Select * from ViewField,Report,ReportField Where ViewField.lngViewFieldID=ReportField.lngViewFieldID " & _
            "And Report.lngReportID=ReportField.lngReportID And Report.lngReportID=" & lngReportID & _
            " And ReportField.blnIsChoosed=1 And ViewField.blnIsChoose=1 " & strCondVersion
   strOrder = " Order By lngReportFieldNO"
   Set rstChoosed = gclsBase.BaseDB.OpenResultset(strSql & strOrder, rdOpenStatic)
   With rstChoosed
       If .EOF Then
          GetReportSet = ""
          Exit Function
       End If
       .MoveLast
       .MoveFirst
       
       If ViewId = 0 Then
            ViewId = ReportViewID
            ReportID = lngReportID
       End If
       Columns = .RowCount
       mintUserCols = Columns
       mvarFixColumns = 0
       mvarGridTop = !intGridTop
       mlngColType = IIf(IsNull(!lngColType), 0, !lngColType)
       For intCount = 1 To .RowCount
            mvarColumnDesc(intCount) = !strReportFieldDesc
            mstrColumnGroup(intCount) = !strGroup
            mvarColumnIsMust(intCount) = !blnIsMust
            mvarColumnFieldName(intCount) = IIf(IsNull(!strFieldName), "", !strFieldName)
            
            '?If !lngDisplayWidth = 0 Or IsNull(!lngDisplayWidth) Then(此句自动配置宽度)
            If IsNull(!lngDisplayWidth) Then
                lngWidth = Utility.GetDisplayWidth(!strViewFieldDesc, !bytFieldSize)
            Else
                lngWidth = !lngDisplayWidth
            End If
            mvarColumnWidth(intCount) = lngWidth
            
            mvarColumnOrderType(intCount) = !bytsort
            mvarColumnIsFix(intCount) = !blnIsFixed
            mvarColumnIsMust(intCount) = !blnIsMust
            If !blnIsFixed Then
                 mvarFixColumns = mvarFixColumns + 1
            End If
            mvarColumnIsFind(intCount) = !blnIsFind
            mblnColumnMayChoose(intCount) = .rdoColumns("blnIsChoose")
            mvarColumnFieldID(intCount) = .rdoColumns("lngViewFieldID")
            mvarColumnFieldType(intCount) = !strFieldType
            mvarColumnFieldSize(intCount) = !bytFieldSize
            mvarColumnCombine(intCount) = IIf(IsNull(!strCombine), "", !strCombine)
            
            .MoveNext
       Next intCount
   End With
   
   GetDataField bytCurType
   
   strSql = "Select bytCodeShow,intCondTop,intCondLeft,intCondWidth,intCondHeight,intCondAlign,bytCondShow,intTitleAlign,intAlign,bytHead,lngDisplayTop,lngDisplayLeft,lngDisplayHeight,lngDisplayWidth," & _
            "intTitleTop,intTitleLeft,intTitleWidth,intTitleHeight," & _
            "ReportField.lngViewFieldID As ID,ViewField.strViewFieldDesc As Name " & _
            "From ViewField,Report,ReportField Where ViewField.lngViewFieldID=ReportField.lngViewFieldID " & _
            "And Report.lngReportID=ReportField.lngReportID And Report.lngReportID=" & lngReportID & _
            " And ReportField.blnIsHeaded=1 " & strCondVersion
   strOrder = " Order By lngReportFieldNO"
   Set rstChoosed = gclsBase.BaseDB.OpenResultset(strSql & strOrder, rdOpenStatic)
   With rstChoosed
        If Not .EOF Then
             .MoveLast
             .MoveFirst
             HeadFields = .RowCount
             mvarHeadTop(0) = IIf(IsNull(!intTitleTop), 0, !intTitleTop)
             mvarHeadLeft(0) = IIf(IsNull(!intTitleLeft), 0, !intTitleLeft)
             mvarHeadWidth(0) = IIf(IsNull(!intTitleWidth), 0, !intTitleWidth)
             mvarHeadHeight(0) = IIf(IsNull(!intTitleHeight), 0, !intTitleHeight)
             mvarHeadAlign(0) = IIf(IsNull(!intTitleAlign), 9, !intTitleAlign)
             
             mlngCondTop = IIf(IsNull(!intCondTop), 1000, !intCondTop)
             mlngCondLeft = IIf(IsNull(!intCondLeft), 100, !intCondLeft)
             mlngCondWidth = IIf(IsNull(!intCondWidth), 1000, !intCondWidth)
             mlngCondHeight = IIf(IsNull(!intCondHeight), 300, !intCondHeight)
             mlngCondAlign = IIf(IsNull(!intCondAlign), 9, !intCondAlign)
             mbytCondShow = IIf(IsNull(!bytCondShow), 2, !bytCondShow)
             
             For intCount = 1 To .RowCount
                 mvarHeadFieldId(intCount) = !ID
                 mvarHeadFieldName(intCount) = !Name
                 mvarHeadTop(intCount) = !lngdisplaytop
                 mvarHeadLeft(intCount) = !lngDisplayLeft
                 mvarHeadHeight(intCount) = !lngDisplayHeight
                 mvarHeadWidth(intCount) = !lngDisplayWidth
                 mvarHeadType(intCount) = !bytHead
                 mvarHeadAlign(intCount) = !intAlign
                 mbytCodeShowType(intCount) = !bytCodeShow
                 .MoveNext
             Next intCount
        Else
             strSql = "Select intTitleTop,intTitleLeft,intTitleWidth,intTitleHeight From Report Where lngReportID=" & lngReportID
             Set rstChoosed = gclsBase.BaseDB.OpenResultset(strSql, rdOpenStatic)
             With rstChoosed
                If Not .EOF Then
                    HeadFields = 0
                    mvarHeadTop(0) = !intTitleTop
                    mvarHeadLeft(0) = !intTitleLeft
                    mvarHeadWidth(0) = !intTitleWidth
                    mvarHeadHeight(0) = !intTitleHeight
                
                    mlngCondTop = IIf(IsNull(!intCondTop), 1000, !intCondTop)
                    mlngCondLeft = IIf(IsNull(!intCondLeft), 100, !intCondLeft)
                    mlngCondWidth = IIf(IsNull(!intCondWidth), 1000, !intCondWidth)
                    mlngCondHeight = IIf(IsNull(!intCondHeight), 300, !intCondHeight)
                    mlngCondAlign = IIf(IsNull(!intCondAlign), 9, !intCondAlign)
                    mbytCondShow = IIf(IsNull(!bytCondShow), 2, !bytCondShow)
                End If
             End With
        End If
   End With
   
   'mvarSelect = GetSelect
   GetReportSet = "Select " & mvarSelect & mvarFrom
End Function

Private Sub GetHeadColumn()
  Dim strSql As String, strOrder As String
  Dim rstHead As rdoResultset
  
   strSql = "Select * from ViewField,Report,ReportField Where ViewField.lngViewFieldID=ReportField.lngViewFieldID " & _
            "And Report.lngReportID=ReportField.lngReportID And Report.lngReportID=" & mvarReportID & _
            " And bytHead>0"
   strOrder = " Order By bytHead"
   Set rstHead = gclsBase.BaseDB.OpenResultset(strSql & strOrder, rdOpenStatic)

End Sub

'取 SELECT 子句
Public Function GetSelect(strGroup As String, arrGroupCol As Variant, lngCondType As Long, strGroupSel As String)
  Dim intCount As Integer, intRow As Integer
  Dim strSelect As String, strSql As String
  Dim rstViewField As rdoResultset
  Dim strName As String
  Dim blnAccount As Boolean
  
     For intCount = 1 To Columns
         strName = ColumnDesc(intCount)
         If strName = "科目编码" Or strName = "科目名称" Then
             blnAccount = True
         End If
     Next intCount
  
     intRow = 1
     For intCount = 1 To Columns
         strName = ColumnDesc(intCount)
         '** IF 1 **
         If blnAccount Then
            If Trim(ColumnFieldName(intCount)) <> "" Then
               If strSelect = "" Then
                   strSelect = ColumnFieldName(intCount) & " As " & strName
               Else
                   strSelect = strSelect & "," & ColumnFieldName(intCount) & " As " & strName
               End If
            Else
               If Trim(ColumnFieldName(intCount)) = "" Then
                   If strSelect = "" Then
                       strSelect = "0" & " As " & strName
                   Else
                       strSelect = strSelect & "," & "0" & " As " & strName
                   End If
               End If
            End If
         '** ELSE OF IF 1 **
         Else
            If strName = "方向" Then
                If strSelect = "" Then
                    strSelect = "'借' As " & strName
                Else
                    strSelect = strSelect & ",'借' As " & strName
                End If
            Else
                If Trim(ColumnFieldName(intCount)) <> "" Then
                   If strSelect = "" Then
                       strSelect = strReplace(ColumnFieldName(intCount), "+SUM", "-SUM") & " As " & strName
                   Else
                       strSelect = strSelect & "," & strReplace(ColumnFieldName(intCount), "+SUM", "-SUM") & " As " & strName
                   End If
                Else
                   If Trim(ColumnFieldName(intCount)) = "" Then
                       If strSelect = "" Then
                           strSelect = "0" & " As " & strName
                       Else
                           strSelect = strSelect & "," & "0" & " As " & strName
                       End If
                   End If
                End If
            End If
         '** END OF IF 1 **
         End If
         
         If Trim(mstrColumnGroup(intCount)) <> "" Then
             If InStr(1, strName, "科目") > 0 Then
                 lngCondType = lngCondType Or ctAccount
             End If
             If InStr(1, strName, "单位") > 0 Then
                 lngCondType = lngCondType Or ctcustomer
             End If
             If InStr(1, strName, "部门") > 0 Then
                 lngCondType = lngCondType Or ctDepartment
             End If
             If InStr(1, strName, "员工") > 0 Or InStr(1, strName, "职员") > 0 Then
                 lngCondType = lngCondType Or ctEmployee
             End If
             If InStr(1, strName, "统计") > 0 Then
                 lngCondType = lngCondType Or ctClass1
             End If
             If InStr(1, strName, "项目") > 0 Then
                 lngCondType = lngCondType Or ctClass2
             End If
             If InStr(1, strName, "工程") > 0 Then
                 lngCondType = lngCondType Or ctJob
             End If
             
             If blnAccount Then
                If strGroup = "" Then
                   strGroup = mstrColumnGroup(intCount)
                   strGroupSel = "LTrim(DECODE(" & mvarColumnFieldName(intCount) & ",NULL,''," & mvarColumnFieldName(intCount) & "))"
                Else
                   strGroup = strGroup & "," & mstrColumnGroup(intCount)
                   strGroupSel = strGroupSel & " || LTrim(DECODE(" & mvarColumnFieldName(intCount) & ",NULL,''," & mvarColumnFieldName(intCount) & "))"
                End If
             Else
                If strName <> "方向" Then
                    If strGroup = "" Then
                       strGroup = mstrColumnGroup(intCount)
                       strGroupSel = "LTrim(DECODE(" & mvarColumnFieldName(intCount) & ",NULL,''," & mvarColumnFieldName(intCount) & "))"
                    Else
                       strGroup = strGroup & "," & mstrColumnGroup(intCount)
                       strGroupSel = strGroupSel & " || LTrim(DECODE(" & mvarColumnFieldName(intCount) & ",NULL,''," & mvarColumnFieldName(intCount) & "))"
                    End If
                End If
             End If
             ReDim Preserve arrGroupCol(intRow)
             arrGroupCol(intRow) = intCount - 1
             intRow = intRow + 1
         End If
     Next intCount
     GetSelect = strSelect
End Function

Private Sub Class_Terminate()
    Erase mvarColumnWidth()
    Erase mvarColumnOrderType()
    Erase mvarColumnIsFix()
    Erase mvarColumnIsMust()
    Erase mvarColumnIsFind()
    Erase mblnColumnMayChoose()
    Erase mvarColumnFieldID()
    Erase mvarColumnFieldName()
    Erase mvarColumnDesc()
    Erase mstrColumnGroup()
    Erase mvarColumnFieldType()
    Erase mvarColumnFieldSize()
    Erase mvarColumnCombine()
End Sub