字段拼接 装换
USE [Ecom]
GO
/****** Object: StoredProcedure [dbo].[R_Merchant_PreOrder_ChargesData] Script Date: 03/15/2016 13:43:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[R_Merchant_PreOrder_ChargesData]
@Type Nvarchar(400),
@MerchantID Nvarchar(400),
@EffectiveDate Nvarchar(400),
@NarmalSelling Nvarchar(400)
AS
BEGIN
if(@Type = 'Without')
begin
if(@NarmalSelling = '' or @MerchantID = '')
begin
select 'Please Enter NarmalSelling'
select 'Please Enter NarmalSelling'
end
else
begin
select * into #a from(
select d.ID,
d.MerchantID,
d.State,
d.EffectiveDate,
d.ChargesType,
d.ChargesName,
d.ChargesCode,
d.InvoiceCol,
d.ValueType,
d.Value,
@NarmalSelling as NarmalSelling,
Tax= Case
WHEN c.ValueType = 'Rate' THEN @NarmalSelling*abs(cast(c.Value as decimal(18,2)))
WHEN c.ValueType = 'Number' THEN @NarmalSelling-abs(cast(c.Value as decimal(18,2)))
ELSE '0' END,
Comments = (select REPLACE(case when ValueType = 'Rate' then
CAST(CAST(Value * 100 AS float) as nvarchar(20)) + '% ' else '$' +
CAST(CAST(Value as Float) as nvarchar(20)) + ' ' end, '-', '') + ChargesCode
from Config_Order_Charges b where d.ID = b.ID FOR xml path(''))
from Config_Order_Charges d
left join
(select *
from Config_Order_Charges
where ID in(
select MAX(ID) as ID
from Config_Order_Charges a
inner join
(select ChargesName,
MAX(EffectiveDate) EffectiveDate
from Config_Order_Charges
group by ChargesName,MerchantID
having MerchantID = @MerchantID) b
on a.ChargesName=b.ChargesName and a.EffectiveDate=b.EffectiveDate
where a.MerchantID = @MerchantID
group by a.ChargesName)) c
on c.ID = d.ID
where d.MerchantID = @MerchantID) g
select * from #a order by ChargesName,EffectiveDate desc,ID desc
select * into #c from(select MerchantID,InvoiceCol,
SUM(Tax) as TotalTaxValue
from #a group by InvoiceCol,MerchantID having MerchantID=@MerchantID) h
select a.InvoiceCol,
a.TotalTaxValue,
stuff((SELECT ' and ' + c.Comments FROM #a AS c Where c.MerchantID = a.MerchantID and a.InvoiceCol=c.InvoiceCol and c.Tax != 0 FOR xml path('')), 1, 5, '') AS Comments
from #c a
drop table #a
drop table #c
end
end
else if(@Type = 'Indoor')
begin
if(@NarmalSelling = '' or @MerchantID = '')
begin
select 'Please Enter NarmalSelling'
select 'Please Enter NarmalSelling'
end
else
begin
select * into #b from(
select d.ID,
d.MerchantID,
d.State,
d.EffectiveDate,
d.ChargesType,
d.ChargesName,
d.ChargesCode,
d.InvoiceCol,
d.ValueType,
d.Value,
@NarmalSelling as NarmalSelling,
Tax= Case
WHEN c.ValueType = 'Rate' THEN @NarmalSelling*abs(cast(c.Value as decimal(18,2)))
WHEN c.ValueType = 'Number' THEN @NarmalSelling-abs(cast(c.Value as decimal(18,2)))
ELSE '0' END,
Comments = (select REPLACE(case when ValueType = 'Rate' then
CAST(CAST(Value * 100 AS float) as nvarchar(20)) + '% ' else '$' +
CAST(CAST(Value as Float) as nvarchar(20)) + ' ' end, '-', '') + ChargesCode
from Config_Order_ChargesToPreView b where d.ID = b.ID FOR xml path(''))
from Config_Order_ChargesToPreView d
left join
(select *
from Config_Order_ChargesToPreView
where ID in(
select MAX(ID) as ID
from Config_Order_ChargesToPreView a
inner join
(select ChargesName,
MAX(EffectiveDate) EffectiveDate
from Config_Order_ChargesToPreView
group by ChargesName,MerchantID
having MerchantID = @MerchantID) b
on a.ChargesName=b.ChargesName and a.EffectiveDate=b.EffectiveDate
where a.MerchantID = @MerchantID
group by a.ChargesName)) c
on c.ID = d.ID
where d.MerchantID = @MerchantID) h
select * from #b order by ChargesName,EffectiveDate desc,ID desc
select * into #d from (select MerchantID,InvoiceCol,
SUM(Tax) as TotalTaxValue
from #b
group by InvoiceCol,MerchantID
having MerchantID=@MerchantID) k
select a.InvoiceCol,
a.TotalTaxValue,
stuff((SELECT ' and ' + c.Comments FROM #b AS c Where c.MerchantID = a.MerchantID and a.InvoiceCol=c.InvoiceCol and c.Tax != 0 FOR xml path('')), 1, 5, '') AS Comments
from #d a
drop table #b
drop table #d
end
end
END
