USE [hzoxkj]
GO
/****** Object: Trigger [dbo].[kehumoneyInsert] Script Date: 2017/3/1 8:50:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[kehumoneyInsert] ON [dbo].[kehumoney]
after insert
AS
BEGIN
declare @firstdate datetime
declare @fenddate datetime
declare @fcode varchar(100)
declare @id varchar(100)
declare @fqty varchar(100)
declare @totalfee varchar(100)
declare @fangan varchar(100)
declare @fccweight varchar(100)
declare @fzzweight varchar(100)
declare @frate varchar(100)
declare @fprovince varchar(100)
declare @fprice varchar(100)
declare @fqty1 varchar(100)
declare @fqty2 varchar(100)
declare @totalfee1 varchar(100)
declare @totalfee2 varchar(100)
--定位插入的日期，客户
select @firstdate=firstdate,@fenddate=fenddate,@id=id,@fcode=fcode from inserted
--找到该客户的优惠方案
select @fangan=fangan from kh_formula where username=@fcode
--找到所有在日期和客户之间的数量
select @fqty=COUNT(*) from khcount where currdate<@fenddate and currdate>@firstdate and customno=@fcode
--找到所有在日期和客户之间的运费
select @totalfee=sum(cast(totalfee as float)) from khcount where currdate<@fenddate and currdate>@firstdate and customno=@fcode
DECLARE #point_cursor CURSOR
FOR
SELECT fccweight,fzzweight,frate,fprovince,fprice
FROM fanganentry a1 inner join fangan b1 on a1.fangan_id=b1.id where b1.fname= @fangan --每条信息从头到尾的写入
OPEN #point_cursor
FETCH NEXT FROM #point_cursor INTO @fccweight,@fzzweight,@frate,@fprovince,@fprice
while @@fetch_status = 0
BEGIN
--排除不优惠的省份，对优惠的省份的数量进行优惠判断
select @totalfee1=sum(cast(totalfee as float)),@fqty1=COUNT(*) from khcount c1 inner join zipcode_detail d1 on c1.zipcode=d1.zipcode
where currdate<@fenddate and currdate>@firstdate and customno=@fcode and d1.province !='新疆' and d1.province !='西藏' and d1.province !='青海'
--不优惠省份的金额
select @totalfee2=sum(cast(totalfee as float)),@fqty2=COUNT(*) from khcount c1 inner join zipcode_detail d1 on c1.zipcode=d1.zipcode
where currdate<@fenddate and currdate>@firstdate and customno=@fcode and d1.province ='新疆' or d1.province ='西藏' or d1.province ='青海'
if(cast(@fccweight as int) <cast(@fqty1 as int)and cast(@fzzweight as int)>cast(@fqty1 as int) )
begin
--select 1
update kehumoney set fyje=cast(@totalfee1 as float)*cast(@frate as float)+cast(@totalfee2 as float) where id=@id
update kehumoney set fyqty=@fqty1 where id=@id
end
FETCH NEXT FROM #point_cursor INTO @fccweight,@fzzweight,@frate,@fprovince,@fprice
END
CLOSE #point_cursor
DEALLOCATE #point_cursor
update kehumoney set fmoney=@totalfee where id=@id
update kehumoney set fqty=@fqty where id=@id
--select * from zipcode_detail where zipcode ='226100';
--select * from kh_formula
--select * from
--订单邮编对应省份
--select @province = province from zipcode_detail where zipcode= @zipcode
--select @khprovince =getprovince from kh_formula where username =@customno
end
--select sum(cast(totalfee as float)) from khcount 西藏,青海,新疆
--select COUNT(*),d1.province from khcount c1 inner join zipcode_detail d1 on c1.zipcode=d1.zipcode
--where d1.province like ('%西藏,青海,新疆,江苏%') group by d1.province
--select sum(cast(totalfee as float)) from khcount c1 inner join zipcode_detail d1 on c1.zipcode=d1.zipcode
--where customno='12345688' and d1.province !='新疆' and d1.province !='西藏' and d1.province !='青海'
--select COUNT(*) from khcount where customno='12345688'
-- select sum(cast(totalfee as float)) from khcount c1 inner join zipcode_detail d1 on c1.zipcode=d1.zipcode
--where customno='12345688' and d1.province ='新疆' or d1.province ='西藏' or d1.province !='青海'
--select * from fanganentry
--select cast(89 as float)*cast(0.95 as float)