1 USE [zxerp]
2 GO
3 /****** Object: StoredProcedure [dbo].[Pro_GrossProfitCount] Script Date: 06/26/2014 08:48:10 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER procedure [dbo].[Pro_GrossProfitCount]
9 @fcomid int,
10 @startrq varchar(10),
11 @endrq varchar(10),
12 @FStockID varchar(20),
13 @FNumber varchar(20),
14 @FItemID varchar(20),
15 @FServiceID varchar(20),
16 @FCompanynumber varchar(20),
17 @FCompanyID varchar(20),
18 @FDeptID varchar(20),
19 @Ftype varchar(10),
20 @Flevel varchar(10)
21 as
22 begin
23 declare @sql varchar(3000)
24 declare @sql1 varchar(2000)
25 declare @sql2 varchar(2000)
26 declare @sql3 varchar(2000)
27 declare @sql4 varchar(2000)
28 -----一级
29 set @sql1='select ''总计'' as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total
30 into #category
31 from T_bill_detail a,t_bill b,t_item c,T_Item d ,t_item e
32 where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and a.FitemID = c.FItemID and c.FcomID = '''+cast(@fcomid as varchar(4))+''' and b.FDate >='''+@startrq+'''
33 and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID '
34
35 ----二级（店级）
36
37 set @sql2='select c.FItemID,1 as sort,'
38 set @sql2=@sql2+''' ''+c.FName as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total
39 into #StoreLevel
40 from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e
41 where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and c.FcomID = '''+cast(@fcomid as varchar(4))+''' and b.FDate >='''+@startrq+'''
42 and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID '
43
44
45
46 --------三级（日期级）
47 if @Flevel = '2'
48 begin
49 set @sql3='select 2 as sort,'
50 end
51 else
52 begin
53 set @sql3='select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,2 as sort,'
54 end
55 set @sql3=@sql3+''' ''+CONVERT(varchar(10), b.FDate, 23) as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total
56 into #RqLevel
57 from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e
58 where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and c.FItemID in (select FItemID from #StoreLevel)
59 and b.FDate >='''+@startrq+''' and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID '
60
61
62
63
64 ------------四级（商品级）
65
66 if @Flevel = '3'
67 begin
68 set @sql4='select 3 as sort,'
69 end
70 else
71 begin
72 set @sql4='select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,3 as sort,'
73 end
74 set @sql4=@sql4+''' ''+e.Fname as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total
75 into #GoodsLevel
76 from T_bill_detail a,t_bill b,T_Item c,T_Item d,t_item e
77 where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and a.fitemid = e.FItemID
78 and b.FDate >='''+@startrq+''' and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and e.FcomID = '''+cast(@fcomid as varchar(4))+''' '
79 --------------------------------
80 if @FStockID<>'zwc'
81 begin
82 set @sql1=@sql1+' and a.FstockIDin ='''+@FStockID+''' '
83 set @sql2=@sql2+' and a.FstockIDin ='''+@FStockID+''' '
84 set @sql3=@sql3+' and a.FstockIDin ='''+@FStockID+''' '
85 set @sql4=@sql4+' and a.FstockIDin ='''+@FStockID+''' '
86 end
87 if @FNumber<>'zwc'
88 begin
89 set @sql1=@sql1+' and e.FNumber like ''%'+@FNumber+'%'' '
90 set @sql2=@sql2+' and e.FNumber like ''%'+@FNumber+'%'' '
91 set @sql3=@sql3+' and e.FNumber like ''%'+@FNumber+'%'' '
92 set @sql4=@sql4+' and e.FNumber like ''%'+@FNumber+'%'' '
93 end
94 if @FItemID<>'zwc'
95 begin
96 set @sql1=@sql1+' and e.FitemID ='''+@FItemID+''' '
97 set @sql2=@sql2+' and e.FitemID ='''+@FItemID+''' '
98 set @sql3=@sql3+' and e.FitemID ='''+@FItemID+''' '
99 set @sql4=@sql4+' and e.FitemID ='''+@FItemID+''' '
100 end
101 if @FServiceID<>'zwc'
102 begin
103 set @sql1=@sql1+' and b.FServiceID ='''+@FServiceID+''' '
104 set @sql2=@sql2+' and b.FServiceID ='''+@FServiceID+''' '
105 set @sql3=@sql3+' and b.FServiceID ='''+@FServiceID+''' '
106 set @sql4=@sql4+' and b.FServiceID ='''+@FServiceID+''' '
107 end
108 if @FCompanynumber<>'zwc'
109 begin
110 set @sql1=@sql1+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
111 set @sql2=@sql2+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
112 set @sql3=@sql3+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
113 set @sql4=@sql4+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
114 end
115 if @FCompanyID<>'zwc'
116 begin
117 set @sql1=@sql1+' and b.FCompanyID ='''+@FCompanyID+''' '
118 set @sql2=@sql2+' and b.FCompanyID ='''+@FCompanyID+''' '
119 set @sql3=@sql3+' and b.FCompanyID ='''+@FCompanyID+''' '
120 set @sql4=@sql4+' and b.FCompanyID ='''+@FCompanyID+''' '
121 end
122 if @FDeptID<>'zwc'
123 begin
124 set @sql1=@sql1+' and b.FDeptID ='''+@FDeptID+''' '
125 set @sql2=@sql2+' and b.FDeptID ='''+@FDeptID+''' '
126 set @sql3=@sql3+' and b.FDeptID ='''+@FDeptID+''' '
127 set @sql4=@sql4+' and b.FDeptID ='''+@FDeptID+''' '
128 end
129
130 set @sql2=@sql2+' group by c.FItemID,c.fname '
131 if @Flevel = '2'
132 begin
133 set @sql3=@sql3+' group by CONVERT(varchar(10), b.FDate, 23) '
134 end
135 else
136 begin
137 set @sql3=@sql3+' group by c.FItemID,c.fname,CONVERT(varchar(10), b.FDate, 23) '
138 end
139 if @Flevel = '123'
140 begin
141 set @sql4 = @sql4 +' group by CONVERT(varchar(10), b.FDate, 23),c.FItemID,e.FName'
142 end
143 else
144 begin
145 if @Flevel = '3'
146 begin
147 set @sql4 = @sql4 +' group by e.FItemID,e.FName'
148 end
149 else
150 begin
151 set @sql4 = @sql4 +' group by c.FItemID,e.FName,CONVERT(varchar(10), b.FDate, 23)'
152 end
153 end
154 --print @sql1
155 --print @sql2
156 --print @sql3
157 --exec (@sql1)
158 --exec (@sql2)
159 --exec (@sql3)
160 set @sql = @sql1 + @sql2 + @sql3 + @sql4
161 if @Flevel = '123'
162 begin
163 set @sql = @sql +'
164 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #three
165 from (
166 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
167 union all
168 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a
169 order by FItemID,FDate,sort
170
171 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #total
172 from (
173 select FItemID,''2000-01-01'' as FDate,sort,category,fqty,famt,fcost,total from #StoreLevel
174 union all
175 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #three ) a
176 order by FItemID,FDate,sort
177
178 select category,fqty,famt,fcost,total from #category
179 union all
180 select category,fqty,famt,fcost,total from #total'
181 end
182 if @Flevel = '12'
183 begin
184 set @sql = @sql +'
185 select category,fqty,famt,fcost,total into #total
186 from (
187 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
188 union all
189 select FItemID,sort,category,fqty,famt,fcost,total from #RqLevel) a order by FItemID,sort
190
191 select category,fqty,famt,fcost,total from #category
192 union all
193 select category,fqty,famt,fcost,total from #total'
194 end
195 if @Flevel = '1'
196 begin
197 set @sql = @sql +'
198 select category,fqty,famt,fcost,total from #category
199 union all
200 select category,fqty,famt,fcost,total from #StoreLevel
201 '
202 end
203 if @Flevel = '13'
204 begin
205 set @sql = @sql +'select * into #total from (
206 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
207 union all
208 select FItemID,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by FItemID,sort
209
210 select category,fqty,famt,fcost,total from #category
211 union all
212 select category,fqty,famt,fcost,total from #total
213 '
214 end
215 if @Flevel = '23'
216 begin
217
218 set @sql = @sql +'select * into #total from (
219 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
220 union all
221 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by FItemID,FDate,sort
222
223 select category,fqty,famt,fcost,total from #category
224 union all
225 select category,fqty,famt,fcost,total from #total
226 '
227 end
228 if @Flevel = '2'
229 begin
230 set @sql = @sql +'
231 select category,fqty,famt,fcost,total from #category
232 union all
233 select category,fqty,famt,fcost,total from #RqLevel
234 'end
235 if @Flevel = '3'
236 begin
237 set @sql = @sql +'
238 select category,fqty,famt,fcost,total from #category
239 union all
240 select category,fqty,famt,fcost,total from #GoodsLevel
241 'end
242 if @Flevel = '0'
243 begin
244 set @sql = @sql +'
245 select category,fqty,famt,fcost,total from #category
246 'end
247 set @sql = @sql +'
248 drop table #category
249 drop table #StoreLevel
250 drop table #RqLevel
251 drop table #GoodsLevel'
252 if @Flevel = '123' or @Flevel = '12' or @Flevel = '13' or @Flevel = '13'
253 set @sql = @sql +'
254 drop table #total'
255
256 if @Flevel = '123'
257 set @sql = @sql +'
258 drop table #three'
259 --print @sql
260 exec(@sql)
261 end
