物料收发存汇总表
# FastERP 进销存模板开发 物料收发存汇总表
物料收发存汇总表位于菜单栏中的 [进销存]-[物料报表]-[物料收发存汇总表]。使用的模块类型为存储过程报表窗体，关于存储过程报表窗体的创建模式请参考 存储过程报表。
# 1. 模块设计
登陆FastERP，打开[主页]->[平台设计]->[系统设计]->[模块设计]。在模块设计列表左侧中找到 进销存-物料管理-统计报表-物料收发存汇总表，单击选择，点击菜单栏中的 [编辑] 按钮。打开 模块设计 界面。
界面中需勾选 打开数据、允许导出表格 与 允许定制表格。
在窗体设计-数据集信息界面中，设定数据集的基础信息，主控件需设置为 dbGridBand。条件代码处的内容可在查询条件设置完成后进行设置。构建的SQL语句如下：
declare @FDate1 datetime,
@FDate2 datetime,
@FBillID int,
@FStockID int,
@FItemCode varchar(30),
@FItemName varchar(50),
@FBatchNO varchar(50)
select @FDate1 = :FDate1, @FDate2 = :FDate2, @FStockID = :FStockID,
@FItemCode = :FItemCode,
@FItemName = :FItemName,
@FBatchNO = :FBatchNO
select @FItemCode = isnull(@FItemCode,''),
@FItemName = isnull(@FItemName,''),
@FBatchNO = isnull(@FBatchNO,'')
create table #tmp1(
FItemID int,
FItemCode varchar(30),
FItemName varchar(100),
FUnitID int,
FUnitCode varchar(30),
FUnitName varchar(50),
FItemSpec varchar(100),
FBatchNo varchar(100),
FBegQtyPiece float default 0,
FBegQty float default 0,
FBegAmount float default 0,
FBegTaxAmount float default 0,
FInQtyPiece float default 0,
FInQty float default 0,
FInAmount float default 0,
FInTaxAmount float default 0,
FOutQtyPiece float default 0,
FOutQty float default 0,
FOutAmount float default 0,
FOutTaxAmount float default 0,
FEndQtyPiece float default 0,
FEndQty float default 0, -- 期末结存 = 期初 + 本期入库(不包括盘盈盘亏单) - 本期出库(不包括盘盈盘亏单)
FEndAmount float default 0,
FEndTaxAmount float default 0,
FCheckQtyPiece float default 0,
FCheckQty float default 0, --盘点数 = 期初 + 本期入库 - 本期出库
FCheckAmount float default 0,
FCheckTaxAmount float default 0,
FOverQtyPiece float default 0,
FOverQty float default 0, --盘盈数量
FOverAmount float default 0,
FOverTaxAmount float default 0,
FLowQtyPiece float default 0, --盘亏数量
FLowQty float default 0, --盘亏数量
FLowAmount float default 0,
FLowTaxAmount float default 0
)
--期初 + 本期入库(不含本期盘点单) - 本期出库(不含本期盘点单) = 期末结存
--期末结存 + 盘盈入库 - 盘亏出库 = 盘点数
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FBegQtyPiece, FBegQty, FBegAmount, FBegTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0) - isnull(sum(FOutQtyPiece), 0),
isnull(sum(FInQty), 0) - isnull(sum(FOutQty), 0),
isnull(sum(FInAmount), 0) - isnull(sum(FOutAmount), 0),
isnull(sum(FInTaxAmount), 0) - isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FDate < @FDate1
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FInQtyPiece ,FInQty, FInAmount, FInTaxAmount, FOutQtyPiece ,FOutQty, FOutAmount, FOutTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0), isnull(sum(FInQty), 0), isnull(sum(FInAmount), 0), isnull(sum(FInTaxAmount), 0),
isnull(sum(FOutQtyPiece), 0),isnull(sum(FOutQty), 0), isnull(sum(FOutAmount), 0), isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FBillID not in (24022002, 24022003)
and FDate between @FDate1 and @FDate2
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
insert into #tmp1(FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
FOverQtyPiece ,FOverQty, FOverAmount, FOverTaxAmount, FLowQtyPiece, FLowQty, FLowAmount, FLowTaxAmount)
select FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo,
isnull(sum(FInQtyPiece), 0), isnull(sum(FInQty), 0), isnull(sum(FInAmount), 0), isnull(sum(FInTaxAmount), 0),
isnull(sum(FOutQtyPiece), 0),isnull(sum(FOutQty), 0), isnull(sum(FOutAmount), 0), isnull(sum(FOutTaxAmount), 0)
from V_MatStockDetail
where FBillID in (24022002, 24022003)
and FDate between @FDate1 and @FDate2
and (FStockID = @FStockID or 0 = @FStockID)
and (isnull(FItemCode, '') = @FItemCode or @FItemCode = '')
and (isnull(FBatchNO, '') = @FBatchNO or @FBatchNO = '')
and (isnull(FItemName,'') LIKE '%' + @FItemName + '%'or @FItemName ='')
group by FItemID, FItemCode, FItemName, FUnitID, FUnitCode, FUnitName, FItemSpec, FBatchNo
delete #tmp1
where FBegQtyPiece=0 and FBegQty = 0 and FBegAmount = 0 and FBegTaxAmount = 0
and FInQtyPiece = 0 and FInQty = 0 and FInAmount = 0 and FInTaxAmount = 0
and FOutQtyPiece = 0 and FOutQty = 0 and FOutAmount = 0 and FOutTaxAmount = 0
and FOverQtyPiece = 0 and FOverQty = 0 and FOverAmount = 0 and FOverTaxAmount = 0
and FLowQtyPiece = 0 and FLowQty = 0 and FLowAmount = 0 and FLowTaxAmount = 0
----插入期末结存的件数/数量
update #tmp1 set
FEndQtyPiece = isnull(FBegQtyPiece, 0) + isnull(FInQtyPiece, 0) - isnull(FOutQtyPiece, 0),
FEndQty = isnull(FBegQty, 0) + isnull(FInQty, 0) - isnull(FOutQty, 0),
FEndAmount = isnull(FBegAmount, 0) + isnull(FInAmount, 0) - isnull(FOutAmount, 0),
FEndTaxAmount = isnull(FBegTaxAmount, 0) + isnull(FInTaxAmount, 0) - isnull(FOutTaxAmount, 0),
FCheckQtyPiece = isnull(FBegQtyPiece, 0) + isnull(FInQtyPiece, 0) - isnull(FOutQtyPiece, 0) + isnull(FOverQtyPiece, 0) - isnull(FLowQtyPiece, 0),
FCheckQty = isnull(FBegQty, 0) + isnull(FInQty, 0) - isnull(FOutQty, 0) + isnull(FOverQty, 0) - isnull(FLowQty, 0),
FCheckAmount = isnull(FBegAmount, 0) + isnull(FInAmount, 0) - isnull(FOutAmount, 0) + isnull(FOverAmount, 0) - isnull(FLowAmount, 0),
FCheckTaxAmount = isnull(FBegTaxAmount, 0) + isnull(FInTaxAmount, 0) - isnull(FOutTaxAmount, 0) + isnull(FOverTaxAmount, 0) - isnull(FLowTaxAmount, 0)
select
a.FItemID, a.FItemCode, a.FItemName,a.FItemSpec, a.FUnitID, a.FUnitCode, a.FUnitName,
Sum(a.FBegQtyPiece) as FBegQtyPiece,Sum(a.FBegQty) as FBegQty,Sum(a.FBegAmount) as FBegAmount,Sum(a.FBegTaxAmount) as FBegTaxAmount,
Sum(a.FInQtyPiece) as FInQtyPiece,Sum(a.FInQty) as FInQty, Sum(a.FInAmount) as FInAmount, Sum(a.FInTaxAmount) as FInTaxAmount,
Sum(a.FOutQtyPiece) as FOutQtyPiece,Sum(a.FOutQty) as FOutQty,Sum(a.FOutAmount) as FOutAmount, Sum(a.FOutTaxAmount) as FOutTaxAmount,
Sum(a.FEndQtyPiece) as FEndQtyPiece,Sum(a.FEndQty) as FEndQty, Sum(a.FEndAmount) as FEndAmount, Sum(a.FEndTaxAmount) as FEndTaxAmount,
Sum(a.FOverQtyPiece) as FOverQtyPiece,Sum(a.FOverQty) as FOverQty,Sum(a.FOverAmount) as FOverAmount, Sum(a.FOverTaxAmount) as FOverTaxAmount,
Sum(a.FLowQtyPiece) as FLowQtyPiece,Sum(a.FLowQty) as FLowQty,Sum(a.FLowAmount) as FLowAmount, Sum(a.FLowTaxAmount) as FLowTaxAmount,
Sum(a.FCheckQtyPiece) as FCheckQtyPiece,Sum(a.FCheckQty) as FCheckQty,Sum(a.FCheckAmount) as FCheckAmount, Sum(a.FCheckTaxAmount) as FCheckTaxAmount
from #tmp1 a
left outer join Basic_Item t1 on t1.FInterID = a.FItemID
Group by a.FItemID, a.FItemCode, a.FItemName,a.FItemSpec, a.FUnitID, a.FUnitCode, a.FUnitName
drop table #tmp1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
在字段信息选项卡中，点击 [加载字段]，将SQL语句构造的数据集字段信息加载至列表中。分别设置各字段的字段标签与分组名。按照图中所示的内容进行设置。
查询条件处按照上图所示的内容进行设置。
其中 FDate1 需设置默认值类型为 VAR_DATEBEGIN，FDate2 需设置默认值类型为 VAR_DATEEND。
设置完成后点击 [保存] 按钮保存模块设置。将查询条件的条件代码填写至数据集信息中的条件代码栏位处。
# 2. 设计布局
点击模块设计菜单栏中的 设计布局-存储过程报表窗体，在FastERP的主窗口中会显示一个新创建的标签页，点击菜单栏中的 设计-设计布局，会打开自定义窗口，其中会显示可使用的组件。将快捷查询条件与表格分别拖动放置于界面中。使用鼠标右键调整取消部分控件的标题显示。最终设置完成的界面显示如下：
设置完成后，点击菜单栏中的 [设计]-[保存布局]，在弹出的确认保存布局窗口中选择 [确认] 按钮，等待保存完成时弹出 保存成功 的提示，至此完成布局的保存。
# 3. 设计菜单
关闭模块设计窗口，点击菜单栏中的 [平台设计]-[菜单设计]，打开菜单设计界面。示例中的菜单创建于进销存-物料报表-物料收发存汇总表中，选择这个菜单项，注意菜单项的设置中模块类型需设置为 打开存储过程窗体，模块编码选择创建的模块编码ReportInvStockSFC。保存菜单设置。
保存完成后，可在菜单栏中 [进销存]-[物料报表]-[物料收发存汇总表] 处打开模块。