-- if exists (select 1 from sysobjects where id = object_id('Get_StrArrayLength' ))
-- drop Function Get_StrArrayLength
-- go
-- create function Get_StrArrayLength
-- (
-- @str varchar(5000 ),
-- @split varchar(10 )
-- )
-- returns int
-- as
-- begin
-- declare @location int
-- declare @start int
-- declare @length int
--
-- SET @str = ltrim(rtrim (@str))
-- SET @location = charindex(@split , @str )
-- SET @length = 1
--
-- while @location <>0
-- begin
-- SET @start = @location + 1
-- SET @location = charindex(@split , @str , @start )
-- SET @length = @length + 1
-- end
--
-- return @length
-- end
-- GO
-- create function .(@SourceSql varchar(8000),@StrSeprate varchar(10),@indexof int)
-- returns nvarchar(50)
-- as
-- begin
-- declare @i as int
-- declare @temp_str as nvarchar(50)
-- set @SourceSql=rtrim(ltrim(@SourceSql))
--
-- while @indexof > 0
-- begin
-- set @indexof = @indexof - 1
-- set @i=charindex(@StrSeprate,@SourceSql)
-- if @i <> 0
-- begin
-- set @temp_str = (left(@SourceSql,@i-1))
-- set @SourceSql = right(@SourceSql,len(@SourceSql)-len(@temp_str)-len(@StrSeprate))
-- end
--
-- if @i = 0
-- begin
-- if @temp_str <> ''
-- begin
-- --取最后一个作为返回值
-- set @temp_str = @SourceSql
-- break
-- end
-- end
--
-- end
-- return @temp_str
-- end
ALTER proc log_spthd
@bh varchar(8000),@ck varchar(20)
as
begin
declare @len int,@len2 int,@Djbh varchar(20)
set @len=1;
--select dbo.Get_StrArrayLength('93143407002',',')
--select dbo.f_split('93143407002',',',0)
set @len2=dbo.Get_StrArrayLength(@bh,',');
select @djbh='JA6'+right('000000'+cast(right(max(djbh),6)+1 as varchar(10)),6) from xtrzb where djmc='SPTHD'
set nocount on
create table #Temp_spdm(spdm varchar(50));
if @len2=1
begin
insert into #Temp_spdm(spdm) values(@bh);
end
else begin
while (@len<=@len2)
begin
insert into #Temp_spdm(spdm) values(dbo.f_split(@bh,',',@len));
set @len=@len+1;
end
end
----------根据退仓编号获取SPKCB完整退仓明细信息-----------------------------------------------------------
select
t.spdm,f.商品名称,s.ckdm,s.gg1dm,s.gg2dm,f.供应商编号,f.供货商名称,sum(s.sl) 数量 into #Temp
from #Temp_spdm t
left join spkcb s on t.spdm=s.spdm
left join fr_vw_shangpin f on f.商品编号=t.spdm
where s.ckdm=@ck and s.sl>0
group by t.spdm,f.商品名称,s.gg1dm,s.gg2dm,f.供应商编号,f.供货商名称,s.ckdm
----------------------------------------------------------------------------------------------------------
create table #Gcour(row int,ghsdm varchar(50),djh varchar(10));
insert into #Gcour(row,ghsdm,djh) select row_number() over (order by ghsdm) as row,m.ghsdm,'' djh
from (select distinct 供应商编号 ghsdm from #temp) m;
----------------------------------------------------------------------------------------------------------
declare g1_cursor cursor for select row,ghsdm,djh from #Gcour where djh='';
open g1_cursor
declare @row int, @ghsdm varchar(20),@djh varchar(20)
fetch next from g1_cursor into @row,@ghsdm,@djh
while @@fetch_status=0
begin
begin
update #Gcour set djh=@djbh where current of g1_cursor
end
begin
insert into xtrzb(guid,ip,jq,dt,czdx,djmc,djbh,ydjh,cz,zymc,module,execmodule)
values('','','PC-20180527CWQX',getdate(),'1',left('SPTHD',50),left(@djbh,20),left('',120),'INSERT','系统管理员',0,'分销')
end
set @djbh='JA6'+right('000000'+cast(right(@djbh,6)+1 as varchar(10)),6)
fetch next from g1_cursor into @row,@ghsdm,@djh
end
close g1_cursor
deallocate g1_cursor
if object_id(N'BSERP3test..#Temp_cptc',N'U') is not null
drop table #Temp_cptc
select g.djh,t.spdm,t.商品名称 spmc,t.ckdm,t.gg1dm,t.gg2dm,t.供应商编号 ghsdm,t.供货商名称 ghsmc,f.采购进价 标准价,
sum(t.数量*f.采购进价) 标准金额,sum(t.数量) 数量 into #Temp_cptc
from #Temp t
left join #Gcour g on t.供应商编号=g.ghsdm
left join fr_vw_shangpin f on f.商品编号=t.spdm
group by t.spdm,t.商品名称,t.ckdm,t.gg1dm,t.gg2dm,t.供应商编号,t.供货商名称,g.djh,f.采购进价
-- select djh,ckdm,sum(数量) SL,sum(标准价) je,sum(标准金额) bzje
-- from #temp_cptc
-- group by djh,ckdm
----------SPTHD------------------------------------------------------------------------------------------------
declare g2_cursor cursor for select djh,ckdm,ghsdm,sum(数量) SL,sum(标准价) je,sum(标准金额) bzje from #temp_cptc
group by djh,ckdm,ghsdm
open g2_cursor
declare @dj varchar(20),@ckdm varchar(20),@dm varchar(50),@sl numeric(18,4),@je numeric(18,4),@bzje numeric(18,4),@rq datetime
fetch next from g2_cursor into @dj,@ckdm,@dm,@sl,@je,@bzje
while @@fetch_status=0
begin
set @rq=getdate()
exec sp_executesql
N'INSERT INTO "BSERP3test".."SPTHD"
("DJBH","RQ","QYDM","DM1","QDDM","DM2","DM2_1","BYZD1","BYZD12","YGDM","DM4","FPLX","BYZD18","BYZD7","DJXZ","LL","SL","JE",
"BZJE","BYZD2","BYZD10","ZDR","RQ_4","SP","SH","JZ","ZS","FP","LLR","TJ","XC","YS","JS","DM5")
VALUES
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,
@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34)',
N'@P1 varchar(9),@P2 datetime,@P3 varchar(3),@P4 varchar(7),@P5 varchar(3),@P6 varchar(3),@P7 varchar(3),@P8 varchar(1),
@P9 numeric(18,4),@P10 varchar(3),@P11 varchar(3),@P12 varchar(1),@P13 varchar(1),@P14 varchar(1),@P15 varchar(1),
@P16 varchar(1),@P17 numeric(18,4),@P18 numeric(18,4),@P19 numeric(18,4),@P20 varchar(1),@P21 numeric(18,4),@P22 varchar(10),
@P23 datetime,@P24 varchar(1),@P25 varchar(1),@P26 varchar(1),@P27 varchar(1),@P28 varchar(1),@P29 varchar(1)
,@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 varchar(1),@P34 varchar(1)',
@dj,@rq,'000',@dm,'000',@ckdm,'000','0',1.0000,'000','002','3','','','0','1',
@sl,@je,@bzje,'0',0,'系统管理员',@rq,'0','0','0','0','0','0','0','0','0','0',''
fetch next from g2_cursor into @dj,@ckdm,@dm,@sl,@je,@bzje
end
close g2_cursor
deallocate g2_cursor
----------SPTHDMX------------------------------------------------------------------------------------------------
declare g3_cursor cursor for select djh,spdm,gg1dm,gg2dm,标准价 bzj,标准金额 bzje,数量 sl from #temp_cptc
open g3_cursor
declare @dj2 varchar(20),@spbh varchar(20),@gg1dm varchar(20),@gg2dm varchar(20),@bzj numeric(18,4),@bze numeric(18,4),
@mxsl numeric(18,4)
fetch next from g3_cursor into @dj2,@spbh,@gg1dm,@gg2dm,@bzj,@bze,@mxsl
while @@fetch_status=0
begin
exec sp_executesql
N'SET NOCOUNT OFF;INSERT INTO "BSERP3test".."SPTHDMX" ("DJBH","MIBH","HH","SPDM","GG1DM","GG2DM","SL_3","SL","SL_1","SL_2"
,"CKJ","ZK","DJ","DJ_1","DJ_2","DJ_3","JE","BYZD1","BZ","PCDM")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20);
--SELECT SCOPE_IDENTITY() AS SCOPE_ID_COLUMN'
,N'@P1 varchar(9),@P2 int,@P3 numeric(18),@P4 varchar(11),@P5 varchar(2),
@P6 varchar(3),@P7 numeric(18,4),@P8 numeric(18,4),@P9 numeric(18,4),@P10 numeric(18,4),@P11 numeric(18,4),@P12 numeric(18,4),
@P13 numeric(18,4),@P14 numeric(18,4),@P15 numeric(18,4),@P16 numeric(18,4),@P17 numeric(18,4),@P18 varchar(1),@P19 varchar(8),
@P20 varchar(1)',
@dj2,1,1,@spbh,@gg1dm,@gg2dm,0,@mxsl,0,0,@bzj,1.0000,@bzj,@bzj,1.0000,@bzj,@bze,'0','',''
fetch next from g3_cursor into @dj2,@spbh,@gg1dm,@gg2dm,@bzj,@bze,@mxsl
end
close g3_cursor
deallocate g3_cursor
drop table #Temp
drop table #Temp_spdm
drop table #Gcour
drop table #Temp_cptc
select '执行成功'
end
go
--exec log_spthd '94242350002,94332136001','W02'
