frm_custport.frm

ktv场所的包房开房、迎宾、预定管理系统。

      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   11.25
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _NumberOfBands  =   1
      _Band(0).Cols   =   2
   End
End
Attribute VB_Name = "frm_custport"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub comb_lx_Click()
   Dim TxtSQL As String
   Dim msgtext As String
   Dim mrc As ADODB.Recordset

    If Me.comb_lx.ListIndex = 1 Then
        TxtSQL = "select * from counterid where TableName='printsn'"
        Set mrc = ExecuteSQL(TxtSQL, msgtext)
        txt_ids(1) = mrc.Fields("CountNum")
        txt_ids(0) = mrc.Fields("precountnum")
        txt_ids(0).Enabled = False
        txt_ids(1).Enabled = False
    End If
    If Me.comb_lx.ListIndex = 3 Then
        txt_ids(0).Enabled = True
        txt_ids(1).Enabled = True
        txt_ids(0).SetFocus
    End If
    If Me.comb_lx.ListIndex = 0 Then
        TxtSQL = "select * from counterid where TableName='printsn'"
        Set mrc = ExecuteSQL(TxtSQL, msgtext)
        txt_ids(0) = mrc.Fields("CountNum") + 1
        TxtSQL = "select * from counterid where TableName='sale_id'"
        Set mrc = ExecuteSQL(TxtSQL, msgtext)
        txt_ids(1) = mrc.Fields("CountNum")
        txt_ids(1).Enabled = False
        txt_ids(0).Enabled = False
    End If
End Sub

Private Sub Command1_Click(Index As Integer)
   Dim TxtSQL As String
   Dim msgtext As String
   Dim mrc As ADODB.Recordset
    Dim roomnum As Integer
    Dim roomstr As String
    Dim BTarray(9) As Integer
    Dim recBT(9) As String
    Dim txt As New clsText
    Dim rpt As New report
    Dim recBT1(8) As String
    Select Case Index
        Case 0
            Select Case Me.comb_lx.ListIndex
                Case 0, 1, 3
                    TxtSQL = "select  c.employee_id,c.employee_name,sum(b.finalprice) as finalprice"
                    TxtSQL = TxtSQL & " from sale_head as a,sale as b,Employee as c"
                    TxtSQL = TxtSQL & " where a.sale_id=b.sale_id"
                    TxtSQL = TxtSQL & " and b.employee_id=c.employee_id"
                    TxtSQL = TxtSQL & " and a.sale_id>=" & Val(Me.txt_ids(0))
                    TxtSQL = TxtSQL & " and a.sale_id<=" & Val(Me.txt_ids(1))
                    TxtSQL = TxtSQL & " group by c.employee_id,c.employee_name"
                    TxtSQL = TxtSQL & " order by c.employee_id"
                Case 2
                    TxtSQL = "select  c.employee_id,c.employee_name,sum(b.finalprice) as finalprice"
                    TxtSQL = TxtSQL & " from sale_head as a,sale as b,Employee as c"
                    TxtSQL = TxtSQL & " where a.sale_id=b.sale_id"
                    TxtSQL = TxtSQL & " and b.employee_id=c.employee_id"
                    TxtSQL = TxtSQL & " and a.sale_date>=#" & Me.dtptime(0).Value & "#"
                    TxtSQL = TxtSQL & " and a.sale_date<=#" & Me.dtptime(1).Value & "#"
                    TxtSQL = TxtSQL & " group by c.employee_id,c.employee_name"
                    TxtSQL = TxtSQL & " order by c.employee_id"
            End Select
            Set mrc = ExecuteSQL(TxtSQL, msgtext)
            Set Me.msglist.DataSource = mrc
            showtitle
        Case 1
            rpt.SetPrinter 11905.488, 7370.064, Portrait
            '定义页首
            Set txt = New clsText
            With txt
                .stringX = struserinfoname & Space(22)
                .fontsize = 14
                .FontName = "黑体"
                .FontBold = True
                .FontUnderLine = True
                .Align = tyLeft
            End With
            rpt.Title.AddText "title1", txt
            Set txt = Nothing
                                               
            Set txt = New clsText
            With txt
                .stringX = "|第&p页/共&s页"
                .fontsize = 10
            End With
            rpt.Title.AddText "title2", txt
            Set txt = Nothing
            
            '定义表首
            Set txt = New clsText
            With txt
                .stringX = "员工销售报表"
                .fontsize = 13
                .FontBold = True
                .Align = tymiddle
            End With
            rpt.Header.AddText "head1", txt
            Set txt = Nothing
            
            Set txt = New clsText
            With txt
                .stringX = "帐单号从:" & Me.txt_ids(0) & "到" & Me.txt_ids(1)
                .fontsize = 10
                .Align = tyLeft
            End With
            rpt.Header.AddText "head2", txt
            Set txt = Nothing
            
            Set txt = New clsText
            With txt
                .stringX = "操作员:" & strCurUser & "|" & "打印日期:" & dteSysDate
                .fontsize = 10
                .Align = tyLeft
            End With
            rpt.Header.AddText "head3", txt
            Set txt = Nothing
                        
            rpt.LeftSection.AlignMode = tyContent
            rpt.RightSection.AlignMode = tyContent
            rpt.Align = tymiddle
            BTarray(1) = 1500
            BTarray(2) = 3000
            recBT(1) = "员工编号"
            recBT(2) = "金额"
            rpt.AttachFlexGrid msglist
            rpt.Preview
        Case 2
            rpt.SetPrinter 11905.488, 7370.064, Portrait
            '定义页首
            Set txt = New clsText
            With txt
                .stringX = struserinfoname & Space(40)
                .fontsize = 14
                .FontName = "黑体"
                .FontBold = True
                .FontUnderLine = True
                .Align = tyLeft
            End With
            rpt.Title.AddText "title1", txt
            Set txt = Nothing
                        
            Set txt = New clsText
            With txt
                .stringX = "|第&p页/共&s页"
                .fontsize = 10
            End With
            rpt.Title.AddText "title2", txt
            Set txt = Nothing
            
            '定义表首
            Set txt = New clsText
            With txt
                .stringX = "员工销售报表(产品销售统计)"
                .fontsize = 13
                .FontBold = True
                .Align = tymiddle
            End With
            rpt.Header.AddText "head1", txt
            Set txt = Nothing
            
            Set txt = New clsText
            With txt
                .stringX = "帐单号从:" & Me.txt_ids(0) & "到" & Me.txt_ids(1)
                .fontsize = 10
                .Align = tyLeft
            End With
            rpt.Header.AddText "head2", txt
            Set txt = Nothing
            
            Set txt = New clsText
            With txt
                .stringX = "操作员:" & strCurUser & "|" & "打印日期:" & dteSysDate
                .fontsize = 10
                .Align = tyLeft
            End With
            rpt.Header.AddText "head3", txt
            Set txt = Nothing
            
            rpt.LeftSection.AlignMode = tyContent
            rpt.RightSection.AlignMode = tyContent
            rpt.Align = tymiddle
            report = True
            rpt.AttachFlexGrid msglist1
            'rpt.AttachFlexGrid MSHFlexGrid1
            'rpt.ReadTemplate Left(App.Path, Len(App.Path)) & "\dllprint\rptkc.txt"
            rpt.Preview
        Case 3
            Unload Me
    End Select
End Sub

Private Sub Form_Load()
    Dim TxtSQL As String
    Dim msgtext As String
    Dim mrc As ADODB.Recordset
   
    comb_lx.AddItem "当班报表"
    comb_lx.AddItem "前班报表"
    comb_lx.AddItem "时间段报表"
    comb_lx.AddItem "帐单号报表"
    Me.dtptime(0).Value = dteSysDate
    Me.dtptime(1).Value = dteSysDate
    TxtSQL = "select * from counterid where TableName='printsn'"
    Set mrc = ExecuteSQL(TxtSQL, msgtext)
    txt_ids(0) = mrc.Fields("CountNum") + 1
    TxtSQL = "select * from counterid where TableName='sale_id'"
    Set mrc = ExecuteSQL(TxtSQL, msgtext)
    txt_ids(1) = mrc.Fields("CountNum")
    txt_ids(1).Enabled = False
    txt_ids(0).Enabled = False
    Me.comb_lx.ListIndex = 0
    Command1_Click (0)
End Sub

Private Sub msglist_Click()
    Dim TxtSQL As String
    Dim msgtext As String
    Dim mrc As ADODB.Recordset
    
    If msglist.row > 0 Then
        Select Case Me.comb_lx.ListIndex
            Case 0, 1, 3
                TxtSQL = "select  b.p_id,b.product_name,b.unit,avg(b.price) as pricee,sum(b.qty) as qtyy,sum(b.finalprice) as finalpricee"
                TxtSQL = TxtSQL & " from sale_head as a,sale as b"
                TxtSQL = TxtSQL & " where a.sale_id=b.sale_id"
                TxtSQL = TxtSQL & " and a.sale_id>=" & Val(Me.txt_ids(0))
                TxtSQL = TxtSQL & " and a.sale_id<=" & Val(Me.txt_ids(1))
                TxtSQL = TxtSQL & " and b.employee_id='" & Me.msglist.TextMatrix(msglist.row, 0) & "'"
                TxtSQL = TxtSQL & " group by b.p_id,b.product_name,b.unit"
                TxtSQL = TxtSQL & " order by b.p_id"
            Case 2
                TxtSQL = "select  b.p_id,b.product_name,b.unit,avg(b.price) as pricee,sum(b.qty) as qtyy,sum(b.finalprice) as finalpricee"
                TxtSQL = TxtSQL & " from sale_head as a,sale as b"
                TxtSQL = TxtSQL & " where a.sale_id=b.sale_id"
                TxtSQL = TxtSQL & " and a.sale_date>=#" & Me.dtptime(0).Value & "#"
                TxtSQL = TxtSQL & " and a.sale_date<=#" & Me.dtptime(1).Value & "#"
                TxtSQL = TxtSQL & " and b.employee_id='" & Me.msglist.TextMatrix(msglist.row, 0) & "'"
                TxtSQL = TxtSQL & " group by b.p_id,b.product_name,b.unit"
                TxtSQL = TxtSQL & " order by b.p_id"

        End Select
        Set mrc = ExecuteSQL(TxtSQL, msgtext)
        Set Me.msglist1.DataSource = mrc
        showtitle1
    End If
End Sub
Private Sub showtitle()
    Dim i As Integer
    
    With msglist
        .Cols = 3
          If .rows <= 2 Then
            .rows = 2
        End If
        .TextMatrix(0, 0) = "员工编号"
        .TextMatrix(0, 1) = "员工姓名"
        .TextMatrix(0, 2) = "金额"
        '设置各列的对齐方
        For i = 0 To 2
            .ColAlignment(i) = 2
        Next i
        '表头项居中
        .FillStyle = flexFillRepeat
        .col = 0
        .row = 0
        '.RowSel = 1
        .ColSel = .Cols - 1
        '.CellAlignment = 4
        '设置单元大小
        .colWidth(0) = 1200
        .colWidth(1) = 2500
        .colWidth(2) = 1000
        '.Row = 1
    End With
End Sub
Private Sub showtitle1()
    Dim i As Integer
    
    With msglist1
        .Cols = 6
      If .rows <= 2 Then
            .rows = 2
        End If
        .TextMatrix(0, 0) = "编号"
        .TextMatrix(0, 1) = "项目名称"
        .TextMatrix(0, 2) = "单位"
        .TextMatrix(0, 3) = "单价"
        .TextMatrix(0, 4) = "数量"
        .TextMatrix(0, 5) = "金额"
        '设置各列的对齐方
        For i = 0 To 5
            .ColAlignment(i) = 2
        Next i
        '表头项居中
        .FillStyle = flexFillRepeat
        .col = 0
        .row = 0
        '.RowSel = 1
        .ColSel = .Cols - 1
        '.CellAlignment = 4
        '设置单元大小
        .colWidth(0) = 1500
        .colWidth(1) = 3000
        .colWidth(2) = 1000
        .colWidth(3) = 1000
        .colWidth(4) = 1000
        .colWidth(5) = 1000
        '.Row = 1
    End With
End Sub