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 end101 if @FServiceID<>‘zwc‘102 begin103 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 end108 if @FCompanynumber<>‘zwc‘109 begin110 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 end115 if @FCompanyID<>‘zwc‘116 begin117 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 end122 if @FDeptID<>‘zwc‘123 begin124 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 end129 130 set @sql2=@sql2+‘ group by c.FItemID,c.fname ‘131 if @Flevel = ‘2‘132 begin133 set @sql3=@sql3+‘ group by CONVERT(varchar(10), b.FDate, 23) ‘134 end135 else136 begin137 set @sql3=@sql3+‘ group by c.FItemID,c.fname,CONVERT(varchar(10), b.FDate, 23) ‘138 end139 if @Flevel = ‘123‘140 begin141 set @sql4 = @sql4 +‘ group by CONVERT(varchar(10), b.FDate, 23),c.FItemID,e.FName‘142 end143 else144 begin145 if @Flevel = ‘3‘146 begin147 set @sql4 = @sql4 +‘ group by e.FItemID,e.FName‘148 end149 else150 begin151 set @sql4 = @sql4 +‘ group by c.FItemID,e.FName,CONVERT(varchar(10), b.FDate, 23)‘152 end153 end154 --print @sql1155 --print @sql2156 --print @sql3157 --exec (@sql1)158 --exec (@sql2)159 --exec (@sql3)160 set @sql = @sql1 + @sql2 + @sql3 + @sql4161 if @Flevel = ‘123‘162 begin163 set @sql = @sql +‘164 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #three165 from (166 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel167 union all168 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a169  order by FItemID,FDate,sort170 171 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #total172 from (173 select FItemID,‘‘2000-01-01‘‘ as FDate,sort,category,fqty,famt,fcost,total from #StoreLevel174 union all175 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #three ) a 176 order by FItemID,FDate,sort177 178 select category,fqty,famt,fcost,total from #category179 union all180 select category,fqty,famt,fcost,total from #total‘181 end182 if @Flevel = ‘12‘183 begin184 set @sql = @sql +‘185 select category,fqty,famt,fcost,total into #total186 from (187 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel188 union all189 select FItemID,sort,category,fqty,famt,fcost,total from #RqLevel) a order by FItemID,sort190 191 select category,fqty,famt,fcost,total from #category192 union all193 select category,fqty,famt,fcost,total from #total‘194 end195 if @Flevel = ‘1‘196 begin197 set @sql = @sql +‘198 select category,fqty,famt,fcost,total from #category199 union all200 select category,fqty,famt,fcost,total from #StoreLevel201 ‘202 end203 if @Flevel = ‘13‘204 begin205 set @sql = @sql +‘select *  into #total from (206 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel207 union all208 select FItemID,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,sort209 210 select category,fqty,famt,fcost,total from #category211 union all212 select category,fqty,famt,fcost,total from #total213 ‘214 end215 if @Flevel = ‘23‘216 begin217 218 set @sql = @sql +‘select *  into #total from (219 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel220 union all221 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,FDate,sort222 223 select category,fqty,famt,fcost,total from #category224 union all225 select category,fqty,famt,fcost,total from #total226 ‘227 end228 if @Flevel = ‘2‘229 begin230 set @sql = @sql +‘231 select category,fqty,famt,fcost,total from #category232 union all233 select category,fqty,famt,fcost,total from #RqLevel234 ‘end235 if @Flevel = ‘3‘236 begin237 set @sql = @sql +‘238 select category,fqty,famt,fcost,total from #category239 union all240 select category,fqty,famt,fcost,total from #GoodsLevel241 ‘end242 if @Flevel = ‘0‘243 begin244 set @sql = @sql +‘245 select category,fqty,famt,fcost,total from #category246 ‘end247 set @sql = @sql +‘248 drop table #category249 drop table #StoreLevel250 drop table #RqLevel251 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 @sql260 exec(@sql)261 end