exec sp_helptext prosampleoldstyle_usp
-- =============================================
-- =============================================
CREATE PROCEDURE [dbo].[prosampleoldstyle_usp]
@doccode varchar(20)
AS
BEGIN
declare @style varchar(50),
@docitem int,
@partid varchar(20),
@caidao varchar(50),
@Promatcode varchar(50),
@Promatname varchar(50),
@partname varchar(50),
@paradoccode varchar(20),
@special varchar(50),
@colortext varchar(50),
@uom varchar(20),
@rowid varchar(20),
@cltcode varchar(20),
@brand varchar(50)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF db_name()='SYERP'
BEGIN
--*****************************
update SYPhoto.dbo.ProSampleImage set style=b.Style,image=b.StyleImage,inserttime=getdate()
from SYPhoto.dbo.ProSampleImage a inner join prosamplehd b on a.formid=b.formid and a.doccode=b.doccode
where a.formid=8205 and a.doccode=@doccode and b.StyleImage is not null
if @@rowcount=0
begin
insert into SYPhoto.dbo.ProSampleImage(formid,doccode,style,image)
select formid,doccode,style,StyleImage from prosamplehd with(nolock)
where doccode=@doccode and StyleImage is not null
end
update prosamplehd set StyleImage=NULL where doccode=@doccode and StyleImage is not null
END
--*****************************
if not exists(select 1 from prosampleitem with(nolock) where doccode=@doccode)
begin
select @style=style from prosamplehd with(nolock) where doccode=@doccode
select top 1 @paradoccode=h.doccode from prosamplehd h with(nolock) inner join prosampleitem t on h.doccode=t.doccode
where docstatus>=100 and style=@style and h.doccode<>@doccode
order by h.docdate desc
--游标取出同一个旧型体的部位信息
if @paradoccode is not null
begin
declare mycur cursor for select docitem,Partid,Partname,Caidao,Promatcode,Promatname,Special,ColorText,UOM
from proSampleitem with(nolock) where doccode=@paradoccode
open mycur
fetch next from mycur into @docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom
while(@@fetch_status=0)
begin
exec dbo.getxxxx @rowid output
--把该型体的部位信息插入新的样品制造单表体
insert into prosampleitem(doccode,rowid,docitem,partid,partname,caidao,promatcode,promatname,special,colortext,uom)
values(@doccode,@rowid,@docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom)
fetch next from mycur into @docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom
end
close mycur
deallocate mycur
end
end
--**************************************
select @cltcode=cltcode,@brand=brand from prosamplehd with(nolock) where doccode=@doccode
if not exists(select 1 from imatpub_card with(nolock) where cltcode=@cltcode and cardname=@brand)
begin
insert into imatpub_card(cltcode,cardname)
values(@cltcode,@brand)
end
END exec sp_helptext prosampleoldstyle_usp
-- =============================================
-- Author: <方清海>
-- Create date: <2008-10-07>
-- Description: <如果是旧型体，则自动把部位信息写入样品制造单>
-- =============================================
CREATE PROCEDURE [dbo].[prosampleoldstyle_usp]
@doccode varchar(20)
AS
BEGIN
declare @style varchar(50),
@docitem int,
@partid varchar(20),
@caidao varchar(50),
@Promatcode varchar(50),
@Promatname varchar(50),
@partname varchar(50),
@paradoccode varchar(20),
@special varchar(50),
@colortext varchar(50),
@uom varchar(20),
@rowid varchar(20),
@cltcode varchar(20),
@brand varchar(50)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF db_name()='SYERP'
BEGIN
--*****************************
update SYPhoto.dbo.ProSampleImage set style=b.Style,image=b.StyleImage,inserttime=getdate()
from SYPhoto.dbo.ProSampleImage a inner join prosamplehd b on a.formid=b.formid and a.doccode=b.doccode
where a.formid=8205 and a.doccode=@doccode and b.StyleImage is not null
if @@rowcount=0
begin
insert into SYPhoto.dbo.ProSampleImage(formid,doccode,style,image)
select formid,doccode,style,StyleImage from prosamplehd with(nolock)
where doccode=@doccode and StyleImage is not null
end
update prosamplehd set StyleImage=NULL where doccode=@doccode and StyleImage is not null
END
--*****************************
if not exists(select 1 from prosampleitem with(nolock) where doccode=@doccode)
begin
select @style=style from prosamplehd with(nolock) where doccode=@doccode
select top 1 @paradoccode=h.doccode from prosamplehd h with(nolock) inner join prosampleitem t on h.doccode=t.doccode
where docstatus>=100 and style=@style and h.doccode<>@doccode
order by h.docdate desc
--游标取出同一个旧型体的部位信息
if @paradoccode is not null
begin
declare mycur cursor for select docitem,Partid,Partname,Caidao,Promatcode,Promatname,Special,ColorText,UOM
from proSampleitem with(nolock) where doccode=@paradoccode
open mycur
fetch next from mycur into @docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom
while(@@fetch_status=0)
begin
exec dbo.getxxxx @rowid output
--把该型体的部位信息插入新的样品制造单表体
insert into prosampleitem(doccode,rowid,docitem,partid,partname,caidao,promatcode,promatname,special,colortext,uom)
values(@doccode,@rowid,@docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom)
fetch next from mycur into @docitem,@partid,@partname,@caidao,@promatcode,@promatname,@special,@colortext,@uom
end
close mycur
deallocate mycur
end
end
--**************************************
select @cltcode=cltcode,@brand=brand from prosamplehd with(nolock) where doccode=@doccode
if not exists(select 1 from imatpub_card with(nolock) where cltcode=@cltcode and cardname=@brand)
begin
insert into imatpub_card(cltcode,cardname)
values(@cltcode,@brand)
end
END
uf_GetSpellCode -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER wzh_cfqdemo1
ON WZH_CKZL AFTER insert
YF
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @msg VARCHAR()
DECLARE @cltname VARCHAR()
SELECT @cltname=cltname from WZH_CKZL select @msg = dbo.uf_GetSpellCode(cltname) from WZH_CKZL
update WZH_CKZL set fastcode=@msg where cltname =@cltname END