📄 frminoutstore.frm
字号:
VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Begin VB.Form frmInOutStore
BorderStyle = 3 'Fixed Dialog
Caption = "商品进销存综合报表"
ClientHeight = 2700
ClientLeft = 45
ClientTop = 330
ClientWidth = 6435
Icon = "frmInOutStore.frx":0000
LinkTopic = "Form1"
MaxButton = 0 'False
MDIChild = -1 'True
MinButton = 0 'False
ScaleHeight = 2700
ScaleWidth = 6435
ShowInTaskbar = 0 'False
Begin VB.CommandButton Command2
Caption = "结账[&1]"
Height = 405
Left = 2370
TabIndex = 10
Top = 1875
Width = 1755
End
Begin VB.OptionButton optNoTax
Caption = "不含税"
Height = 195
Left = 3480
TabIndex = 9
Top = 1020
Value = -1 'True
Width = 1260
End
Begin VB.OptionButton optTax
Caption = "含税"
Height = 180
Left = 1155
TabIndex = 8
Top = 990
Width = 990
End
Begin VB.CommandButton cmdReport
Caption = "报表[&R]"
Height = 405
Left = 450
TabIndex = 7
Top = 1890
Width = 1755
End
Begin MSComctlLib.ProgressBar prg
Height = 270
Left = 480
TabIndex = 6
Top = 1410
Width = 5340
_ExtentX = 9419
_ExtentY = 476
_Version = 393216
Appearance = 1
End
Begin VB.CommandButton cmdExit
Caption = "退出[&X]"
Height = 405
Left = 4260
TabIndex = 5
Top = 1890
Width = 1755
End
Begin VB.CommandButton Command1
Caption = "结账[&0]"
Height = 405
Left = 1875
TabIndex = 4
Top = 2220
Visible = 0 'False
Width = 1755
End
Begin MSComCtl2.DTPicker dtpEnd
Height = 300
Left = 4035
TabIndex = 2
Top = 315
Width = 1545
_ExtentX = 2725
_ExtentY = 529
_Version = 393216
Format = 52559873
CurrentDate = 37139
End
Begin MSComCtl2.DTPicker dtpBegin
Height = 330
Left = 1290
TabIndex = 3
Top = 270
Width = 1650
_ExtentX = 2910
_ExtentY = 582
_Version = 393216
Format = 52559873
CurrentDate = 37139
End
Begin VB.Label Label2
AutoSize = -1 'True
Caption = "时间止"
Height = 180
Left = 3270
TabIndex = 1
Top = 360
Width = 540
End
Begin VB.Label Label1
AutoSize = -1 'True
Caption = "时间起"
Height = 180
Left = 480
TabIndex = 0
Top = 375
Width = 540
End
End
Attribute VB_Name = "frmInOutStore"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdReport_Click()
On Error Resume Next
sSQL = "select * from inoutstore order by 商品编码,颜色,尺寸"
If envDStar.rscmdInOutStore.State = adStateOpen Then
envDStar.rscmdInOutStore.Close
End If
envDStar.Commands("cmdInOutStore").CommandText = sSQL
envDStar.Commands("cmdInOutStore").Execute
envDStar.rscmdInOutStore.Open
Load rptInOutStore
rptInOutStore.Sections("ReportHeader").Controls("lblDate").Caption = "日期:" & Format(dtpBegin.Value, "yyyy-mm-dd") & "～" & Format(dtpEnd.Value, "yyyy-mm-dd")
rptInOutStore.Show 1
End Sub
Private Sub Command1_Click()
On Error Resume Next
Dim RS1 As New ADODB.Recordset
Dim RIn As New ADODB.Recordset
Dim ROut As New ADODB.Recordset
Dim RS2 As New ADODB.Recordset
Dim RG As New ADODB.Recordset
Dim TAX, NoTax
Dim PQty, PSum, InQty, InSum, OutQty, OutSum, NQty, NSum
If optTax Then
TAX = 1
NoTax = 1.17
Else
TAX = 1.17
NoTax = 1
End If
Cmd.ActiveConnection = Conn
Cmd.CommandText = "delete inoutstore"
Cmd.Execute
'1271823297
RG.Open "select * from 配送中心库存", Conn, adOpenStatic, adLockReadOnly
prg.Max = RG.RecordCount
prg.Value = 0
While Not RG.EOF
sSQL = "select 商品编码,品名,单位,颜色,尺寸,数量,进价金额 as 金额 from RecMainStore " & _
" where 商品编码='" & RG("商品编码") & _
"' and 颜色='" & RG("颜色") & "' and 尺寸='" & RG("尺寸") & "' and 日期='" & Format(DateAdd("D", -1, dtpBegin.Value), "yyyy-mm-dd") & "'"
Set RS1 = Nothing
RS1.Open sSQL, Conn, adOpenStatic, adLockReadOnly
If Not RS1.EOF Then
PQty = RS1("数量")
PSum = RS1("金额") * NoTax
Else
PQty = 0
PSum = 0
End If
sSQL = "select 商品编码,品名,单位,颜色,尺寸,SUM(数量) AS 数量,SUM(金额) AS 金额" & _
" from v_in_detail " & _
" where 日期 between '" & Format(dtpBegin.Value, "yyyy-mm-dd") & "' and '" & Format(dtpEnd.Value, "yyyy-mm-dd") & "' and 商品编码='" & RG("商品编码") & _
"' and 颜色='" & RG("颜色") & "' and 尺寸='" & RG("尺寸") & "' group by 商品编码,品名,单位,颜色,尺寸"
Set RIn = Nothing
RIn.Open sSQL, Conn, adOpenStatic, adLockReadOnly
If Not RIn.EOF Then
InQty = RIn("数量")
InSum = Format(RIn("金额") / TAX, "#.00")
Else
InQty = 0
InSum = 0
End If
sSQL = "select 商品编码,品名,单位,颜色,尺寸,SUM(数量) AS 数量,SUM(金额) AS 金额" & _
" from v_out_detail " & _
" where 日期 between '" & Format(dtpBegin.Value, "yyyy-mm-dd") & "' and '" & Format(dtpEnd.Value, "yyyy-mm-dd") & "' and 商品编码='" & RG("商品编码") & _
"' and 颜色='" & RG("颜色") & "' and 尺寸='" & RG("尺寸") & "' group by 商品编码,品名,单位,颜色,尺寸"
Set ROut = Nothing
ROut.Open sSQL, Conn, adOpenStatic, adLockReadOnly
If Not ROut.EOF Then
OutQty = ROut("数量")
OutSum = Format(ROut("金额") / TAX, "#.00")
Else
OutQty = 0
OutSum = 0
End If
sSQL = "select 商品编码,品名,单位,颜色,尺寸,数量,进价金额 as 金额 from RecMainStore" & _
" where 商品编码='" & RG("商品编码") & _
"' and 颜色='" & RG("颜色") & "' and 尺寸='" & RG("尺寸") & "' and 日期='" & Format(dtpEnd.Value, "yyyy-mm-dd") & "'"
Set RS2 = Nothing
RS2.Open sSQL, Conn, adOpenStatic, adLockReadOnly
If Not RS2.EOF Then
NQty = RS2("数量")
NSum = RS2("金额") * NoTax
Else
NQty = 0
NSum = 0
End If
sSQL = "insert into InOutStore(商品编码,品名,单位,颜色,尺寸,上期结存数量,上期结存金额,本期购进数量,本期购进金额,本期发出数量,本期发出金额,本期结存数量,本期结存金额)" & _
" values('" & RG("商品编码") & "','" & RG("品名") & "','" & RG("单位") & "','" & RG("颜色") & "','" & RG("尺寸") & "','" & _
PQty & "','" & PSum & "','" & InQty & "','" & InSum & "','" & OutQty & "','" & OutSum & "','" & NQty & "','" & NSum & "')"
Cmd.CommandText = sSQL
Cmd.Execute
prg.Value = prg.Value + 1
Refresh
RG.MoveNext
Wend
MsgBox "结账完毕！", vbInformation, "提示信息"
End Sub
Private Sub Command2_Click()
On Error Resume Next
If optTax Then
Cmd.CommandText = "XP_进销存明细账 '" & Format(dtpBegin.Value, "yyyy-mm-dd") & "','" & Format(dtpEnd.Value, "yyyy-mm-dd") & "',1"
Else
Cmd.CommandText = "XP_进销存明细账 '" & Format(dtpBegin.Value, "yyyy-mm-dd") & "','" & Format(dtpEnd.Value, "yyyy-mm-dd") & "',0"
End If
Cmd.ActiveConnection = Conn
Cmd.Execute
MsgBox "结账完毕！", vbInformation, "提示信息"
End Sub
Private Sub Form_Load()
dtpBegin.Value = Now
dtpEnd.Value = Now
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -