📄 frmv_sellhis.frm
字号:
EndProperty
ForeColor = &H000000FF&
Height = 240
Index = 4
Left = 6660
TabIndex = 19
Top = 3420
Width = 255
End
Begin VB.Line Line1
BorderColor = &H00C0C0C0&
X1 = 5580
X2 = 8760
Y1 = 2790
Y2 = 2790
End
Begin VB.Line Line2
BorderColor = &H00C0C0C0&
X1 = 5580
X2 = 8760
Y1 = 3840
Y2 = 3840
End
Begin VB.Label Label2
Caption = "￥"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 240
Index = 5
Left = 6660
TabIndex = 18
Top = 4440
Width = 255
End
Begin VB.Label lblSumZs
Caption = "0.00"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 315
Left = 7080
TabIndex = 17
Top = 4440
Width = 1665
End
Begin VB.Label Label6
Caption = "金 额："
Height = 210
Left = 5550
TabIndex = 16
Top = 4470
Width = 1050
End
Begin VB.Label lblNums
Caption = "0"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 315
Left = 7080
TabIndex = 15
Top = 4020
Width = 1665
End
Begin VB.Label Label12
Caption = "整单折扣："
Height = 210
Left = 5550
TabIndex = 14
Top = 4050
Width = 1050
End
Begin VB.Label Label2
Caption = "￥"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 240
Index = 0
Left = 6660
TabIndex = 13
Top = 825
Width = 255
End
Begin VB.Label lblBack
Caption = "0.00"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 315
Left = 7080
TabIndex = 12
Top = 825
Width = 1515
End
Begin VB.Label Label1
Caption = "退 货："
Height = 210
Index = 0
Left = 5550
TabIndex = 11
Top = 825
Width = 1050
End
Begin VB.Label Label2
Caption = "￥"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 240
Index = 6
Left = 6660
TabIndex = 10
Top = 1320
Width = 255
End
Begin VB.Label lblNow
Caption = "0.00"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H000000FF&
Height = 315
Left = 7080
TabIndex = 9
Top = 1320
Width = 1515
End
Begin VB.Label Label1
Caption = "实际金额："
Height = 210
Index = 2
Left = 5550
TabIndex = 8
Top = 1335
Width = 1050
End
Begin VB.Label Label1
Caption = "起 始："
Height = 210
Index = 1
Left = 5550
TabIndex = 7
Top = 5130
Width = 840
End
Begin VB.Label Label2
Caption = "截 止："
Height = 210
Index = 3
Left = 5550
TabIndex = 6
Top = 5610
Width = 840
End
End
Attribute VB_Name = "frmV_SellHis"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim BeginDate As String
Dim EndDate As String
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdQuery_Click()
BeginDate = Format(dtp1.Value, "yyyy-mm-dd")
EndDate = Format(dtp2.Value, "yyyy-mm-dd")
Dim SQL As String
tabReport.Tabs(1).Selected = True
Dim Rst As ADODB.Recordset
Dim I As Integer
Dim Cost As Currency
Dim Price As Currency
Dim Gain As Currency
Dim Back As Currency
For I = 1 To Grid.Rows - 1
Price = Price + Val(Grid.TextMatrix(I, 5))
Cost = Cost + Val(Grid.TextMatrix(I, 6))
Next
lblPrice.Caption = Format(CStr(Price), "0.00")
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
Rst.Open "select sum(num*backprice) from backsell_list where date between '" & _
BeginDate & "' and '" & EndDate & "'", Conn, adOpenDynamic, adLockReadOnly, adCmdText
If IsNull(Rst.Fields(0)) Then
Back = 0
Else
Back = Rst.Fields(0)
End If
lblBack.Caption = Format(Back, "0.00")
lblNow.Caption = Format(Price - Back, "0.00")
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
SQL = "select sum(num*cost) from backsell_list where date between '" & _
BeginDate & "' and '" & EndDate & "'"
Rst.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
If IsNull(Rst.Fields(0)) Then
Back = 0
Else
Back = Rst.Fields(0)
End If
Cost = Cost - Back
lblCost.Caption = Format(CStr(Cost), "0.00")
lblGain.Caption = Format(CStr(Val(lblNow.Caption) - Cost), "0.00")
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
Rst.Open "select count(*),sum(sums - sellsums) from sell_index where (date between '" & _
BeginDate & "' and '" & EndDate & "') and zhekou<>1", _
Conn, adOpenDynamic, adLockReadOnly, adCmdText
If Not IsNull(Rst.Fields(0)) Then lblNums.Caption = Rst.Fields(0) & " 笔"
If Not IsNull(Rst.Fields(1)) Then lblSumZs.Caption = _
Format(Val(Rst.Fields(1) & ""), "0.00")
Set Rst = New ADODB.Recordset
Rst.CursorLocation = adUseClient
Rst.Open "SELECT COUNT(*) AS counts, SUM((a.price - a.sellprice) * a.num) AS sums" & _
" FROM sell_list as a INNER JOIN sell_index b ON a.id = b.id AND (date between '" & _
BeginDate & "' and '" & EndDate & "') WHERE (a.zhekou <> 1)", _
Conn, adOpenDynamic, adLockReadOnly, adCmdText
If Not IsNull(Rst.Fields(0)) Then lblNum.Caption = Rst.Fields(0) & " 笔"
If Not IsNull(Rst.Fields(1)) Then lblSumZ.Caption = Format(Val(Rst.Fields(1) & ""), "0.00")
Set Rst = Nothing
End Sub
Private Sub Form_Load()
dtp1.Value = Date$
dtp2.Value = Date$
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set frmV_SellToday = Nothing
End Sub
Private Sub tabReport_Click()
Dim SQL As String
Select Case tabReport.SelectedItem.Index
Case 1
SQL = "SELECT a.id, a.name, SUM(b.cost * b.num) / SUM(b.num) AS cost, " _
& "SUM(b.sellprice * b.num)/ SUM(b.num) AS price, SUM(b.num) AS num, " _
& "SUM(b.sellprice * b.num) AS allprice,SUM(b.cost * b.num) AS allcost," _
& " SUM((b.sellprice - b.cost) * b.num) AS gain FROM dbo.yaopin_list a" _
& " INNER JOIN dbo.sell_list b ON a.id = b.yaopin_id INNER JOIN " _
& "dbo.sell_index c ON (c.[date] between '" & BeginDate & "' and '" _
& EndDate & "') AND b.id = c.id GROUP BY a.id, a.name"
FillGrid " 商品编码 |^ 商 品 名 称|^ 平均成本|^ 平均售价|^ 销售数量|^ 销售额|^ 总成本|^ 毛 利", _
SQL, Grid, 8
Case 2
SQL = "SELECT username,(SELECT COUNT(*) FROM sell_index AS b " _
& " WHERE b.username = a.username AND (b.[date] between '" _
& BeginDate & "' and '" & EndDate & "')) AS counts," _
& "(SELECT SUM(sellsums) FROM sell_index AS b " _
& "WHERE b.username = a.username AND (b.[date] between '" _
& BeginDate & "' and '" & EndDate & "' )) AS sellsums FROM dbo.[user] a"
FillGrid " 收 款 员 |^ 交易次数 |^ 销售金额 ", SQL, Grid, 3
Case 3
SQL = "SELECT id, name,(SELECT SUM(num) From sell_list " _
& " WHERE yaopin_id LIKE a.id + '%' AND (LEFT(id, 6) between '" _
& Format(dtp1.Value, "yymmdd") & "' and '" & Format(dtp2.Value, "yymmdd") _
& "')) AS nums,(SELECT SUM(num * sellprice) From sell_list " _
& "WHERE yaopin_id LIKE a.id + '%' AND (LEFT(id, 6) between '" _
& Format(dtp1.Value, "yymmdd") & "' and '" & Format(dtp2.Value, "yymmdd") _
& "')) AS sellprice FROM dbo.yaopin_types a WHERE (LEN(id) = 2)"
FillGrid " 类 别 码|^ 类别名称 |^ 销售数量|^ 销售金额", SQL, Grid, 4
Case 4
SQL = "select a.id,name,num,backprice,num*backprice,username from " & _
"backsell_list as a,yaopin_list as b where a.id=b.id and date between '" & _
BeginDate & "' and '" & EndDate & "'"
FillGrid "商品编码|^ 商 品 名 称|^ 退货数量|^ 价 格|^ 合 计|^ 收款员", SQL, Grid, 6
End Select
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -