USE [CoCo_Web]
GO
/****** Object: StoredProcedure [dbo].[proc_bi_netsales_report] Script Date: 2020/4/30 14:59:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_bi_netsales_report]
@userId varchar(10),
@companyname varchar(50),
@dbareaname varchar(50),
@gln varchar(20),
@branchid varchar(20),
@branchname varchar(50),
@taxclassname varchar(20),
@startdate varchar(25),
@enddate varchar(25)
as
--创建行转列临时标题字段表
create table #datetmp(sales_date date null)
--循环存入日期标题数据
declare @up_date date = cast(@startdate as date)
while(@up_date <= cast(@enddate as date))
begin
insert into #datetmp select @up_date --使用inser into 表 select 数据语法
select @up_date = DATEADD(DAY,1,@up_date) --插入完毕之后，新增一天
end
--select * from #datetmp
--日期拼接
declare @sql0 varchar(max) --返回标题中日期部分
SET @sql0 = ( SELECT DISTINCT STUFF(
( SELECT ',' + cast(sales_date as varchar) FROM #datetmp WITH ( NOLOCK )
order by convert(char(8),sales_date,112)
FOR XML PATH('')), 1, 1, '') AS T
FROM #datetmp)
--print @sql0
declare @sql1 varchar(max) --用于列转行
SET @sql1= ( SELECT DISTINCT STUFF(
( SELECT ',[' + cast(sales_date as varchar)+']'FROM #datetmp WITH ( NOLOCK ) order by convert(char(8),sales_date,112) FOR XML PATH('')), 1, 1, ''
) AS T FROM #datetmp)
--print @sql1
declare @sql2 varchar(max) --用于子查询，如果为空就显示0
SET @sql2= ( SELECT DISTINCT STUFF(
( SELECT '+isnull([' + cast(sales_date as varchar)+'],0)'FROM #datetmp WITH ( NOLOCK ) order by convert(char(8),sales_date,112) FOR XML PATH('')), 1, 1, ''
) AS T FROM #datetmp)
--print @sql2
declare @sql3 varchar(max) --赋标题日期别名
SET @sql3= ( SELECT DISTINCT STUFF(
( SELECT ',isnull([' + cast(sales_date as varchar)+'],0) as [' + cast(sales_date as varchar)+']'FROM #datetmp WITH ( NOLOCK )
order by convert(char(8),sales_date,112) FOR XML PATH('')), 1, 1, '') AS T FROM #datetmp)
--print @sql3
--0返回动态标题
Declare @output varchar(max)
set @output ='分公司,区域,全球编码,CN编码,门店名称,财务分类,当月合计,'+@sql0
select @output as title
--生成交叉表，获取财务分类详细数据
declare @sql varchar(max)
SET @sql= 'SELECT regionName 分公司,dbareaname 区域,gln 全球编码,branch_id CN编码,branchname 门店名称,tax_classname 财务分类,total 当月合计,'+@sql3+' FROM
(SELECT *,'+@sql2+' as total from (
SELECT b.regionname,b.dbareaname,b.gln,b.branch_id,b.branchname,n.tax_classname
,n.sales_Date,n.net_total,b.opendate
FROM sys_user u
join sys_user_branchoffice ub on u.user_id = ub.user_id
join sys_branchoffice_cn bo on ub.branchoffice_id = bo.branchoffice_id
join business_product_netsales n on bo.dbname=n.dbname and n.sales_Date between '''+@startdate+''' and '''+@enddate+'''
join bin_View as b on n.branch_id=b.branch_Id and n.dbname=b.dbname
and (1=(case when '''+isnull(@userId,'')+'''='''+''' then 1 else 0 end) or u.user_id='''+@userId+''')
and (1=(case when '''+isnull(@companyname,'')+'''='''+''' then 1 else 0 end) or b.regionName='''+@companyname+''')
and (1=(case when '''+isnull(@dbareaname,'')+'''='''+''' then 1 else 0 end) or b.dbareaname='''+@dbareaname+''')
and (1=(case when '''+isnull(@gln,'')+'''='''+''' then 1 else 0 end) or b.gln = '''+@gln+''')
and (1=(case when '''+isnull(@branchid,'')+'''='''+''' then 1 else 0 end) or b.branch_id = '''+@branchid+''')
and (1=(case when '''+isnull(@branchname,'')+'''='''+''' then 1 else 0 end) or b.branchname like ''%'+@branchname+'%'')
and (1=(case when '''+isnull(@taxclassname,'')+'''='''+''' then 1 else 0 end) or n.tax_classname = '''+@taxclassname+''')
) AS a
PIVOT(SUM(net_total) FOR sales_Date IN ('+@sql1+'))as hj ) AS t
order by branch_id
--order by opendate,gln '
exec (@sql)
print @sql
--生成交叉表，获取财务分类合计数据
declare @sqltotal varchar(max)
SET @sqltotal= 'SELECT regionName 分公司,dbareaname 区域,gln 全球编码,branch_id CN编码,branchname 门店名称,''合计'' 财务分类,total 当月合计,'+@sql3+' FROM
(SELECT *,'+@sql2+' as total from (
SELECT b.regionname,b.dbareaname,b.gln,b.branch_id,b.branchname,n.sales_Date,n.net_total,b.opendate
FROM sys_user u
join sys_user_branchoffice ub on u.user_id = ub.user_id
join sys_branchoffice_cn bo on ub.branchoffice_id = bo.branchoffice_id
join business_product_netsales n on bo.dbname=n.dbname and n.sales_Date between '''+@startdate+''' and '''+@enddate+'''
join bin_View as b on n.branch_id=b.branch_Id and n.dbname=b.dbname
and (1=(case when '''+isnull(@userId,'')+'''='''+''' then 1 else 0 end) or u.user_id='''+@userId+''')
and (1=(case when '''+isnull(@companyname,'')+'''='''+''' then 1 else 0 end) or b.regionName='''+@companyname+''')
and (1=(case when '''+isnull(@dbareaname,'')+'''='''+''' then 1 else 0 end) or b.dbareaname='''+@dbareaname+''')
and (1=(case when '''+isnull(@gln,'')+'''='''+''' then 1 else 0 end) or b.gln = '''+@gln+''')
and (1=(case when '''+isnull(@branchid,'')+'''='''+''' then 1 else 0 end) or b.branch_id = '''+@branchid+''')
and (1=(case when '''+isnull(@branchname,'')+'''='''+''' then 1 else 0 end) or b.branchname like ''%'+@branchname+'%'')
--and (1=(case when '''+isnull(@taxclassname,'')+'''='''+''' then 1 else 0 end) or n.tax_classname = '''+@taxclassname+''')
) AS a
PIVOT(SUM(net_total) FOR sales_Date IN ('+@sql1+'))as hj ) AS t
order by branch_id
--order by opendate,gln '
exec (@sqltotal)
-- print @sqltotal
