CREATE procedure [control].[mp_GetSellerTerminalList_Export]
(
@Svalidtime nvarchar(30),
@Evalidtime nvarchar(30),
@UseStatus nvarchar(30),
@ProvinceNo nchar(100),
@CityNo nchar(100),
@DistributorName nvarchar(100),
@OrderType nvarchar(30),
@MobileNo nvarchar(100),
@SellerNO nvarchar(100),
@OrderNo nvarchar(50),
@StrSrcBatchNo nvarchar(100),
@GoodsName nvarchar(100),
@DeviceID nvarchar(100),
@EnterpriseName nvarchar(100),
@VoucherId nvarchar(100),
--加外部订单号
@ExOrderNo nvarchar(100)
--Svalidtime:2014-09-05,
--Evalidtime:2014-09-05,
--UseStatus:-1,
--ProvinceNo:*,
--CityNo:*,
--DistributorName:*,
--OrderType:-1,
--MobileNo:*,
--SellerNO:S104310001,
--OrderNo:*,
--StrSrcBatchNo:*,
--GoodsName:*,
--DeviceID:*,
--EnterpriseName:*,
--VoucherId:*,
--ExOrderNo:*
)
AS
begin
IF OBJECT_ID(N'tempdb.dbo.##tb_VoucherValidLogC') IS NOT NULL
Begin
DROP TABLE ##tb_VoucherValidLogC
End
IF OBJECT_ID(N'tempdb.dbo.##tb_VoucherB') IS NOT NULL
Begin
drop table ##tb_VoucherB;
End
IF OBJECT_ID(N'tempdb.dbo.##tb_VoucherDetailsA') IS NOT NULL
Begin
drop table ##tb_VoucherDetailsA;
End
IF OBJECT_ID(N'tempdb.dbo.##tb_OrderM') IS NOT NULL
Begin
drop table ##tb_OrderM;
End
--IF EXISTS(Select Name From Sysobjects Where Name='##tb_OrganizationG')
IF OBJECT_ID(N'tempdb.dbo.##tb_OrganizationG') IS NOT NULL
Begin
drop table ##tb_OrganizationG;
End
declare @whereSQl nvarchar(max);
declare @whereSQlB nvarchar(max);
set @whereSQl='1=1 ';
set @whereSQlB='1=1 ';
---------------------------C的SQL
declare @tb_VoucherValidLogC nvarchar(max);
declare @VoucherValidLogCsQL nvarchar(max);
set @VoucherValidLogCsQL=' 1=1';
if(@Svalidtime !='*' and @Evalidtime !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and convert(varchar(10),C.ValidTime,120) between '''+@Svalidtime+''''+' and '''+@Evalidtime+''''; end
if(@StrSrcBatchNo !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and (C.BatchNo+C.TradeNo) like ''%'+cast(@StrSrcBatchNo as varchar)+'%''';
end
if(@GoodsName !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.GoodsName like ''%'+cast(@GoodsName as varchar)+'%''';
set @whereSQl=@whereSQl+' and GoodsName is not null';
set @whereSQlB=@whereSQlB+' and b.GoodsName is not null';
end
if(@SellerNO !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.SellerNo='''+cast(@SellerNO as varchar)+'''';
set @whereSQl=@whereSQl+' and SellerNo is not null';
set @whereSQlB=@whereSQlB+' and b.SellerNo is not null';
end
--20140927----
if(@DeviceID !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.DeviceID like ''%'+cast(@DeviceID as varchar)+'%''';
set @whereSQl=@whereSQl+' and DeviceID is not null';
set @whereSQlB=@whereSQlB+' and b.DeviceID is not null';
end
if(@EnterpriseName !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.EnterpriseName like ''%'+cast(@EnterpriseName as varchar)+'%''';
set @whereSQl=@whereSQl+' and EnterpriseName is not null';
set @whereSQlB=@whereSQlB+' and b.EnterpriseName is not null';
end
if(@OrderType !='-1')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.OrderType='+cast(@OrderType as varchar);
set @whereSQl=@whereSQl+' and OrderType is not null';
set @whereSQlB=@whereSQlB+' and b.OrderType is not null';
end
-------------20140923ST
if(@VoucherId !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.VoucherId='''+cast(@VoucherId as varchar)+'''';
set @whereSQl=@whereSQl+' and VoucherId is not null';
set @whereSQlB=@whereSQlB+' and b.VoucherId is not null';
end
if(@OrderNo !='*')
begin
set @VoucherValidLogCsQL=@VoucherValidLogCsQL+' and C.orderNo='''+cast(@OrderNo as varchar)+'''';
set @whereSQl=@whereSQl+' and orderNo is not null';
set @whereSQlB=@whereSQlB+' and b.orderNo is not null';
end
-------------20140923End
print @VoucherValidLogCsQL;
--------------------------------------------B的SQL
declare @tb_VoucherBM nvarchar(max);
declare @voucherBsQL nvarchar(500);
set @voucherBsQL=' 1=1 ';
if(@UseStatus !='-1')
begin
set @voucherBsQL=@voucherBsQL+' and B.UseStatus='+cast(@UseStatus as varchar);
set @whereSQl=@whereSQl+' and UseStatus is not null';
set @whereSQlB=@whereSQlB+' and b.UseStatus is not null';
end
if(@MobileNo !='*')
begin
set @voucherBsQL=@voucherBsQL+' and B.MobileNo='''+cast(@MobileNo as varchar)+'''';
set @whereSQl=@whereSQl+' and MobileNo is not null';
set @whereSQlB=@whereSQlB+' and b.MobileNo is not null';
end
--if(@SellerNO !='*')
--begin
-- set @voucherBsQL=@voucherBsQL+' and B.SellerNo='''+cast(@SellerNO as varchar)+'''';
-- set @whereSQl=@whereSQl+' and SellerNo is not null';
-- set @whereSQlB=@whereSQlB+' and b.SellerNo is not null';
--end
--print @voucherBsQL;
------------------------G SQl
declare @tb_OrganizationG nvarchar(max);
declare @tb_OrganizationGsQL nvarchar(max);
set @tb_OrganizationGsQL=' 1=1';
if(@ProvinceNo !='*')
begin
set @tb_OrganizationGsQL=@tb_OrganizationGsQL+' and G.ProvinceNo='+cast(@ProvinceNo as varchar);
set @whereSQl=@whereSQl+' and ProvinceNo is not null';
set @whereSQlB=@whereSQlB+' and b.ProvinceNo is not null';
end
if(@CityNo !='*')
begin
set @tb_OrganizationGsQL=@tb_OrganizationGsQL+' and G.CityNo='+cast(@CityNo as varchar);
set @whereSQl=@whereSQl+' and CityNo is not null';
set @whereSQlB=@whereSQlB+' and b.CityNo is not null';
end
print 'orrr_____'+@tb_OrganizationGsQL
--------------------------------------------A与B的SQL
declare @tb_VoucherDetailsA nvarchar(max);
declare @tb_VoucherDetailsAsQL nvarchar(max);
set @tb_VoucherDetailsAsQL=' 1=1';
if(@DistributorName !='*')
begin
set @tb_VoucherDetailsAsQL=@tb_VoucherDetailsAsQL+' and A.DistributorName='''+cast(@DistributorName as varchar)+'''';
set @whereSQl=@whereSQl+' and DistributorName is not null';
set @whereSQlB=@whereSQlB+' and b.DistributorName is not null';
end
------------------------------------------------M与A
declare @tb_VoucherDetailsM nvarchar(max);
declare @tb_VoucherDetailsMsQL nvarchar(max);
set @tb_VoucherDetailsMsQL=' 1=1 ';
if(@ExOrderNo!='*')
begin
set @tb_VoucherDetailsMsQL=@tb_VoucherDetailsMsQL+' and M.ExOrderNo like ''%'+cast(@ExOrderNo as varchar)+'%''';
set @whereSQl=@whereSQl+' and ExOrderNo is not NULL';
set @whereSQlB=@whereSQlB+' and b.ExOrderNo is not NULL';
end
-------------------------------------
create table #mp_GetSellerTerminalListTable
(
CVoucherId nvarchar(100),
COrderNo nvarchar(100),
CEnterpriseName nvarchar(200),
CVoucherType nvarchar(100),
LogID int,
DistributorName nvarchar(100),
OrderType1 nvarchar(20),
VoucherType1 nvarchar(20),
VoucherId1 nvarchar(30),
MobileNo nvarchar(30),
StrSrcBatchNo nvarchar(30),
StrSrcTradeNo nvarchar(30),
SinglePrice nchar(20),
TolPrice nchar(20),
GoodsName nvarchar(50),
SettlementPrice nchar(20),
OrderNo nchar(50),
UseStatus nchar(20),
VoucherId nchar(50),
ExOrderNo nchar(50),
SellerNo nchar(30),
GoodsAmount nchar(20),
StoreName nvarchar(40),
ValidTime datetime,
DeviceID nchar(30),
VoucherNo nchar(30),
SettlementAllPrice nchar(20),
GoodsNum1 nchar(30),
OrderType nchar(20),
EnterpriseName nvarchar(40),
VoucherType nchar(20),
ValidateType1 nchar(20),
ValidateType nchar(20),
--ID nchar(30),
ValidNo nchar(20),
RePrintCount nchar(20),
GoodsNum nchar(30),
TradeNo nchar(20),
ProvinceNo nchar(20),
CityNo nchar(20),
AreaNo nchar(20),
AreaName nchar(40),
IsReset nchar(10)
)
create index index_validTime on #mp_GetSellerTerminalListTable(OrderNo);
declare @Sql nvarchar(max);
declare @coutongj nvarchar(max);
select D.[name],D.[value] into #dicDataD from base.tb_DictionaryData As D where D.dicId=1086;
select E.[name],E.[value] into #dicDataE from base.tb_DictionaryData AS E where E.dicId=1185;
select F.[name],F.[value] into #dicDataF from base.tb_DictionaryData AS F where F.dicId=1119;
--------------------------------------------C表临时表
set @tb_VoucherValidLogC='select C.BatchNo+C.TradeNo as StrSrcBatchNo, * into ##tb_VoucherValidLogC from control.tb_VoucherValidLog AS C
where '+@VoucherValidLogCsQL;
print @tb_VoucherValidLogC;
EXEC(@tb_VoucherValidLogC);
---------------------------------------------B 表
set @tb_VoucherBM='select distinct B.AllPointAmout,B.VoucherNo,B.OrderType,B.CustomerName,B.VoucherType,B.VoucherId,B.UseStatus,B.MobileNo,B.RePrintTimes into ##tb_VoucherB from control.tb_Voucher As B
where B.UseStatus<>5 and '+@voucherBsQL+' and B.VoucherNo in (select VoucherNo from ##tb_VoucherValidLogC AS C)';
print 'CCCCC___'+@tb_VoucherBM;
EXEC(@tb_VoucherBM);
---------------------------------------------G的临时表
--control.tb_Organization AS G
set @tb_OrganizationG='select G.ProvinceNo,G.CityNo,G.AreaNo,G.OrganizationNo into ##tb_OrganizationG from control.tb_Organization As G
where '+@tb_OrganizationGsQL;
EXEC(@tb_OrganizationG);
print @tb_OrganizationG
-----------------------------------------------A表与B
set @tb_VoucherDetailsA='select A.VoucherNo,A.OrderNo,A.OrderType,CASE WHEN B.OrderType IN(4,5) THEN B.CustomerName ELSE A.DistributorName END AS DistributorName,';
set @tb_VoucherDetailsA=@tb_VoucherDetailsA+'Convert(decimal(18,2),(CASE WHEN B.VoucherType=54 or B.VoucherType=60 THEN CASE WHEN B.AllPointAmout=0 THEN 0 ELSE A.SinglePrice/B.AllPointAmout END ELSE A.SinglePrice END)) AS SinglePrice ';
set @tb_VoucherDetailsA=@tb_VoucherDetailsA+'into ##tb_VoucherDetailsA from control.tb_VoucherDetails As A left JOIN ##tb_VoucherB As B on A.VoucherNo=B.VoucherNo ';
set @tb_VoucherDetailsA=@tb_VoucherDetailsA+' where'+ @tb_VoucherDetailsAsQL;
print '____cxj__'+@tb_VoucherDetailsA;
EXEC(@tb_VoucherDetailsA);
-----------------------------------------------------------------M与A2014/09/17
set @tb_VoucherDetailsM=
'select M.OrderNo,M.ExOrderNo into ##tb_OrderM from control.tb_Order As M
where '+@tb_VoucherDetailsMsQL+' and M.OrderNo in (
select A.OrderNo from ##tb_VoucherDetailsA AS A
)';
print @tb_VoucherDetailsM;
EXEC(@tb_VoucherDetailsM);
----------------------------------------------------------------
insert into #mp_GetSellerTerminalListTable
SELECT DISTINCT
C.VoucherId as CVoucherId,
YY.OrderNo as COrderNo,
C.EnterpriseName as CEnterpriseName,
C.VoucherType as CVoucherType,
C.LogID,
--(case when C.VoucherType=61 or C.VoucherType=60 then C.ProviderName else A.DistributorName end) AS DistributorName,
(case when C.VoucherType=60 then
case when B.OrderType=4 then B.CustomerName
when B.OrderType=1 then A.DistributorName
else kk.CustomerName
end
when C.VoucherType=61 then kk.CustomerName
when C.VoucherType=52 then
case when B.OrderType=2 then B.CustomerName
when B.OrderType=1 then A.DistributorName
else kk.CustomerName
end
else A.DistributorName end)
AS DistributorName,
D.[name] as OrderType1,
F.[name] as VoucherType1,
CASE
WHEN LEN(B.VoucherId)>0
THEN replace(B.VoucherId,substring(B.VoucherId,4,len(B.VoucherId)-7),'*********')
ELSE ''
END as VoucherId1,
(case
when B.VoucherType=53
then replace(B.MobileNo,substring(B.MobileNo,4,len(B.MobileNo)-7),'*********')
else B.MobileNo
end) as MobileNo,
--C.BatchNo+C.TradeNo AS StrSrcBatchNo,
C.StrSrcBatchNo,
C.SrcBatchNo+C.SrcTradeNo AS StrSrcTradeNo,
(case when C.VoucherType=61
then kk.SinglePrice
else A.SinglePrice
end) as SinglePrice,
Convert(decimal(18,2),
(case
when C.ValidateType=2
then -((CASE
WHEN B.VoucherType=54 or B.VoucherType=60
THEN
CASE WHEN B.AllPointAmout=0
THEN 0
else C.ConsumeAmount*A.SinglePrice
END
WHEN C.VoucherType=61
THEN kk.SinglePrice*C.ConsumeAmount
ELSE C.ConsumeAmount*A.SinglePrice
END))
ELSE
(CASE
WHEN B.VoucherType=54
THEN
CASE WHEN B.AllPointAmout=0
THEN 0
ELSE
C.ConsumeAmount*A.SinglePrice
END
WHEN C.VoucherType=61
THEN kk.SinglePrice*C.ConsumeAmount
ELSE C.ConsumeAmount*A.SinglePrice
END)END)) AS TolPrice,
C.GoodsName,C.SettlementPrice,
C.orderNo,
B.UseStatus,
--B.VoucherId AS VoucherId,
(case when B.VoucherId is Null then C.VoucherId else B.VoucherId end)AS VoucherId,
M.ExOrderNo,
--B.SellerNo
---20140925
(case when C.SellerNo is Null then kk.SellerNo else C.SellerNo end) as SellerNo
,
---20140926
C.GoodsAmount,C.StoreName,
C.ValidTime,C.DeviceID,C.VoucherNo,
/*
结算价
*/
(case when C.ValidateType=2
then -(C.SettlementAllPrice)
else
---2014092601
---2014092601
--C.SettlementAllPrice
case when C.VoucherType=61
--then C.SettlementPrice*YY.CardCount
then C.SettlementPrice*C.ConsumeAmount
else C.SettlementAllPrice
end
end
)as SettlementAllPrice,
(case when B.VoucherType=55
then convert(nvarchar(30),C.GoodsAmount)
when (B.VoucherType=53 or B.VoucherType=54 or B.VoucherType=60)
then convert(nvarchar(30),C.ConsumeAmount)
when (B.VoucherType=51 or B.VoucherType=52)
then convert(nvarchar(30),C.GoodsAmount)
end) as GoodsNum1,
C.OrderType,
C.EnterpriseName,B.VoucherType,E.[name] AS ValidateType1,C.ValidateType,
--B.ID,
C.TradeNo as ValidNo,
B.RePrintTimes AS RePrintCount,
(case when B.VoucherType=55
then convert(nvarchar(30),C.GoodsAmount)+'份'
when (B.VoucherType=53 or B.VoucherType=54 or C.VoucherType=60)
then convert(nvarchar(30),C.ConsumeAmount)+'点'
when (B.VoucherType=51 or B.VoucherType=52)
then convert(nvarchar(30),C.GoodsAmount)+'张'
--20140926
when C.VoucherType=61
then convert(nvarchar(30),C.ConsumeAmount)+'张'
--20140926
end) as GoodsNum,
C.TradeNo,
G.ProvinceNo,
G.CityNo,
G.AreaNo,
I.AreaName,
C.IsReSet
from ##tb_VoucherValidLogC AS C
left JOIN ##tb_VoucherB AS B ON C.VoucherNo=B.VoucherNo
left join control.tb_movieCard as YY on C.VoucherId=YY.CardNo
left join control.tb_CustomerOrder as kk on kk.OrderNo=C.OrderNo
left JOIN ##tb_VoucherDetailsA AS A ON A.VoucherNo=C.VoucherNo
LEFT JOIN #dicDataD AS D on D.[value]=B.OrderType
LEFT JOIN #dicDataE AS E on E.[value]=C.ValidateType
LEFT JOIN #dicDataF AS F on F.[value]=C.VoucherType
LEFT JOIN control.tb_Cinema AS H on H.CinemaNo=C.EnterpriseNo
LEFT JOIN ##tb_OrganizationG AS G on G.OrganizationNo=H.OrganizationNo
LEFT JOIN base.tb_Area AS I ON G.CityNo=I.AreaNo
LEFT JOIN ##tb_OrderM AS M ON M.OrderNo = C.OrderNo
--------------------------------------------------------------------20140926要加的
declare @SqlText nvarchar(max);
set @SqlText='select [DistributorName],[OrderType1],[OrderNo],[VoucherType1],[AreaName],[VoucherId1],[MobileNo],[StrSrcBatchNo],[StrSrcTradeNo],';
set @SqlText=@SqlText+'[SinglePrice],[TolPrice],[GoodsName],[SettlementPrice],[GoodsNum],[SettlementAllPrice],[EnterpriseName],[DeviceID],[ValidateType1]';
set @SqlText=@SqlText+',[ValidTime],[RePrintCount] from #mp_GetSellerTerminalListTable where '+@whereSQl;
print @SqlText;
EXEC(@SqlText);
drop table #dicDataD;
drop table #dicDataE;
drop table #dicDataF;
drop table ##tb_VoucherValidLogC;
drop table ##tb_VoucherB;
drop table ##tb_VoucherDetailsA;
drop table ##tb_OrderM;
drop table ##tb_OrganizationG;
drop table #mp_GetSellerTerminalListTable
end