Hn_SellPrize_20110101

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:  Sidney
-- ALTER  date: 2012-3-23
-- Description: 用户信息修改
-- =============================================
alter PROCEDURE [dbo].[Hn_SellPrize_20110101]
@al_orno int,
@as_ym char(7),
@as_ctym CHAR(7),
@ai_cttimes int,
@al_return int,
@as_info varchar(500) OUT
AS
BEGIN
declare @li_have_row int
declare @li_ct int
declare @ls_gs varchar(1000)
if ISNULL(@as_ctym,'')=''  --重提日期为空为正常TJ
begin
 set @as_ctym=@as_ym
 set @li_ct=0
end
else --为重提
begin
 set @ls_gs=@ls_gs+''
 set @li_ct=1
end
set @ls_gs=@ls_gs+'(20110101)'
set @as_info='(20110101)'
--清除该订单该奖项该月TJ结果表中的计算结果
if @li_ct=0  --重提的已经在重提前一次性删除所有TJ月的TJ结果，不需要再删除 ？？isnull(t_cttimes,0)=@ai_cttimes该条件是否需要？
begin
 delete from tbl_priseresult where t_orno=@al_orno and (t_priz=3 or t_priz=10) and t_yymm=@as_ym and isnull(t_adjust,0)<>1 and isnull(t_cttimes,0)=@ai_cttimes
end
--获取订单信息Begin
declare @li_zfz int
declare @li_zzj int,@li_xxj int,@li_brdq int,@li_zzzj int
declare @li_080901 int, @li_xlh int,@li_lsl int,@li_aj4 int
declare @li_070516 int
declare @ldb_dkr numeric(18,4)
declare @li_mort_years int,@li_mort_per int,@li_416 int,@ld_getdate datetime ,@li_hg int,@li_qk int
declare @li_yj int,@li_PayFlag int,@li_exch int,@li_LawFlag int, @li_zzz int,@ls_zzz char(7),@ldb_OrderSum numeric(18,4)
declare @ls_OrderLevel char(5),@li_BackFlag int,@li_CustomType int,@ls_ProdType char(20),@ldt_SendDate datetime,@li_CompCode int
declare @ls_CrewId char(20),@ll_company int,@ll_province int,@li_orderFlag int,@ll_corp int,@li_jxs int,@ldb_yxdb int,@ls_zfz char(7)
declare @ldb_Accuracy numeric(18,4),@li_jsb int,@li_yjhx int,@ldb_zs numeric(18,4), @ldb_yhq numeric(18,4), @ldb_nopp numeric(18,4), @ldb_xzjbc numeric(18,4), @li_ajqzsk int,@li_sx int
select
@li_zfz=isnull(t_cl,0),
@li_zzj=isnull(t_zzj,0),
@li_xxj=isnull(t_xxj,0),
@li_brdq=isnull(t_brdq,0),
@li_zzzj=isnull(t_zzzj,0),
@li_080901=datediff(day,'2008-08-31',t_sdat),
@li_xlh=isnull(t_xlh,0),
@li_lsl=isnull(t_lls,0),
@li_aj4=isnull(t_aj4,0),
@ldb_dkr=isnull(t_dkr,0),
@li_070516=datediff(day,'2007-05-15',t_sdat),
@li_mort_per=t_mort_years,
@li_mort_per=t_mort_per,
@li_416=datediff(day,'2007-04-12',t_sdat),
@ld_getdate=getdate(),
@li_hg=isnull(t_rtn,0),
@li_qk=isnull(t_qk,0),
@li_yj=isnull(t_yj,0),
@li_PayFlag=isnull(t_pamd,0),
@li_exch=isnull(t_exch,0),
@li_LawFlag=isnull(t_cl,0),
@li_zzz=isnull(t_zzj,0),
@ls_zzz=isnull(convert(char(7),t_zzsj,121),'2020-01'),
@ldb_OrderSum=t_amnt,
@ls_OrderLevel=isnull(t_grad,'C'),
@li_BackFlag=isnull(t_rtnd,0),
@li_CustomType=isnull(t_cutp,0),
@ls_ProdType=t_item,
@ldt_SendDate=t_sdat,
@li_CompCode=t_company,
@ls_CrewId=t_emno,
@ll_company=t_company,
@ll_province=t_province,
@li_orderFlag=t_flag,
@ll_corp=t_org,
@li_jxs=isnull(t_jxs,0),
@ldb_yxdb=isnull(t_yxdb,0),
@ls_zfz=isnull(convert(char(7),t_cldat,121),'2050-01'),
@ldb_Accuracy=isnull(t_accuracy,0),
@li_jsb=isnull(t_jsb,0),
@li_yjhx=isnull(t_yjhx,0),
@ldb_zs=isnull(t_prst,0),
@ldb_yhq=isnull(t_yhq,0),
@ldb_nopp=isnull(t_nopp,0),
@ldb_xzjbc=isnull(t_xzjbc,0),
@li_ajqzsk =isnull(t_ajqzsk,0),
@li_sx=isnull(t_ppas,0)
from tbl_sls
where t_orno=@al_orno
--获取订单信息END
--订单信息处理BEGIN
if @li_LawFlag =1
begin
 if @ls_zfz>@as_ym set @li_LawFlag = 0
end

if  @li_zzz = 1
begin
 if @ls_zzz>@as_ym set @li_zzz = 0
end

if @li_PayFlag=6 set @li_qk=1  --根据付未类型确定是否为全款
--订单信息处理END

if  @li_xlh=1 --销售新利恒产品，不计销售奖
begin
  set @as_info='销售新利恒产品，不计销售奖!'
  select @as_info as info, 0 as rtn,0 priz
  return 0
end

if @li_LawFlag=1
begin
 set @as_info=@as_info+'订单转法制，不提销售奖'
  --将错误信息写入系统日志表中
 insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('一般提示',@al_orno,@as_ym,2,@as_info)
  select @as_info as info, 0 as rtn,0 priz
  return 0
end
--转法制
if @li_zzz=1
begin
  set @as_info=@as_info+'订单转专制，不提销售奖!'
 --将错误信息写入系统日志表中
 insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('一般提示',@al_orno,@as_ym,2,@as_info)
   select @as_info as info, 0 as rtn,0 priz
  return 0
end
--换物
declare @ldb_exchPara numeric(18,4)
if @li_exch=1
begin
 set @ldb_exchPara=0.8
end
else
begin 
 set @ldb_exchPara=1
end

declare @ldb_info numeric(18,4),@ldb_sale_per numeric(18,4),@ldb_BasicPrize numeric(18,4),@ldb_LowPrize  numeric(18,4)
declare @ldb_LevelA numeric(18,4),@ldb_LevelA1 numeric(18,4),@ldb_LevelB numeric(18,4),@ldb_LevelC numeric(18,4)
declare @ldb_LevelC1 numeric(18,4),@ldb_LevelD numeric(18,4),@ldb_a_per numeric(18,4),@ldb_b_per numeric(18,4)
declare @ldb_c_per numeric(18,4),@ldb_d_per numeric(18,4),@ls_type char(20),@ldb_sale_a numeric(18,4)
declare @ldb_sale_b numeric(18,4),@ldb_fsyzjxs numeric(18,4)

----获取提奖参数
set @li_have_row=0
select top 1 @li_have_row=1,
       @ldb_info=isnull(t_info,0),
       @ldb_sale_per=isnull(t_jbc_xsjbl,0.008),
       @ldb_BasicPrize=isnull(t_sale,0),
       @ldb_LowPrize=isnull(t_lpri,0),
       @ldb_LevelA=isnull(t_a,0),
       @ldb_LevelA1=isnull(t_a1,0),
       @ldb_LevelB=isnull(t_b,0),
       @ldb_LevelC=isnull(t_c,0),
       @ldb_LevelC1=isnull(t_c1,0),
       @ldb_LevelD=isnull(t_d,0),
       @ldb_a_per=isnull(t_a_per,0),
       @ldb_b_per=isnull(t_b_per,0),
       @ldb_c_per=isnull(t_c_per,0),
       @ldb_d_per=isnull(t_d_per,0),
       @ls_type=t_type,
       @ldb_sale_a=isnull(t_sale_a,0),
       @ldb_sale_b=isnull(t_sale_b,0),
       @ldb_fsyzjxs=isnull(t_fsyzjxs,0)
from tbl_proprize
where t_item=@ls_ProdType and  t_udat<=@ldt_SendDate order by t_udat desc;
if @li_have_row=0
begin
set @as_info=@as_info+'获取提奖参数失败!'
 --将错误信息写入系统日志表中
 insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('错误',@al_orno,@as_ym,2,@as_info)
   select @as_info as info, -1 as rtn,0 priz
  return 0
end

declare @ldb_ppri numeric(18,4)
----获取提奖参数
set @li_have_row=0
set @ldb_ppri=0
select top 1 @ldb_ppri=t_ppri, @li_have_row=1 from tbl_proprice
where t_item=@ls_ProdType and t_udat<=@ldt_SendDate order by t_udat desc
if @li_have_row=0
begin
set @as_info=@as_info+'获取提奖价格参数失败!'
 --将错误信息写入系统日志表中
 insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('错误',@al_orno,@as_ym,2,@as_info)
   select @as_info as info, -1 as rtn,0 priz
  return 0
end

 

--取得所有回款计划的总金额，该金额做为实际的合同金额
declare @ldb_plan_sum numeric(18,4)
select @ldb_plan_sum=sum(t_amnt)  from tbl_backorder where t_orno=@al_orno
if isnull(@ldb_plan_sum,0)<=0
begin
  set @as_info=@as_info+'回款计划为零!'
  insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('数据错误',@al_orno,@as_ym,2,@as_info)
  select @as_info as info, -1 as rtn,0 priz
  return
end
declare @ldb_amnt  numeric(18,4)
declare @ldb_Back  numeric(18,4)
--将回款计划做为总的合同金额
set @ldb_ordersum=@ldb_plan_sum
set @ldb_amnt=@ldb_ordersum
--从回款信息表中取得截止TJ月的实际回款金额
select @ldb_Back=isnull(sum(t_rtmn),0)  from tbl_retamn where t_orno=@al_orno and  convert(char(7),t_rdat,121)<=@as_ym
if @ldb_back is null  set @ldb_back=0

 -----
declare  @ldb_lpri  numeric(18,4)
set @ldb_fsyzjxs=0.5 --搅拌站非三一主机系数设定为0.5 2011
set @ls_type=rtrim(ltrim(@ls_type))
set @ldb_lpri=@ldb_LowPrize --保底佣金
--2008-06-17  力士乐主油泵保底佣金减半
if @li_lsl=1
begin
 set @ldb_lpri=@ldb_lpri/2
 set @ls_gs=@ls_gs+',力士乐主油泵。'
end

--2009.01.01换房换物抵款合同，保底佣金设为0
if @li_exch=1 set @ldb_lpri = 0

declare @ls_sdat char(10)
declare @ldb_zk numeric(18,4)
declare @ldb_cjj numeric(18,4)
declare @ldb_yjpara numeric(18,4)
select  @ls_sdat=convert(char(10),@ldt_senddate,121)
if @li_yj=1 -- //优价
begin
   set @ldb_cjj=@ldb_amnt - (@ldb_zs+@ldb_yhq)*0.5 - @ldb_zk  -@ldb_xzjbc --计算出实际价格(可能是多台的总价) add by wangq11 2010-07-21 ,2010-08-07 增加优价机赠送配件*0.5
   set @ldb_yjpara=@ldb_cjj/@ldb_ppri
   set @ls_gs=@ls_gs+'优价机,合同金额('+str(@ldb_cjj)+')/保护价('+str(@ldb_ppri)+')='+str(@ldb_cjj/@ldb_ppri)+','
end
else
begin 
 set @ldb_yjpara=1
end

--地区系数 2011 直接取自订单
--地区系数
declare @ldb_AreaPara numeric(18,4)
if @li_brdq=1  set @ldb_AreaPara=1.2
else  set @ldb_AreaPara=1

--取得是否已经提过销售奖的标志
declare @ll_sale_flag int
declare @ls_have_ym char(7)
declare @sale_count_sjc numeric(18,4) --销售奖已提比率
set @ll_sale_flag=0
set @ls_have_ym=''
select top 1 @ll_sale_flag=t_orno,@ls_have_ym=t_yymm   from tbl_priseresult where t_priz=3  and isnull(t_adjust,0)<>1 and t_orno=@al_orno and isnull(t_cttimes,0)=@ai_cttimes
if @ll_sale_flag  is null set @ll_sale_flag=0

select @sale_count_sjc=sum(isnull(t_ratio,0))   from tbl_priseresult  where t_priz=3  and isnull(t_adjust,0)<>1 and t_orno=@al_orno  and isnull(t_cttimes,0)=@ai_cttimes;
if @sale_count_sjc is null set  @sale_count_sjc=0
if @sale_count_sjc>=1
begin
 set @as_info=@as_info+'销售奖已于'+@ls_have_ym+'被提取!'
  insert into tbl_log(t_error,t_orno,t_yymm,t_priz,t_desc)
              values('一般提示',@al_orno,@as_ym,2,@as_info)
  select @as_info as info, 0 as rtn,0 priz
  return
end

return 1
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/75527/viewspace-719426/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/75527/viewspace-719426/