USE [MoponZhongYingGroup_cs]
GO

/****** Object: StoredProcedure [dbo].[TempCountCimetomg] Script Date: 12/24/2014 15:24:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--EXEC TempCountCimetomg ‘‘,‘‘,‘2014-12-19‘,‘2014-12-22‘,‘2014-12-19‘,0

alter procedure [dbo].[TempCountCimetomg]
(@CinemaNo VARCHAR(30), --影院编号
@CinemaName VARCHAR(50), --影院名称
@StartDate VARCHAR(20), --开始时间
@EndDate VARCHAR(20), --结束时间
@SearchDate VARCHAR(20),--查询时间
@isGroupClient INT --是否按客户端分类
)


AS
BEGIN
IF(len(@StartDate)<1 OR len(@EndDate)<1) --必须输入时间范围
RETURN 0

DECLARE @sqlIf VARCHAR(4000)
DECLARE @sql VARCHAR(max)
DECLARE @PayChannelNo VARCHAR(20) --支付方式
DECLARE @PayChannelList VARCHAR(500) --支付方式列表

SET @PayChannelList=‘TCARDPAY,USERCARD,CPTPAY,WEB,CLIENT,ACT,All‘
--定义表
DECLARE @PayChannelTb TABLE (
PayChannelNo VARCHAR(20) NOT NULL
)
--调用control.f_splitToTable 函数将数据插到@PayChannelTb表中去
INSERT INTO @PayChannelTb SELECT col FROM control.f_splitToTable(@PayChannelList,‘,‘) fstt


SELECT oe.OrderNo,ae.AreaName,ce.CinemaNo,ce.OrganizationName,oe.CreateTime,ode.showType,
ode.PiaoFangPrice,ode.OrderType,oe.ClientNo,oe.ClientName,isnull(th.IsVipHall,0) IsVipHall,
ode.RealSinglePrice,ode.SubsidyPrice,
ode.ChargeSinglePrice ,oe.PayTime,oe.[Status]
,[PayChannelNo]=
(case
when (SELECT SUM(ExchangeFlag) FROM control.tb_OrderDetail WHERE OrderNo=oe.OrderNo GROUP BY OrderNo )>0
THEN ‘CPTPAY‘
ELSE
--这里也可以写查询语句
(SELECT top 1 tpod.PayChannelNo
FROM control.tb_PayOrder tpo INNER JOIN control.tb_PayOrderDetail tpod ON
tpo.PayOrderNo=tpod.PayOrderNo
AND tpod.[Status]=2
AND tpo.[Status]=3
AND tpo.OrderNo=ode.OrderNo
AND tpod.PayChannelNo<>‘CPTPAY‘)
END)
-- 现将所有数据放到临时表#temptb中去 into #temptb
INTO #temptb
FROM control.tb_Order oe
inner join control.tb_OrderDetail ode on oe.OrderNo=ode.OrderNo
inner join control.tb_Cinema ce on oe.CinemaNo=ce.CinemaNo
inner join control.tb_Organization oze on ce.OrganizationNo=oze.OrganizationNo
inner join base.tb_Area ae on oze.CityNo=ae.AreaNo
--满足多条件用And
LEFT JOIN control.tb_Hall th ON th.[Status]=1 AND th.HallNo=ode.HallNo
WHERE oe.TicketStatus =3
AND ode.OrderType IN (1,2)
AND oe.[Status] in (1,5,6)
AND oe.PayStatus=3
--注意这里的写法.
AND oe.CinemaNo=(case when len(@CinemaNo)=0 then oe.CinemaNo else @CinemaNo end)
AND oe.CinemaName like (case when len(@CinemaName)=0 then oe.CinemaName else ‘%‘+@CinemaName+‘%‘ end)
AND oe.CreateTime >= @StartDate+‘ 06:00:00‘
AND oe.CreateTime < CONVERT(varchar(10),dateadd(day,1,@EndDate),120)+‘ 06:00:00‘
--
select * from #temptb;
select ‘A‘;
--这里是额外添加的
--原
SELECT ‘原‘ DataType, * into #temptbOriginal FROM #temptb
WHERE [Status]=1
--补
SELECT ‘补‘ DataType,* into #temptbSupplement FROM #temptb
WHERE CONVERT(VARCHAR(10),PayTime,120) > CONVERT(VARCHAR(10),CreateTime,120) AND CONVERT(VARCHAR(10),PayTime,120)<=@SearchDate
--退
SELECT ‘退‘ DataType,* into #temptbRefund FROM #temptb WHERE [Status] IN (5,6)

SELECT @sqlIf=‘‘,@sql=‘‘

DECLARE @DataTypeList VARCHAR(200)
DECLARE @DataType VARCHAR(200)
SET @DataTypeList=‘Original,Supplement,Refund‘
DECLARE @DataTypeTb TABLE (
DataType VARCHAR(20) NOT NULL
)

--while Exists 一直执行 到不存在为止
--if Exists 执行一次
WHILE EXISTS(SELECT TOP 1 * FROM @PayChannelTb pct) --支付方式循环开始
--注意while循环的方式
BEGIN
SELECT @sql=‘‘
---循环取值@PayChannelNo=（TCARDPAY,USERCARD,CPTPAY,WEB,CLIENT,ACT,All）
SELECT TOP 1 @PayChannelNo=[PayChannelNo] FROM @PayChannelTb pct

--二重循环
DELETE @DataTypeTb
INSERT INTO @DataTypeTb SELECT col FROM control.f_splitToTable(@DataTypeList,‘,‘) fstt
WHILE EXISTS(SELECT TOP 1 * FROM @DataTypeTb ) --数据类型（DataType）循环开始
BEGIN
---循环取值 原 退 补
SELECT TOP 1 @DataType=[DataType] FROM @DataTypeTb

IF(@PayChannelNo=‘WEB‘)
BEGIN
SET @sqlIf= ‘ and PayChannelNo IN (‘‘ALIPAY10‘‘,‘‘UPOP6‘‘) and OrderType=1 and ClientNo <>‘‘C10000006‘‘‘
END
else IF(@PayChannelNo=‘CLIENT‘)
BEGIN
SET @sqlIf= ‘ and PayChannelNo IN (‘‘ALIPAY10‘‘,‘‘UPOP6‘‘) and OrderType=1 and ClientNo=‘‘C10000006‘‘‘
END
else IF(@PayChannelNo=‘ACT‘)
BEGIN
SET @sqlIf= ‘ and OrderType=2‘
END
ELSE IF(@PayChannelNo=‘All‘)
BEGIN
SET @sqlIf= ‘ and PayChannelNo not in (‘‘THIRDPAY‘‘,‘‘UPMP3‘‘)‘
END
ELSE
BEGIN
SET @sqlIf= ‘ and PayChannelNo=‘‘‘+@PayChannelNo+‘‘‘ and OrderType=1 ‘
END

SET @sql=@sql+‘ SELECT DataType,CinemaNo,OrganizationName as CinemaName ‘
IF(@isGroupClient=1)
BEGIN
set @sql=@sql+‘,ClientName,ClientNo‘;
END
ELSE
BEGIN
set @sql=@sql+‘ ,‘‘合计‘‘ as ClientName,‘‘‘‘ ClientNo ‘;
END

SET @sql=@sql+‘,[‘+@PayChannelNo+‘_2D]=SUM(CASE WHEN showType=‘‘2D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_3D]=SUM(CASE WHEN showType=‘‘3D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_IMAX2D]=SUM(CASE WHEN showType=‘‘IMAX 2D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_IMAX3D]=SUM(CASE WHEN showType=‘‘IMAX 3D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_DMAX2D]=SUM(CASE WHEN showType=‘‘DMAX 2D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_DMAX3D]=SUM(CASE WHEN showType=‘‘DMAX 3D‘‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[‘+@PayChannelNo+‘_Total]=count(1)
,[‘+@PayChannelNo+‘_SettlementPrice]=sum(RealSinglePrice)
,[‘+@PayChannelNo+‘_Reserve_G]=0
,[‘+@PayChannelNo+‘_Reserve_C]=0
,[‘+@PayChannelNo+‘_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[‘+@PayChannelNo+‘_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[‘+@PayChannelNo+‘_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptb‘+@DataType+‘
where 1=1 ‘+@sqlIf+‘
GROUP BY DataType,CinemaNo,OrganizationName ‘
--#temptbOriginal #temptbSupplement #temptbRefund

IF(@isGroupClient=1)
BEGIN
set @sql=@sql+‘,ClientNo,ClientName‘
END
--set @sql=@sql+‘ order by CinemaNo‘

DELETE @DataTypeTb WHERE [DataType]=@DataType
IF(EXISTS(SELECT TOP 1 * FROM @DataTypeTb ))
set @sql=@sql+‘ UNION ALL ‘
END --数据类型（DataType）循环结束

print @sql;
exec (@sql);


DELETE @PayChannelTb WHERE [PayChannelNo]=@PayChannelNo

END --支付方式循环结束



DROP TABLE #temptb
DROP TABLE #temptbOriginal
DROP TABLE #temptbSupplement
DROP TABLE #temptbRefund


END

GO

 ---------------输出的print @sql

(7 行受影响)

(77 行受影响)

(77 行受影响)

(1 行受影响)

(77 行受影响)

(0 行受影响)

(0 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo=‘TCARDPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo=‘TCARDPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[TCARDPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[TCARDPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[TCARDPAY_Total]=count(1)
,[TCARDPAY_SettlementPrice]=sum(RealSinglePrice)
,[TCARDPAY_Reserve_G]=0
,[TCARDPAY_Reserve_C]=0
,[TCARDPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[TCARDPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[TCARDPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo=‘TCARDPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(3 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo=‘USERCARD‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo=‘USERCARD‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[USERCARD_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[USERCARD_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[USERCARD_Total]=count(1)
,[USERCARD_SettlementPrice]=sum(RealSinglePrice)
,[USERCARD_Reserve_G]=0
,[USERCARD_Reserve_C]=0
,[USERCARD_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[USERCARD_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[USERCARD_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo=‘USERCARD‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo=‘CPTPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo=‘CPTPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CPTPAY_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CPTPAY_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CPTPAY_Total]=count(1)
,[CPTPAY_SettlementPrice]=sum(RealSinglePrice)
,[CPTPAY_Reserve_G]=0
,[CPTPAY_Reserve_C]=0
,[CPTPAY_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CPTPAY_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CPTPAY_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo=‘CPTPAY‘ and OrderType=1
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[WEB_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo <>‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[WEB_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo <>‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[WEB_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[WEB_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[WEB_Total]=count(1)
,[WEB_SettlementPrice]=sum(RealSinglePrice)
,[WEB_Reserve_G]=0
,[WEB_Reserve_C]=0
,[WEB_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[WEB_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[WEB_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo <>‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo=‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo=‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[CLIENT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[CLIENT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[CLIENT_Total]=count(1)
,[CLIENT_SettlementPrice]=sum(RealSinglePrice)
,[CLIENT_Reserve_G]=0
,[CLIENT_Reserve_C]=0
,[CLIENT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[CLIENT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[CLIENT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo IN (‘ALIPAY10‘,‘UPOP6‘) and OrderType=1 and ClientNo=‘C10000006‘
GROUP BY DataType,CinemaNo,OrganizationName

(1 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[ACT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[ACT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[ACT_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[ACT_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[ACT_Total]=count(1)
,[ACT_SettlementPrice]=sum(RealSinglePrice)
,[ACT_Reserve_G]=0
,[ACT_Reserve_C]=0
,[ACT_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[ACT_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[ACT_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and OrderType=2
GROUP BY DataType,CinemaNo,OrganizationName

(0 行受影响)

(1 行受影响)

(0 行受影响)

(3 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[All_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbOriginal
where 1=1 and PayChannelNo not in (‘THIRDPAY‘,‘UPMP3‘)
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[All_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbSupplement
where 1=1 and PayChannelNo not in (‘THIRDPAY‘,‘UPMP3‘)
GROUP BY DataType,CinemaNo,OrganizationName UNION ALL SELECT DataType,CinemaNo,OrganizationName as CinemaName ,‘合计‘ as ClientName,‘‘ ClientNo ,[All_2D]=SUM(CASE WHEN showType=‘2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_3D]=SUM(CASE WHEN showType=‘3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX2D]=SUM(CASE WHEN showType=‘IMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_IMAX3D]=SUM(CASE WHEN showType=‘IMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX2D]=SUM(CASE WHEN showType=‘DMAX 2D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_DMAX3D]=SUM(CASE WHEN showType=‘DMAX 3D‘ and IsVipHall=0 THEN 1 ELSE 0 END )
,[All_Vip]=SUM(CASE WHEN IsVipHall=1 THEN 1 ELSE 0 END )
,[All_Total]=count(1)
,[All_SettlementPrice]=sum(RealSinglePrice)
,[All_Reserve_G]=0
,[All_Reserve_C]=0
,[All_Reserve_P]=sum(isnull(SubsidyPrice,0))
,[All_ChargePrice]=sum(isnull(ChargeSinglePrice,0))
,[All_AllMoney]=sum(RealSinglePrice)+sum(isnull(SubsidyPrice,0))
from #temptbRefund
where 1=1 and PayChannelNo not in (‘THIRDPAY‘,‘UPMP3‘)
GROUP BY DataType,CinemaNo,OrganizationName

(4 行受影响)

(1 行受影响)

--------------------------------------------EasyUI前端的显示是：

复杂报表的存储过程