mainzz.frm

网吧库存管理系统 主要对商品网费等费用和数量管理

      Left            =   300
      TabIndex        =   18
      Top             =   2490
      Width           =   9495
   End
   Begin VB.Label Label7 
      BackColor       =   &H00FFFFFF&
      BorderStyle     =   1  'Fixed Single
      Caption         =   "Label7"
      Height          =   450
      Left            =   285
      TabIndex        =   12
      Top             =   1980
      Width           =   9540
   End
End
Attribute VB_Name = "mainzz"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim zy As Byte

Private Sub Combo1_Click()
zy = Combo1.ListIndex
Adodc1.RecordSource = "select DISTINCT dws from yye where flags='" & zy & "'"
Adodc1.Refresh
Combo2.Clear
Combo2.AddItem " "
Do While Not Adodc1.Recordset.EOF
Combo2.AddItem Trim(Adodc1.Recordset.Fields(0).Value)
Adodc1.Recordset.MoveNext
Loop

Adodc1.RecordSource = "select DISTINCT xm from yye where flags='" & zy & "'"
Adodc1.Refresh
Combo3.Clear
Combo3.AddItem " "
Do While Not Adodc1.Recordset.EOF
Combo3.AddItem Trim(Adodc1.Recordset.Fields(0).Value)
Adodc1.Recordset.MoveNext
Loop

Adodc1.RecordSource = "select DISTINCT dh from yye where flags='" & zy & "'"
Adodc1.Refresh
Combo4.Clear
Combo4.AddItem " "
Do While Not Adodc1.Recordset.EOF
Combo4.AddItem Trim(Adodc1.Recordset.Fields(0).Value)
Adodc1.Recordset.MoveNext
Loop

Adodc1.RecordSource = "select DISTINCT year(sdate)  from yye where flags='" & zy & "'"
Adodc1.Refresh
Combo5.Clear
Do While Not Adodc1.Recordset.EOF
Combo5.AddItem Trim(Adodc1.Recordset.Fields(0).Value)
Adodc1.Recordset.MoveNext
Loop
Combo6.Clear
For i = 1 To 12
Combo6.AddItem i
Next
loadmsh
End Sub

Private Sub Combo2_Click()
loadmsh
End Sub
Private Sub Combo3_Click()
loadmsh
End Sub
Private Sub Combo4_Click()
loadmsh
End Sub
Private Sub Combo6_Click()
loadmsh
End Sub

Private Sub Combo5_Click()
If Trim(Combo5.Text) = "" Then loadmsh
End Sub

Private Sub Form_Load()
Me.Top = mme.Top + mme.Height
Me.Left = Screen.Width - Me.Width - 100
CoolBoySkin1.FormOnTop Me.hDC, True
Adodc1.ConnectionString = connstr
Adodc1.CommandType = adCmdText
Adodc1.CommandTimeout = 20
Combo1.AddItem "未记帐内容"
Combo1.AddItem "已记帐内容"
Combo1.AddItem "内部记帐"
Combo1.Text = "未记帐内容"
zy = 0
loadmsh
End Sub

Public Sub loadmsh()
With MSH
.Clear
.Rows = 1

Combo5.Text = Year(Date)
Combo6.Text = Month(Date)
Label7.Caption = ""
Label6.Caption = ""
If Check1.Value = 0 Then
.Cols = 12
.TextMatrix(0, 0) = "单号"
.TextMatrix(0, 1) = "内容简介"
.TextMatrix(0, 2) = "收款"
.TextMatrix(0, 3) = "付款"
.TextMatrix(0, 4) = "小计"
.TextMatrix(0, 5) = "备注"
.TextMatrix(0, 6) = "时间"
.TextMatrix(0, 7) = "交班人"
.TextMatrix(0, 8) = "接班人"
.TextMatrix(0, 9) = "部门"
.TextMatrix(0, 10) = "标记"
.ColWidth(10) = 0
Else
.Cols = 5
.TextMatrix(0, 0) = "内容简介"
.TextMatrix(0, 1) = "收款"
.TextMatrix(0, 2) = "付款"
.TextMatrix(0, 3) = "小计"
.TextMatrix(0, 4) = "部门"
End If

End With


End Sub

Private Sub xpcmdbutton1_Click()
If Check1.Value = 0 Then
Call loaddate
Else
Call loaddate1
End If



End Sub

Private Sub xpcmdbutton2_Click()
frmbb.Show
Unload mainzz
End Sub

Public Sub loaddate()
loadmsh
If Trim(Combo1.Text) = "" Then Exit Sub
Dim sqlstr As String
Dim sqlstr1 As String
Dim sss As String
Dim d1 As Double
Dim d2 As Double
Dim d3 As Double

sss = sss & " 帐面类型为：" & Trim(Combo1.Text)
sqlstr = " where dh not in ('0') and flags=" & zy
If Trim(Combo2.Text) <> "" Then sqlstr = sqlstr & " and dws='" & Trim(Combo2.Text) & "'": sss = sss & " 部门为：" & Trim(Combo2.Text)
If Trim(Combo3.Text) <> "" Then sqlstr = sqlstr & " and xm='" & Trim(Combo3.Text) & "'": sss = sss & " 内容简介为：" & Trim(Combo3.Text)
If Trim(Combo4.Text) <> "" Then sqlstr = sqlstr & " and dh='" & Trim(Combo4.Text) & "'": sss = sss & " 单据号为：" & Trim(Combo4.Text)
sqlstr1 = sqlstr
If Trim(Combo5.Text) <> "" Then sqlstr = sqlstr & " and year(sdate)='" & Trim(Combo5.Text) & "'": sss = sss & " 年份为：" & Trim(Combo5.Text)
If Trim(Combo6.Text) <> "" Then sqlstr = sqlstr & " and month(sdate)='" & Trim(Combo6.Text) & "'": sss = sss & " 月份为：" & Trim(Combo6.Text)
Adodc1.RecordSource = "select dh as 单号,xm as 内容简介,sum(jk) as 收款,sum(fk) as 付款,sum(je) as 小计,beizhu as 备注,sdate as 时间,jbr as 交班人,jbs as 接班人,dws as 部门,flags as 标记 from yye " & sqlstr & "  group by xm,dh,beizhu,sdate,jbr,jbs,dws,flags order by dh,xm,beizhu,sdate,jbr,jbs,dws,flags"
Adodc1.Refresh
i = 0
With MSH
Label7.Caption = sss
.ColWidth(10) = 0
Do While Not Adodc1.Recordset.EOF
i = i + 1
If .Rows < i + 1 Then .Rows = i + 1
For j = 0 To 10
.TextMatrix(i, j) = Trim(Adodc1.Recordset.Fields(j).Value)
Next

d1 = d1 + CDbl(.TextMatrix(i, 2))
d2 = d2 + CDbl(.TextMatrix(i, 3))
d3 = d3 + CDbl(.TextMatrix(i, 4))
Adodc1.Recordset.MoveNext
Loop
.AddItem " " & vbTab & "总计" & vbTab & Format(d1, "#0.00") & vbTab & Format(d2, "#0.00") & vbTab & Format(d3, "#0.00")
.Row = .Rows - 1
For i = 0 To .Cols - 1
.ColAlignment(i) = 4
.ColAlignmentFixed(i) = 4
.Col = i
.CellForeColor = vbRed
Next
Adodc1.RecordSource = "select sum(jk) ,sum(fk) ,sum(je)  from yye " & sqlstr1 & " and left(dh,6)< " & Format(Format(Val(Combo5.Text), "0000") & Format(Val(Combo6.Text), "00"))
Adodc1.Refresh
Label6.Caption = Trim(Combo5.Text) & "年" & Trim(Combo6.Text) & "月前总收款：" & Format(Adodc1.Recordset.Fields(0).Value, "#0.00") & "元   总支出款：" & Format(Adodc1.Recordset.Fields(2).Value, "#0.00") & "元 合计：" & Format(Adodc1.Recordset.Fields(2).Value, "#0.00") & "元 "
End With
End Sub
Public Sub loaddate1()
loadmsh
If Trim(Combo1.Text) = "" Then Exit Sub
Dim sqlstr As String
Dim sss As String
Dim d1 As Double
Dim d2 As Double
Dim d3 As Double

sss = sss & " 帐面类型为：" & Trim(Combo1.Text)
sqlstr = " where dh not in ('0') and flags=" & zy
If Trim(Combo2.Text) <> "" Then sqlstr = sqlstr & " and dws='" & Trim(Combo2.Text) & "'": sss = sss & " 部门为：" & Trim(Combo2.Text)
If Trim(Combo3.Text) <> "" Then sqlstr = sqlstr & " and xm='" & Trim(Combo3.Text) & "'": sss = sss & " 内容简介为：" & Trim(Combo3.Text)
If Trim(Combo4.Text) <> "" Then sqlstr = sqlstr & " and dh='" & Trim(Combo4.Text) & "'": sss = sss & " 单据号为：" & Trim(Combo4.Text)
If Trim(Combo5.Text) <> "" Then sqlstr = sqlstr & " and year(sdate)='" & Trim(Combo5.Text) & "'": sss = sss & " 年份为：" & Trim(Combo5.Text)
If Trim(Combo6.Text) <> "" Then sqlstr = sqlstr & " and month(sdate)='" & Trim(Combo6.Text) & "'": sss = sss & " 月份为：" & Trim(Combo6.Text)
Adodc1.RecordSource = "select xm as 内容简介,sum(jk) as 收款,sum(fk) as 付款,sum(je) as 小计,dws as 部门 from yye " & sqlstr & "  group by xm,dws order by xm,dws"
Adodc1.Refresh
i = 0
With MSH
Label7.Caption = sss
Do While Not Adodc1.Recordset.EOF
i = i + 1
If .Rows < i + 1 Then .Rows = i + 1
For j = 0 To 4
.TextMatrix(i, j) = Trim(Adodc1.Recordset.Fields(j).Value)
Next

d1 = d1 + CDbl(.TextMatrix(i, 1))
d2 = d2 + CDbl(.TextMatrix(i, 2))
d3 = d3 + CDbl(.TextMatrix(i, 3))
Adodc1.Recordset.MoveNext
Loop
.AddItem "总计" & vbTab & Format(d1, "#0.00") & vbTab & Format(d2, "#0.00") & vbTab & Format(d3, "#0.00")
.Row = .Rows - 1
For i = 0 To .Cols - 1
.ColAlignment(i) = 4
.ColAlignmentFixed(i) = 4
.Col = i
.CellForeColor = vbRed
Next

End With
Adodc1.RecordSource = "select sum(jk) ,sum(fk) ,sum(je)  from yye " & sqlstr1 & " and left(dh,6)< " & Format(Format(Val(Combo5.Text), "0000") & Format(Val(Combo6.Text), "00"))
Adodc1.Refresh
Label6.Caption = Trim(Combo5.Text) & "年" & Trim(Combo6.Text) & "月前总收款：" & Format(Adodc1.Recordset.Fields(0).Value, "#0.00") & "元   总支出款：" & Format(Adodc1.Recordset.Fields(2).Value, "#0.00") & "元 合计：" & Format(Adodc1.Recordset.Fields(2).Value, "#0.00") & "元 "

End Sub