frmcountanalyze.frm

一套简易的MIS系统。带SQLServer数据库。供参考。

        cmbCust.Enabled = False
        chkLX.Value = 0
    End If
    
End Sub

Private Sub chkGoods_Click()
    
    If chkGoods.Value = 1 Then
        chkOpr.Value = 0
        cmbOprInfo.Enabled = False
        cmbGoods.Enabled = True
        
'        chkCash.Value = 0
        chkGB.Value = 0
        chkJS.Value = 0
        
        chkCust.Value = 0
        cmbCust.Enabled = False
        chkLX.Value = 0
    Else
        cmbGoods.Enabled = False
    End If
    
End Sub

Private Sub chkJS_Click()
    
    If chkJS.Value = 1 Then
        chkOpr.Value = 0
        cmbOprInfo.Enabled = False
        
        chkGoods.Value = 0
        cmbGoods.Enabled = False
        
'        chkCash.Value = 0
        
        chkCust.Value = 0
        cmbCust.Enabled = False
        chkLX.Value = 0
    End If
    
End Sub

Private Sub chkLX_Click()
    
    If chkLX.Value = 1 Then
        chkOpr.Value = 0
        cmbOprInfo.Enabled = False
        
        chkGoods.Value = 0
        cmbGoods.Enabled = False
        
'        chkCash.Value = 0
        chkGB.Value = 0
        chkJS.Value = 0
        
        chkCust.Value = 0
        cmbCust.Enabled = False
    End If
    
End Sub

Private Sub chkOpr_Click()
    
    If chkOpr.Value = 1 Then
        cmbOprInfo.Enabled = True
        chkGoods.Value = 0
        cmbGoods.Enabled = False
        
'        chkCash.Value = 0
        chkGB.Value = 0
        chkJS.Value = 0
        
        chkCust.Value = 0
        cmbCust.Enabled = False
        chkLX.Value = 0
    Else
        cmbOprInfo.Enabled = False
    End If
    
End Sub

Private Sub cmdClose_Click()
    
    '返回
    tBackMain Me
     
End Sub

Private Sub cmdOK_Click()
    
    On Error GoTo ErrInfo
    
    If getSQLString = False Then MsgBox "条件错误！请检查条件", vbInformation, "提示：": Exit Sub
    getHeadList iCountFlag
    
    getDataList strCountTab, iCountFlag
    
    SSTab1.Tab = 0
    
    Exit Sub
ErrInfo:
    MsgBox Err.Description, vbInformation, "提示："
    
End Sub

Private Sub Form_Load()

    '计算窗体显示位置
    tFormSpace frmMain, Me, uWindows
    
    '显示基础数据
    Call getBaseInfo
    
    bDate.Value = Format(tServerDate, "YYYY年MM月DD日")
    eDate.Value = Format(tServerDate, "YYYY年MM月DD日")
    
    Call getControlEnable
    
    '首次显示时显示列头
    getHeadList 0

End Sub

Private Sub getBaseInfo()
    
    Dim rsTemp                     As New ADODB.Recordset
    
    Set rsTemp = DBCN.Execute("Select Cust_ID,Cust_Name From tbccCust Where Instate=0 Order By Cust_ID")
    
    If rsTemp.EOF = False Then
        cmbCust.Clear
        cmbCust.AddItem "全部"
        Do Until rsTemp.EOF
            cmbCust.AddItem rsTemp.Fields("Cust_name") & "[" & rsTemp.Fields("Cust_ID") & "]"
            rsTemp.MoveNext
        Loop
        cmbCust.Text = cmbCust.List(0)
    Else
        cmbCust.Clear
        cmbCust.AddItem "全部"
        cmbCust.Text = cmbCust.List(0)
    End If
    
    Set rsTemp = DBCN.Execute("Select Oper_ID,Oper_Name From TbCCOper Where Instate=0 Order By Oper_ID")
    
    If rsTemp.EOF = False Then
        cmbOprInfo.Clear
        cmbOprInfo.AddItem "全部"
        Do Until rsTemp.EOF
            cmbOprInfo.AddItem rsTemp.Fields("Oper_Name") & "[" & rsTemp.Fields("Oper_ID") & "]"
            rsTemp.MoveNext
        Loop
        cmbOprInfo.Text = cmbOprInfo.List(0)
    Else
        cmbOprInfo.Clear
        cmbOprInfo.AddItem "全部"
        cmbOprInfo.Text = cmbCust.List(0)
    End If
    
    Set rsTemp = DBCN.Execute("Select Goods_ID,Goods_Name From tbccGoods Where Instate=0 Order By Goods_ID")
    
    If rsTemp.EOF = False Then
        cmbGoods.Clear
        cmbGoods.AddItem "全部"
        Do Until rsTemp.EOF
            cmbGoods.AddItem rsTemp.Fields("Goods_Name") & "[" & rsTemp.Fields("Goods_ID") & "]"
            rsTemp.MoveNext
        Loop
        cmbGoods.Text = cmbGoods.List(0)
    Else
        cmbGoods.Clear
        cmbGoods.AddItem "全部"
        cmbGoods.Text = cmbCust.List(0)
    End If
        
End Sub

'设置控件无效
Private Sub getControlEnable()
    
    chkOpr.Value = 0
    cmbOprInfo.Enabled = False
    
    chkGoods.Value = 0
    cmbGoods.Enabled = False
    
'    chkCash.Value = 0
    chkGB.Value = 0
    chkJS.Value = 0
    
    chkCust.Value = 0
    cmbCust.Enabled = False
    
    chkLX.Value = 0
    
End Sub

'依据条件
Private Function getSQLString() As Boolean
    
    Dim strSQL                      As String
    Dim isSQL                       As Boolean
    getSQLString = False
    If chkOpr.Value = 1 Then
        getSQLString = True
        iCountFlag = 0
        strCountTab = "tbcc_OprCountList"
        If InStr(cmbOprInfo.Text, "[") <> 0 Then
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Sum(Ticket_Weigh) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Weigh) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Weigh) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '" & tString(cmbOprInfo.Text, "[", "]", 1) & "%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        Else
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Sum(Ticket_Weigh) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Weigh) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Weigh) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '%%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        End If
        '建立临时表
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCount') Drop Table tbcc_OprCount"
        DBCN.Execute strSQL
        
        '整理数据
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCountList') Drop Table tbcc_OprCountList"
        DBCN.Execute "Select Oper_ID,Sum(iCount) AS iCount,Sum(iWeight) as iWeight,Sum(iCount1) As iCount1, " _
                    & " Sum(iChange) As iChange,Sum(iCount2) AS iCount2,Sum(iCash) AS iCash " _
                    & " Into tbcc_OprCountList From tbcc_OprCount Group By Oper_ID Order By Oper_ID"
    End If

    If chkGoods.Value = 1 Then
        getSQLString = True
        iCountFlag = 1
        strCountTab = "tbcc_OprCountList"
        If InStr(cmbGoods.Text, "[") <> 0 Then
            strSQL = "Select Goods_ID,(Case Instate When 0 Then Sum(Ticket_Weigh) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Weigh) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Weigh) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Goods_ID Like '" & tString(cmbOprInfo.Text, "[", "]", 1) & "%' " _
                    & " Group By Goods_ID,Instate Order By Goods_ID"
        Else
            strSQL = "Select Goods_ID,(Case Instate When 0 Then Sum(Ticket_Weigh) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Weigh) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Weigh) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Goods_ID Like '%%' " _
                    & " Group By Goods_ID,Instate Order By Goods_ID"
        End If
        '建立临时表
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCount') Drop Table tbcc_OprCount"
        DBCN.Execute strSQL
        
        '整理数据
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCountList') Drop Table tbcc_OprCountList"
        DBCN.Execute "Select Goods_ID,Sum(iCount) AS iCount,Sum(iWeight) as iWeight,Sum(iCount1) As iCount1, " _
                    & " Sum(iChange) As iChange,Sum(iCount2) AS iCount2,Sum(iCash) AS iCash " _
                    & " Into tbcc_OprCountList From tbcc_OprCount Group By Goods_ID Order By Goods_ID"
    End If
    
    If chkGB.Value = 1 Then
        getSQLString = True
        iCountFlag = 2
        strCountTab = "tbcc_OprCountList"
        If InStr(cmbGoods.Text, "[") <> 0 Then
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '" & tString(cmbOprInfo.Text, "[", "]", 1) & "%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        Else
            strSQL = "Select Oper_ID,(Case Instate When 0 Then Count(Ticket_id) Else 0 End) as iCount, " _
                    & " (Case Instate When 0 Then Sum(Ticket_Pay) Else 0 End) as iWeight, " _
                    & " (Case Instate When 2 Then Count(Ticket_id) Else 0 End) as iCount1, " _
                    & " (Case Instate When 2 Then Sum(Ticket_Pay) Else 0 End) as iChange, " _
                    & " (Case Instate When -1 Then Count(Ticket_id) Else 0 End) as iCount2, " _
                    & " (Case Instate When -1 Then Sum(Ticket_Pay) Else 0 End) AS iCash " _
                    & " Into tbcc_OprCount From tbCcTicket Where Convert(Varchar(10),Ticket_Date,120) Between '" & Format(bDate.Value, "yyyy-mm-dd") & "' And " _
                    & " '" & Format(eDate.Value, "yyyy-mm-dd") & "' And Oper_ID Like '%%' " _
                    & " Group By Oper_ID,Instate Order By Oper_ID"
        End If
        '建立临时表
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCount') Drop Table tbcc_OprCount"
        DBCN.Execute strSQL
        
        '整理数据
        DBCN.Execute "If Exists(Select * from sysObjects Where Name ='tbcc_OprCountList') Drop Table tbcc_OprCountList"
        DBCN.Execute "Select Oper_ID,Sum(iCount) AS iCount,Sum(iWeight) as iWeight,Sum(iCount1) As iCount1, " _