修改日期 | 修改人 | 备注 |
2022-08-01 17:16:30[当前版本] | 董宏彬 | 其他原因... |
2022-03-07 10:09:09 | 董宏彬 | 创建版本 |
泰格前台移动支付的流水查询不到,但是现金流水可以查询到,说明了可能是因为泰格后台屏蔽移动支付的流水写入。
1.复制代码进去
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tri_pos_t_payflow' AND type = 'TR')
DROP TRIGGER tri_pos_t_payflow
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
GO
create trigger tri_pos_t_payflow on pos_t_payflow with ENCRYPTION for insert
as
begin
declare
@flowNo varchar(20),
@flowID decimal(5),
@max_flowID decimal(5),
@operID varchar(20),
@branchNO varchar(10),
@saleAmt decimal(14,4),
@payAmt decimal(14,4),
@payWay varchar(10),
@operDate datetime,
@date_begin datetime,
@date_end datetime,
@coinNO varchar(10),
@coinRate decimal(10,4),
@payDate datetime,
@saleWay char(1),
@vipNO varchar(20),
@cardNO varchar(20),
@remoteFlag char(1)
declare
@com_no decimal(14,0),
@ld_sheetamt decimal(16,4),
@ld_cardAmt decimal(16,4),
@ld_tmpIntegral decimal(16,4),
@ld_vipIntegral decimal(16,4),
@ld_residualAmt decimal(16,4),
@ls_residualAmt varchar(20),
@ls_temp varchar(20),
@ls_iccardUsing char(1),
@class_specintegral decimal(14,4),
@class_integral decimal(14,4),
@def_vipintegral decimal(14,4),
@def_vipspecintegral decimal(14,4),
@ld_specpx_std decimal(14,4),
@ls_specpx_mode char(1),
@ld_in_price decimal(14,4),
@ls_itemno varchar(20),
@ld_sourcepx decimal(14,4),
@ld_salepx decimal(14,4),
@ld_money decimal(14,4),
@ls_classno varchar(20),
@ld_barcodecount decimal(14,0),
@ld_sheetcount decimal(14,0),
@ls_flag2 char(1),
@ln_spec_price decimal(16,4),
@ln_needintegral decimal(16,4),
@ln_itemneedintegral decimal(16,4),
@ld_sale_qnty decimal(16,4),
@vip_integral_nozero char(1),
@gwq_checkno char(1),
@spec_flag varchar(1),
@jf_type char(1),
@brand_no char(6),
@item_counter_no varchar(10),
@tmp_pay_amt decimal(14,4),
@tmp_pay_ttl decimal(14,4),
@vip_integral_pw varchar(200),
@multiple_integral decimal(10,2),
@jf_datedatetime,
@vip_specpx_kou_jf char(1),
@ls_branch_flag char(1),
@ls_columns varchar(500),
@ls_values varchar(500),
@oper_str varchar(100),
@memo varchar(40),
@shift_date datetime,
@shift_no char(1),
@counter_no varchar(8),
@sale_man char(20),
@exchange_flag char(1),
@vippriceisspec varchar(20)
/*is_distributed含义:0=不是分布式、1=分布式总部、2=分布式分布*/
select @ls_branch_flag = system_value from sys_option where lower(system_id)='is_distributed'
declare @vip_integral_min_amt decimal(10, 2), @vip_integral_max_fen decimal(10, 2)
select @vip_integral_min_amt = convert(decimal(10, 2), isnull(min(system_value), 0))
from sys_option where system_id = 'vip_integral_min_amt'
select @vip_integral_max_fen = convert(decimal(10, 2), isnull(min(system_value), 0))
from sys_option where system_id = 'vip_integral_max_fen'
/*会员积分换购特价时是否扣积分, 默认要扣*/
select @vip_specpx_kou_jf = isnull(rtrim(ltrim(max(system_value))), '1')
from sys_option where lower(system_id) = 'vip_specpx_kou_jf'
/*按数量积分*/
declare @vip_jf_byqty char(1)
select @vip_jf_byqty = isnull(rtrim(ltrim(max(system_value))), '0')
from sys_option where lower(system_id) = 'vip_jf_byqty'
/*是否效验购物券编号*/
select @gwq_checkno = Isnull(Max(system_value),'0') from sys_option where rtrim(lower(system_id)) = 'gwq_checkno'
declare @err_rows integer
select @err_rows = count(1)
from inserted
where oper_date > '2019-10-26'
and pay_way in ( 'I3', 'I6') and sale_way='A'
and isnull(other1, '') <> '断网无法获取'
and ( len(other2) < 28
or not isnull(other3, '') like rtrim(branch_no) + flow_no + '%'
or (pay_way = 'I3' and not isnull(other1, '') like '%***%')
or (pay_way = 'I3' and not isnull(other2, '') like convert(char(8), oper_date, 112) + '%')
or (pay_way = 'I6' and len(rtrim(other1)) < 28 )
or (pay_way = 'I6' and not isnull(other2, '') like '4200%' and not isnull(other2, '') like 'face_4200%')
)
-- if @err_rows > 0
-- begin
-- rollback
-- raiserror('数据格式有错误,保存失败', 16, 1)
-- end
select @ls_columns = 'flow_id,flow_no,sale_amt,branch_no,pay_way,sale_way,card_no,vip_no,coin_no,coin_rate,pay_amt,oper_date,oper_id,counter_no,sale_man,memo,voucher_no,remote_flag,exchange_flag,shift_date,shift_no'
declare pay_cur cursor for
select com_no, flow_no,flow_id,oper_id, rtrim(branch_no),sale_amt,pay_amt,pay_way,coin_no,coin_rate,oper_date,sale_way,vip_no,card_no,remote_flag,memo,shift_date,shift_no,exchange_flag,counter_no,sale_man
from inserted
open pay_cur
fetch next from pay_cur into @com_no, @flowNo,@flowID,@operID,@branchNo,@saleAmt,@payAmt,@payWay,@coinNO,@coinRate,@operDate,@saleWay,@vipNO,@cardNO,@remoteFlag,@memo,@shift_date,@shift_no,@exchange_flag,@counter_no,@sale_man
while(@@FETCH_STATUS = 0)
begin
/*购物券付款且效验购物券编号时修改购物券的已使用标志*/
if left(@payWay,1) = 'N' and @gwq_checkno = '1'
begin
update pos_gwq_info set status = '2', use_date = getdate() where gwq_id = @cardNO
end
if(@remoteFlag is null) /*是本地生成的,而不是分店上传的才执行下面程序*/
begin
/*1.修改会员累计消费金额和积分, 只要第一行修改即可*/
if(@flowID = 1 and @vipNO is not null and @vipNo<>'')
begin
/*a.取默认正常销售会员积分*/
select @def_vipintegral = isnull(rtrim(ltrim(max(system_value))), 1)
from sys_option where lower(system_id) = 'vip_integral'
/*b.取默认特价销售会员积分*/
select @def_vipspecintegral = isnull(rtrim(ltrim(max(system_value))), @def_vipintegral)
from sys_option where lower(system_id) = 'vip_spec_integral'
/*c.取得特价率和特价积分模式*/
select @ld_specpx_std = convert(decimal(16,4),convert(decimal,isnull(ltrim(rtrim(max(system_value))), 5))/100)
from sys_option where lower(system_id) = 'specpxstd'
select @ls_specpx_mode = isnull(ltrim(rtrim(max(system_value))), '1')
from sys_option where lower(system_id) = 'specpx_mode'
/*第三种模式时检查设置会员价是否算特价,默认不算特价*/
select @vippriceisspec = system_value from sys_option where system_id = 'vippriceisspec'
if @vippriceisspec = '' or @vippriceisspec is null or @vippriceisspec = '0' or @vippriceisspec = 'N'
set @vippriceisspec = 'N'
else
set @vippriceisspec = 'Y'
/*取零头是否积分*/
select @vip_integral_nozero=isnull(rtrim(ltrim(max(system_value))),'0')
from sys_option
where lower(system_id)='vip_integral_nozero'
/*取会员使用的积分方案*/
select @jf_type = jf_type from pos_vip_info,pos_vip_type
where pos_vip_info.card_type = pos_vip_type.type_id
and pos_vip_info.card_id = @vipNO
declare sale_cur cursor for
select f.item_no, f.source_price, f.sale_price, f.sale_amt, rtrim(bi.class_no),rtrim(bi.brand_no), isnull(bi.counter_no, '9999'), isnull(bi.flag2, '0'),sale_qty, bi.in_price,spec_flag
from pos_t_saleflow as f,item as bi
where f.oper_date > convert(char(10), @operDate, 120)
and f.item_no = bi.item_no and f.flow_no = @flowNo and f.branch_no = @branchno
open sale_cur
fetch next from sale_cur into @ls_itemno, @ld_sourcepx, @ld_salepx, @ld_money, @ls_classno,@brand_no, @item_counter_no, @ls_flag2, @ld_sale_qnty, @ld_in_price, @spec_flag
while(@@fetch_status = 0)
begin
/*会员卡消费金额累计*/
set @ld_cardamt = isnull(@ld_cardamt,0) + isnull(@ld_money,0)
/*按数量积分, 把下面用金额计算的变量改为等于数量*/
If @vip_jf_byqty = '1'
set @ld_money = @ld_sale_qnty
if exists(select 1 from pos_vip_jf where vip_jf_no = @jf_type and (class_no = case jf_type when 'B' then @brand_no when 'Z' then @item_counter_no else @ls_classno end) )
begin /*指定了类别积分*/
select @class_specintegral = isnull(min(vip_spec_centrate),0), @class_integral = isnull(min(vip_centrate),0)
from pos_vip_jf
where vip_jf_no = @jf_type
and (class_no = case jf_type when 'B' then @brand_no when 'Z' then @item_counter_no else @ls_classno end)
end
else
begin /*没有指定类别积分,取默认积分*/
set @class_specintegral = isnull(@def_vipspecintegral,0)
set @class_integral = isnull(@def_vipintegral,0)
end
/*商品单独设置不参与积分, 把积分率改为零*/
if @ls_flag2 = '1'
begin
set @class_specintegral = 0
set @class_integral = 0
end
if ( @ld_sourcepx <> @ld_salepx and left(@ls_itemno, 2) <> 'DP')
begin /*特价销售积分*/
if @ls_specpx_mode = '1'
begin
if ( round(@ld_sourcepx - @ld_salepx, 2) >= (@ld_sourcepx * @ld_specpx_std))
set @ld_tmpintegral = (case when @class_specintegral = 0 then 0 else @ld_money/@class_specintegral end)
else
set @ld_tmpintegral = (case when @class_integral = 0 then 0 else @ld_money/@class_integral end)
end
if @ls_specpx_mode = '2'
begin
if (@ld_salepx < (@ld_in_price + @ld_in_price * @ld_specpx_std) and @ld_specpx_std > 0)
set @ld_tmpintegral = (case when @class_specintegral = 0 then 0 else @ld_money/@class_specintegral end)
else
set @ld_tmpintegral = (case when @class_integral = 0 then 0 else @ld_money/@class_integral end)
end
if @ls_specpx_mode = '3'
begin
/*4:零售物价;5:固定时间特价;6:超量购买特价;7:会员特价;8:促销调价;G:贵宾会员特价;T:阶梯价促销;V:会员价;X:促销赠送方案;Z:促销赠送方案;O手动打折;K*/
if @spec_flag = '4' or @spec_flag = '5' or @spec_flag = '6' or @spec_flag = '7' or @spec_flag = '8' or @spec_flag in('G', 'T', 'X', 'Z', 'O', 'K')
set @ld_tmpintegral = (case when @class_specintegral = 0 then 0 else @ld_money/@class_specintegral end)
else
begin
if @vippriceisspec = 'Y' and @spec_flag = 'V'
set @ld_tmpintegral = (case when @class_specintegral = 0 then 0 else @ld_money/@class_specintegral end)
else
set @ld_tmpintegral = (case when @class_integral = 0 then 0 else @ld_money/@class_integral end)
end
end
end
else
begin /*正常销售积分*/
if @ls_specpx_mode = '2' and @ld_salepx < (@ld_in_price + @ld_in_price * @ld_specpx_std)
set @ld_tmpintegral = (case when @class_specintegral = 0 then 0 else @ld_money/@class_specintegral end)
else
set @ld_tmpintegral = (case when @class_integral = 0 then 0 else @ld_money/@class_integral end)
end
set @ld_vipintegral=isnull(@ld_vipintegral,0) + isnull(@ld_tmpintegral,0)
/*购买所需积分 */
if @vip_specpx_kou_jf = '1'
begin
select @ln_spec_price = min(isnull(spe_price,0)),
@ln_itemneedintegral =max(isnull(spec_need_integral,0))
from pm_spec_price
where special_type='7' and
convert(char(8),getdate(),112) between convert(char(8),start_date,112) and convert(char(8),end_date,112) and
branch_no =@branchno and item_no = @ls_itemno
/*所需积分大于0、原价与售价不等且售价等与会员价时才冲减积分, 允许所需积分小于零即为增积分,同时去掉原价等于特价 and @ld_sourcepx <> @ld_salepx */
if @ln_itemneedintegral<>0 and (@ld_salepx = @ln_spec_price or @ld_salepx = 0 and @ln_spec_price = 0.01)
begin
select @ln_needintegral = isnull(@ln_needintegral,0) + ( @ln_itemneedintegral * @ld_sale_qnty)
insert pos_card_addintegral_log(card_id, addintegral, oper_id, oper_date, integral_later, add_type, saving_id, num1, num2, num3, remark1, remark2, remark3, branch_no)
select @vipNO, - @ln_itemneedintegral * @ld_sale_qnty, @operID, @operDate, 0, 'HG', '', 0, 0, 0, '前台积分换购','', @branchNo+'店'+@flowNo+'单号'+@ls_itemno+'商品, '+convert(varchar, @ld_salepx)+'元积分换购', @branchno
end
END /*购买所需积分*/
fetch next from sale_cur into @ls_itemno, @ld_sourcepx, @ld_salepx, @ld_money, @ls_classno,@brand_no, @item_counter_no, @ls_flag2,@ld_sale_qnty, @ld_in_price, @spec_flag
end
close sale_cur
deallocate sale_cur
/*按付款方式计算可积分数,IA表示线上支付的,必定积分*/
if (select isnull(max(system_value),'0') from sys_option where system_id = 'vip_integral_pw_flag') = '1'
begin
select @vip_integral_pw = isnull(max(system_value),'A,C,E,I2,I3,I5,I6,IT,ID,IF,IA')+',' from sys_option where system_id = 'vip_integral_pw'
set @vip_integral_pw = ',' + ltrim(rtrim(@vip_integral_pw)) + ','
select @tmp_pay_ttl = isnull(sum(pay_amt * case sale_way when 'D' then -1 else 1 end),0),
@tmp_pay_amt = isnull(sum(case when charindex(',' + ltrim(rtrim(case when left(other1, 2) = 'EO' then 'IA' else (case when left(pay_way,1) in ('A','C','N') then left(pay_way,1) else pay_way end) end))+',', @vip_integral_pw ) > 0 then pay_amt else 0 end * case sale_way when 'D' then -1 else 1 end),0)
from pos_t_payflow
where branch_no = @branchno
and flow_no = @flowno
if @tmp_pay_ttl > 0
set @ld_vipintegral = @ld_vipintegral * @tmp_pay_amt / @tmp_pay_ttl
else
set @ld_vipintegral = 0
end
/*销售为正, 赠送为0, 退货为负*/
set @ld_cardamt=@ld_cardamt*(case when @saleWay = 'A' then 1 when @saleway = 'C' then 0 else -1 end)
set @ld_vipintegral=@ld_vipintegral*(case when @saleWay = 'A' then 1 when @saleway = 'C' then 0 else -1 end)
/*零头不积分*/
if @vip_integral_nozero = '1'
begin
if @ld_vipintegral <= 0 and @ld_vipintegral >= -.5
begin
select @ld_vipintegral = 0
end
else
if @ld_vipintegral < 0
begin
set @ld_vipintegral = convert(numeric(16), @ld_vipintegral + .5)
end
else
begin
set @ld_vipintegral = convert(numeric(16), @ld_vipintegral - .5)
end
end
/*低于某金额不积分*/
if @saleAmt < @vip_integral_min_amt and @saleAmt > 0
begin
set @ld_vipintegral = 0
end
/*每单最多积XX分*/
if @ld_vipintegral > @vip_integral_max_fen and @vip_integral_max_fen > 0
begin
set @ld_vipintegral = @vip_integral_max_fen
end
/*退货时按原单销售日期计算积分*/
select @jf_date = min(case when isdate('20' + substring(voucher_no, 5, 6)) = 1 then '20' + substring(voucher_no, 5, 6) else oper_date end )
from pos_t_payflow
where com_no = @com_no and sale_way = 'B' and isnull(voucher_no,'') <> ''
if @jf_date is null set @jf_date = @operdate
/*设定积分初始倍数*/
set @multiple_integral = 1.0
/*按周指定星期几多倍*/
declare @ll_double decimal(10,2)
select @ll_double=1
select @ll_double = isnull(double_jf , 1.00)
from pos_vip_doublejf_date2
where witch_day = case when datepart(weekday, @jf_date)=1 then 7 else datepart(weekday, @jf_date) -1 end
and type='W' and double_jf<>1
if @ll_double > @multiple_integral set @multiple_integral = @ll_double
/*按月指定号数多倍*/
select @ll_double = isnull(double_jf , 1.00)
from pos_vip_doublejf_date2
where witch_day =datepart(day,@jf_date)
and type='M' and double_jf<>1
if @ll_double > @multiple_integral set @multiple_integral = @ll_double
/*按指定日期多倍*/
select @ll_double = isnull(double_jf , 1.00)
from pos_vip_doublejf_date
where convert(char(8),double_date,112)=convert(char(8),@jf_date,112)
and (substring(remark, 6, 1) = '-' and substring(convert(varchar, @jf_date, 120), 12, 5) between left(remark, 5) and right(remark, 5))
if @ll_double > @multiple_integral set @multiple_integral = @ll_double
/*======end double==============*/
/*会员生日N倍积分2009.03.31*/
declare @ll_birthday_jf decimal(10,2),
@vip_birthday char(4),
@today char(4),
@vip_dayormonth varchar(20),
@tomonth char(2),
@vip_brithmon char(2)
select @ll_birthday_jf = convert(decimal(10,2),isnull(rtrim(ltrim(max(system_value))), 1.00))
from sys_option where system_id = 'birthday_jf'
/*0默认当天*/
select @vip_dayormonth = isnull(rtrim(ltrim(max(system_value))), '0')
from sys_option where system_id = 'birthday_month'
select @vip_birthday =isnull(right(convert(char(8),vip_birthday,112),4),'0000'),
@today =right(convert(char(8),@jf_date,112),4),
@vip_brithmon = isnull(substring(convert(char(8),vip_birthday,112),5,2),'00'),
@tomonth =substring(convert(char(8),@jf_date,112),5,2)
from pos_vip_info where card_id = @vipNO
if (@ll_birthday_jf >1 )
begin
if ((@vip_dayormonth = '0' and @vip_birthday = @today ) OR (@vip_dayormonth = '1' and @vip_brithmon = @tomonth ) )
if @ll_birthday_jf > @multiple_integral set @multiple_integral = @ll_birthday_jf
end
/*会员生日N倍积分 end*/
/*消费金额N倍积分 2009.09.20*/
declare @salemulint_jfdecimal(10,2),
@salemulint_amtint,
@salemulint_begindatetime,
@salemulint_enddatetime
select @salemulint_amt = case when isnumeric(max(system_value)) = 1 then convert(int,max(system_value)) else 0 end
from sys_option where system_id = 'salemulint_amt'
select @salemulint_jf = case when isnumeric(max(system_value)) = 1 then convert(decimal(10,2),max(system_value)) else 0 end
from sys_option where system_id = 'salemulint_jf'
select @salemulint_begin = case when isdate(max(system_value)) = 1 then convert(datetime,max(system_value)) else '1900-01-01' end
from sys_option where system_id = 'salemulint_begin'
select @salemulint_end = case when isdate(max(system_value)) = 1 then convert(datetime,max(system_value)) else '1900-01-01' end
from sys_option where system_id = 'salemulint_end'
if @salemulint_amt > 0 and @salemulint_jf > 0 and @salemulint_begin < @jf_date and @salemulint_end > @jf_date
begin
if @saleAmt >= @salemulint_amt
begin
if @salemulint_jf > @multiple_integral set @multiple_integral = @salemulint_jf
end
end
/*消费金额N倍积分 end*/
/*会员日多倍积分 20191224*/
declare @ll_vipdate_double_jf decimal(10,2)
select @ll_vipdate_double_jf = max(mult_jf)
from pos_vip_day_plan
where (ctype = 'W' and ccheck = '1' and cvalue = day(getdate())) or
(ctype = 'W' and ccheck = '1' and cvalue =datepart(weekday,getdate())) or
(ctype = 'D' and yhdate = cast(datename(year,getdate())+'-'+datename(month,getdate())+'-'+datename(day,getdate()) as datetime))
if @ll_vipdate_double_jf > @multiple_integral set @multiple_integral = @ll_vipdate_double_jf
/*会员日多倍积分 end*/
/*积分乘上倍数*/
set @ld_vipintegral = @ld_vipintegral * @multiple_integral
/*计算促销赠送加积分*/
declare @add_jf decimal(14,4)
execute pr_pos_gift_jf @flowNo,@branchno,@vipNo,@add_jf output
set @ld_vipintegral = @ld_vipintegral + @add_jf
/*减掉购买所需积分*/
select @ld_vipintegral = @ld_vipintegral - (isnull(@ln_needintegral,0) * (case when @saleway = 'A' then 1 when @saleway = 'C' then 0 else -1 end))
/*更新会员卡日结表,在会员卡日结表的TRIGGER里计算会员积分*/
if exists(select 1 from pos_vip_daysum where card_no = @vipNo and branch_no = @branchNo
and card_type = '1' and oper_date = convert(char(10),@operDate,120))
update pos_vip_daysum set
sale_amt = isnull(sale_amt, 0) + isnull(@ld_cardAmt, 0),
sale_count = isnull(sale_count, 0) + case when @saleWay = 'A' then 1 else 0 end,
vip_integral = isnull(vip_integral, 0) + isnull(@ld_vipintegral, 0)
where card_no = @vipNo and branch_no = @branchNo and card_type = '1'
and oper_date = convert(char(10), @operDate, 120)
else
insert into pos_vip_daysum(oper_date, branch_no, card_no, card_type, sale_amt,
sale_count, vip_integral, other1, other2, other3)
values(convert(char(10), @operdate, 120), @branchNo, @vipNo, '1', isnull(@ld_cardAmt,0),1,
isnull(@ld_vipintegral,0),'','','')
/*把积分存储回pos_t_payflow表 20070616*/
if @ld_vipintegral <> 0
update pos_t_payflow set integral = @ld_vipintegral
where com_no = @com_no
end
/*2.修改储值卡累计消费金额*/
select @ls_iccardUsing = rtrim(system_value) from sys_option where system_id = 'iccard_using'
if(@cardNO is not Null and @cardNO<>'' and @payWay = 'E' )
begin
set @ld_sheetamt = @payAmt * isnull(@coinRate, 1)*
(case when @saleWay = 'A' then 1 when @saleway = 'C' then 0 else -1 end)
/*更新会员卡日结表,在会员卡日结表的TRIGGER里计算储值卡金额*/
if exists(select 1 from pos_vip_daysum where card_no = @cardNo and branch_no = left(@branchNo, 2)
and card_type = '2' and oper_date = convert(char(10),@operDate,120))
update pos_vip_daysum set
sale_amt = isnull(sale_amt, 0) + isnull(@ld_sheetamt, 0),
sale_count = isnull(sale_count, 0) + 1
where card_no = @cardNo and branch_no = left(@branchNo, 2) and card_type = '2'
and oper_date = convert(char(10), @operDate, 120)
else
insert into pos_vip_daysum(oper_date, branch_no, card_no, card_type, sale_amt,
sale_count, vip_integral, other1, other2, other3)
values(convert(char(10), @operdate, 120), left(@branchNo, 2), @cardNo, '2', isnull(@ld_sheetamt,0),1,0,'','','')
end
/*3.更新Pos_casher_daysum表*/
/*把现金找零设置为负值*/
if(@payWay = 'A' and @saleWay = 'D') select @saleWay = 'A', @payAmt = -isnull(@payAmt, 0)
/*计算该方式实际付款金额*/
if not exists(select 1 from pos_casher_daysum
where oper_date = convert(char(10), @operDate, 120)
and casher_no=@operID and branch_no = left(@branchNO, 2)
and pay_way=@payWay and sale_way=@saleWay and coin_no=@coinNO)
insert into pos_casher_daysum (oper_date, casher_no, branch_no, sale_amt, real_amt,
sale_way, pay_way, coin_no, oper_type, other1, other2, other3, sheet_count, barcode_count)
values(convert(char(10), @operdate, 120), @operID, left(@branchNO, 2), 0, isnull(@payAmt,0), @saleWay, @payWay, @coinNO, '0',
null,null,null, 0, 0)
else
update pos_casher_daysum
set real_amt = isnull(real_amt,0)+ isnull(@payAmt,0)
where oper_date = convert(char(10), @operDate, 120)
and casher_no = @operID and branch_no = left(@branchNO, 2)
and pay_way = @payWay and sale_way = @saleWay and coin_no = @coinNO
/*计算该收银员今天销售总额、客单数、条码数*/
if @flowid = 1
begin
declare @max_sale_amt decimaL(14, 4), @max_sheet_count decimal(10), @max_barcode_count decimal(10)
select @max_sale_amt = max(sale_amt), @max_sheet_count = max(sheet_count),
@max_barcode_count = max(barcode_count)
from pos_casher_daysum
where oper_date = convert(char(10), @operDate, 120)
and casher_no=@operID and branch_no = left(@branchNO, 2)
select @ld_barcodecount = count(*)
from pos_t_saleflow
where oper_date >= convert(char(10), @operdate, 120)
and branch_no = @branchNo
and flow_no = @flowNo
update pos_casher_daysum
set sale_amt = @max_sale_amt + (case when @saleWay = 'A' then @saleAmt when @saleway = 'C' then 0 else -@saleAmt end),
sheet_count = @max_sheet_count + 1,
barcode_count = @max_barcode_count + @ld_barcodecount
where oper_date = convert(char(10), @operDate, 120)
and casher_no = @operID and branch_no = left(@branchNO, 2)
end
end
fetch next from pay_cur into @com_no, @flowNo,@flowID,@operID,@branchNo,@saleAmt,@payAmt,@payWay,@coinNO,@coinRate,@operDate,@saleWay,@vipNO,@cardNO,@remoteFlag,@memo,@shift_date,@shift_no,@exchange_flag,@counter_no,@sale_man
end
close pay_cur
deallocate pay_cur
end
GO
( 2 )选择泰格的数据库
( 3 )执行代码
( 4 )看到命令执行成功代表完成
重启即可