frmyh_yhdzcx.frm

一个用VB写的财务软件源码

   End
End
Attribute VB_Name = "frmYH_Yhdzcx"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'2001.01.22肖兆芹修改
Option Explicit

'以下一段常量用于窗体frm_print的CELL打印
'-------------------------------------------------------------
'Cell单元格对齐方式: 33 = 左对齐, 34 = 右对齐, 36 = 居中对齐;

'银行对账单
Const ROWS_PAGE_YH = 30               '每页行数

Const COL_START_YH = 1                 '开始列
Const COL_RQ = 1                       '日期
Const COL_JSFS_YH = 2                  '结算方式
Const COL_BILL_NUMBER_YH = 3           '票号
Const COL_DEBIT_MONEY_YH = 4           '借方金额
Const COL_CREDIT_MONEY_YH = 5          '贷方金额
Const COL_LQBZ_YH = 6                  '两清标志
Const COL_ZY = 7                       '摘要
Const COL_END_YH = 7                   '结束列

'缺省列宽
'CELL  1单元长度 = 3.8mm
Const COLWIDTH_YH = "85,70,100,140,140,40,140"


'单位日记账
Const ROWS_PAGE_DW = 30                '每页行数

Const COL_START_DW = 1                 '开始列
Const COL_PZRQ = 1                     '凭证日期
Const COL_BILL_DATE = 2                '票据日期
Const COL_JSFS_DW = 3                  '结算方式
Const COL_BILL_NUMBER_DW = 4           '票号
Const COL_DEBIT_MONEY_DW = 5           '借方金额
Const COL_CREDIT_MONEY_DW = 6          '贷方金额
Const COL_LQBZ_DW = 7                  '两清标志
Const COL_PZZL = 8                     '凭证种类
Const COL_PZBH = 9                     '凭证编号
Const COL_PZZY = 10                     '凭证摘要
Const COL_END_DW = 10                   '结束列

'缺省列宽
'CELL  1单元长度 = 3.8mm
Const COLWIDTH_DW = "85,85,100,80,140,140,40,80,80,200"


Const ROW_TITLE = 1                 '标题
Const ROW_BLANK = 2                 '空白行
Const ROW_SUBJNAME = 3              '页眉
Const ROW_HEAD1 = 4                 '页标头行
Const ROW_GRID_START = 5            '表格开始行

Const CRB_LINE = vbBlack            '表格线颜色


Dim sEnterName As String                            '单位名称
Dim IsChangeCurrentTable As Boolean                 '是否改变当前表格中的内容
Dim frmP As frmPrint                                '通用打印窗体(CELL)
'-------------------------------------------------------------


Dim rstTemp As ADODB.Recordset
Dim sSQL As String
Dim sSQLYhdzd As String
Dim sSQLDwrjz As String
Dim Yhdzqyrq As String                      '银行对账启用日期
Dim i As Integer
Dim j As Integer
Dim aKmdm() As String                       '存放银行对账的科目
Dim oldKmdmListIndex As Integer             '上一次的科目代码列表框的行索引号
Dim oldViewListIndex As Integer             '上一次显示条件的行索引号
Dim KmdmSelect As String                    '当前选中的科目代码
Dim sQueryStr As String                     '查询条件字符串
Dim bFormload As Boolean                    '当前是否在窗体的引导状态
Dim bTheSameYear As String                  '0 -- 注册年份小于银行对账启用年份
                                            '1 -- 注册年份等于银行对账启用年份
                                            '2 -- 注册年份大于银行对账启用年份

'科目选择改变
Private Sub cboKmmc_Click()
    Dim s As String
    If Not bFormload Then
        If oldKmdmListIndex <> cboKmmc.ListIndex Then
            Me.Refresh
            Me.MousePointer = vbHourglass
            oldKmdmListIndex = cboKmmc.ListIndex
            IsChangeCurrentTable = True
            KmdmSelect = aKmdm(cboKmmc.ListIndex)
            
            '''''''''''''
            If GetKmWbdw(KmdmSelect) = "" Then
                s = "je"
            Else
                s = "wb"
            End If
            '''''''''''''''
            
            '从银行对账启用日期表中取出银行对账启用日期
            Set rstTemp = New ADODB.Recordset
            rstTemp.CursorLocation = adUseClient
            sSQL = "SELECT * FROM tZW_Yhdzqyrq WHERE kmdm = '" & KmdmSelect & "'"
            rstTemp.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
            With rstTemp
                If .RecordCount <> 0 Then
                    If Year(.Fields("qyrq").value) < CInt(glo.sOperateYear) Then
                        bTheSameYear = 0
                    ElseIf Year(.Fields("qyrq").value) = CInt(glo.sOperateYear) Then
                        bTheSameYear = 1
                    Else
                        bTheSameYear = 2
                    End If
                    Yhdzqyrq = Format(.Fields("qyrq").value, "yyyy-mm-dd")
                Else
                    bTheSameYear = 0
                End If
            End With
            
            sSQLYhdzd = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
                        " WHERE kmdm = '" & KmdmSelect & _
                        "' AND qcbz <> 0 AND hxbz = 0 ORDER BY rq,jsfsCode,Bill"
                        
            '如果当前注册年份和银行对账启用年份相同
            sQueryStr = ""
            If bTheSameYear = 1 Then
                sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                            "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
                            " AND kjqj <= 12)" & " OR kjqj = 21)" & _
                            " AND yhdz_hxbz = 0"
            ElseIf bTheSameYear = 2 Then
                sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                            "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)" & _
                            " AND yhdz_hxbz = 0"
            End If
            
            sSQLDwrjz = "SELECT pzzl,pzbh,pzrq,pzzy,fx," + s + " je,yhdz_jsfscode," & _
                        "yhdz_bill,yhdz_date,yhdz_lqbz" & _
                        " FROM tZW_Pzsj" & glo.sOperateYear & sQueryStr & _
                        " ORDER BY pzrq"
                        
            Call FillGridYhdzd(sSQLYhdzd)
            Call FillGridDwrjz(sSQLDwrjz)
'==================================2002.8.22 yao add=====================================
            cboViewselect.ListIndex = 0
'========================================================================================
            Call cboViewselect_Click
            Me.MousePointer = vbDefault
        End If
    End If
End Sub

'显示条件改变
Private Sub cboViewselect_Click()
    Dim sYhSql As String
    Dim sDwSql As String
    Dim sQueryStr As String
    Dim dTotalYhJfje As Double                  '银行方合计借方金额
    Dim dTotalYhDfje As Double                  '银行方合计贷方金额
    Dim dTotalDwJfje As Double                  '单位方合计借方金额
    Dim dTotalDwDfje As Double                  '单位方合计贷方金额
    Dim i As Long
    Dim s As String
    
    If Not bFormload Then
        If oldViewListIndex <> cboViewselect.ListIndex Then
            oldViewListIndex = cboViewselect.ListIndex
            IsChangeCurrentTable = True
        Else
            oldViewListIndex = cboViewselect.ListIndex
            Exit Sub
        End If
         If GetKmWbdw(KmdmSelect) = "" Then
            s = "je"
        Else
            s = "wb"
        End If
        If cboViewselect.text = "全部显示" Then
            sYhSql = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
                   " WHERE kmdm = '" & KmdmSelect & _
                   "' AND qcbz <> 0  ORDER BY rq,jsfsCode,Bill"
                
            '如果当前注册年份和银行对账启用年份相同
               sQueryStr = ""
               If bTheSameYear = 1 Then
                  sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                            "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
                            " AND kjqj <= 12)" & " OR kjqj = 21)"
                           
               ElseIf bTheSameYear = 2 Then
                    sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                            "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)"
               End If
               sDwSql = "SELECT pzzl,pzbh,pzrq,pzzy,fx," + s + " je,yhdz_jsfscode," & _
                        "yhdz_bill,yhdz_date,yhdz_lqbz" & _
                        " FROM tZW_Pzsj" & glo.sOperateYear & sQueryStr & _
                        " ORDER BY pzrq"
        
             Call FillGridYhdzd(sYhSql)
             Call FillGridDwrjz(sDwSql)
            
        ElseIf cboViewselect.text = "显示已达账" Then
 '==================================2002.8.22 yao revise=========================================
        
'                   sYhSql = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
'                            " WHERE kmdm = '" & KmdmSelect & _
'                            "' AND qcbz <> 0 AND hxbz <> 0 ORDER BY rq,jsfsCode,Bill"
                    
                    sYhSql = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
                            " WHERE kmdm = '" & KmdmSelect & _
                            "' AND qcbz <> 0 AND ((lqbz=1 or lqbz=2) and hxbz=0) ORDER BY rq,jsfsCode,Bill"
                
                        '如果当前注册年份和银行对账启用年份相同
                   sQueryStr = ""
                   If bTheSameYear = 1 Then
'                        sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
'                                "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
'                                " AND kjqj <= 12)" & " OR kjqj = 21)" & _
'                                " AND yhdz_hxbz <>0"
                       sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                                "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
                                " AND kjqj <= 12)" & " OR kjqj = 21)" & _
                                " AND (yhdz_hxbz <>0 or yhdz_lqbz=1 or yhdz_lqbz=2)"
                   ElseIf bTheSameYear = 2 Then
'                        sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
'                                "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)" & _
'                                " AND yhdz_hxbz <> 0 "
                        sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                                "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)" & _
                                " AND (yhdz_hxbz <> 0 or yhdz_lqbz=1 or yhdz_lqbz=2)"
                   End If
'================================================================================================
                    sDwSql = "SELECT pzzl,pzbh,pzrq,pzzy,fx," + s + " je,yhdz_jsfscode," & _
                            "yhdz_bill,yhdz_date,yhdz_lqbz" & _
                            " FROM tZW_Pzsj" & glo.sOperateYear & sQueryStr & _
                            " ORDER BY pzrq"
                  Call FillGridYhdzd(sYhSql)
                  Call FillGridDwrjz(sDwSql)
          
        Else
            '显示未达账
'=========================================2002.8.22 yao revise====================================================

'                sYhSql = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
'                         " WHERE kmdm = '" & KmdmSelect & _
'                         "' AND qcbz <> 0 AND hxbz = 0 ORDER BY rq,jsfsCode,Bill"
                 sYhSql = "SELECT * FROM tZW_Yhdzd" & glo.sOperateYear & _
                         " WHERE kmdm = '" & KmdmSelect & _
                         "' AND qcbz <> 0 AND ( lqbz is null) and hxbz=0 ORDER BY rq,jsfsCode,Bill"
                   
                           '如果当前注册年份和银行对账启用年份相同
                      sQueryStr = ""
                      If bTheSameYear = 1 Then
'                         sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
'                                   "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
'                                   " AND kjqj <= 12)" & " OR kjqj = 21)" & _
'                                   " AND yhdz_hxbz =0"
                          sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                                   "' AND ((kjqj >= " & Month(CDate(Yhdzqyrq)) & _
                                   " AND kjqj <= 12)" & " OR kjqj = 21)" & _
                                   " AND (rtrim(yhdz_lqbz)=0 or yhdz_lqbz is null) and yhdz_hxbz=0"
                      ElseIf bTheSameYear = 2 Then
'                           sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
'                                   "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)" & _
'                                   " AND yhdz_hxbz = 0"
                           sQueryStr = " WHERE kmdm = '" & KmdmSelect & _
                                   "' AND ((kjqj >= 1 AND kjqj <= 12)" & " OR kjqj = 21)" & _
                                   " AND yhdz_hxbz = 0  and (rtrim(yhdz_lqbz)=0 or yhdz_lqbz is null)"
                      End If
                   
                    sDwSql = "SELECT pzzl,pzbh,pzrq,pzzy,fx," + s + " je,yhdz_jsfscode," & _
                               "yhdz_bill,yhdz_date,yhdz_lqbz" & _
                               " FROM tZW_Pzsj" & glo.sOperateYear & sQueryStr & _
                               " ORDER BY pzrq"
'================================================================================================================
                   Call FillGridYhdzd(sYhSql)
                   Call FillGridDwrjz(sDwSql)
        
        End If
    End If
End Sub

Private Sub form_load()
    Dim curIndex As Integer
    Dim s As String
    fMainForm.MousePointer = vbHourglass
    IsChangeCurrentTable = True
    bFormload = True
    
    Set rstTemp = New ADODB.Recordset
    rstTemp.CursorLocation = adUseClient
    '从科目表中取出是银行对账科目
    sSQL = "SELECT kmdm,kmmc FROM tZW_km" & glo.sOperateYear & _
            " WHERE IsYhz =-1 and IsEndKm =-1" & _
            " ORDER BY kmdm"
    rstTemp.Open sSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
    With rstTemp
        If .RecordCount <> 0 Then
            ReDim aKmdm(.RecordCount)
            .MoveFirst
            curIndex = 0
            Do Until .EOF