太多了
if exists (select * from dbo.sysobjects where id = object_id('jltmp_单位基本情况表'))
drop table dbo.jltmp_单位基本情况表
if exists (select * from dbo.sysobjects where id = object_id('jltmp_单位基本情况表2'))
drop table dbo.jltmp_单位基本情况表2
if exists (select * from dbo.sysobjects where id = object_id('jltmp_系统单位类别字典表'))
drop table dbo.jltmp_系统单位类别字典表
if exists (select * from dbo.sysobjects where id = object_id('jltmp_系统经济性质字典表'))
drop table dbo.jltmp_系统经济性质字典表
if exists (select * from dbo.sysobjects where id = object_id('jltmp_系统片区字典表'))
drop table dbo.jltmp_系统片区字典表
if exists (select * from dbo.sysobjects where id = object_id('jltmp_IncreNumber'))
drop function dbo.jltmp_IncreNumber
if exists (select * from dbo.sysobjects where id = object_id('jltmp_整理数据'))
drop procedure dbo.jltmp_整理数据
if exists (select * from dbo.sysobjects where id = object_id('jltmp_整理数据2'))
drop procedure dbo.jltmp_整理数据2
if exists (select * from dbo.sysobjects where id = object_id('jltmp_添加数据'))
drop procedure dbo.jltmp_添加数据
if exists (select * from dbo.sysobjects where id = object_id('dbo.jltmp_许可证编号转换'))
drop function dbo.jltmp_许可证编号转换
GO
create function dbo.jltmp_许可证编号转换
(
@psNumber varchar(100),
@len int
)
RETURNS varchar(100) AS
BEGIN
declare @sNewNumber varchar(100),
@iPos int
if @psNumber is null select @sNewNumber=null --返回null
else
begin
select @psNumber=ltrim(rtrim(@psNumber))
select @iPos=len(@psNumber)
--小于等于10为的许可证编码不变
if @iPos=0 select @sNewNumber=null --空格返回null
else begin
if @iPos<=10 select @sNewNumber=@psNumber
else begin
select @sNewNumber=substring(@psNumber,(@len+1),(@iPos-@len))
if len(ltrim(rtrim(@sNewNumber)))<>@iPos-@len select @sNewNumber=null
end
end
end
return @sNewNumber
END
GO
create table dbo.jltmp_单位基本情况表
(
申请编号 varchar(40) null,
单位名称 varchar(100) null,
单位类别 varchar(60) null,
经济性质 varchar(60) null,
地址 varchar(100) null,
电话 varchar(60) null,
法人或法人代表 varchar(60) null,
-- 负责人 varchar(60) null,
经营范围 varchar(80) null,
主管部门 varchar(60) null,
职工人数 int null,
从业人员数 int null,
建档日期 datetime null,
卫生许可证号 varchar(60) null,
卫生卡号 varchar(60) null,
片区 varchar(60) null,
操作日期 datetime null,
状态 varchar(60) null,
年度评价 varchar(255) null,
应体检日期 datetime null,
下次定期监测日期 datetime null,
档案编号 varchar(60) null,
卫生种类 varchar(60) null,
助记符 varchar(60) null,
有效开始日期 datetime null,
有效截止日期 datetime null,
最后复验日期 datetime null
)
create table dbo.jltmp_单位基本情况表2
(
申请编号 UDT_申请编号 not null,
档案编号 UDT_档案编号 not null,
单位名称 varchar(80) null,
法人代表 varchar(20) null,
经济性质 varchar(20) null,
片区 varchar(20) null,
地址 varchar(80) null,
街道 varchar(20) null,
电话 varchar(20) null,
职工人数 int null,
从业人数 int null,
卫生种类 varchar(10) null,
行业类别 varchar(40) null,
经营内容 varchar(80) null,
主管单位 varchar(60) null,
应体检人数 int null,
已体检人数 int null,
卫生许可证号 char(10) null,
助记符 varchar(40) null,
建档日期 datetime not null,
有效开始日期 datetime null,
有效截止日期 datetime null,
最后复验日期 datetime null
)
create table dbo.jltmp_系统单位类别字典表
(
编号 varchar(20) null,
内容 varchar(60) null,
卫生种类 varchar(20) null
)
create table dbo.jltmp_系统经济性质字典表
(
编号 varchar(20) null,
内容 varchar(60) null
)
create table dbo.jltmp_系统片区字典表
(
编号 varchar(20) null,
内容 varchar(60) null
)
GO
create function dbo.jltmp_IncreNumber
(
@psNumber varchar(100)
)
RETURNS varchar(100) AS
BEGIN
declare @sNewNumber varchar(100),@iPos int
if @psNumber is null select @sNewNumber='01'
else begin
select @iPos=len(@psNumber)
while @iPos>0 and substring(@psNumber,@iPos,1)>='0' and substring(@psNumber,@iPos,1)<='9'
select @iPos=@iPos - 1
if @iPos>0 begin
select @sNewNumber=substring(@psNumber,1,@iPos)
select @psNumber=substring(@psNumber,@iPos+1,len(@psNumber) - @iPos) end
else select @sNewNumber=''
select @iPos=len(@psNumber)
select @psNumber=rtrim(ltrim(str(convert(int, @psNumber)+1)))
-- @iPos may < len(@psNumber), for example, @psNumber = '9' + 1 -> '10'
--because replace(' ',space(<=0), '0')=' ', so
if @iPos<=len(@psNumber) select @sNewNumber=@sNewNumber+@psNumber
else select @sNewNumber=@sNewNumber+replace(space(@iPos -len(@psNumber)),' ', '0')+@psNumber
end
return @sNewNumber
END
GO
create procedure dbo.jltmp_整理数据2 as
BEGIN
set nocount on
--添加字典数据
delete jltmp_系统单位类别字典表
insert into jltmp_系统单位类别字典表 select distinct null,单位类别,卫生种类 from jltmp_单位基本情况表 where 单位类别 is not null
delete jltmp_系统片区字典表
insert into jltmp_系统片区字典表 select distinct null,片区 from jltmp_单位基本情况表 where 片区 is not null
delete jltmp_系统经济性质字典表
insert into jltmp_系统经济性质字典表 select distinct null,经济性质 from jltmp_单位基本情况表 where 经济性质 is not null
--经济性质升级与防疫26一致
update jltmp_单位基本情况表 set 经济性质 = case
when 经济性质 in ('国有','集体','私营') then 经济性质 + '企业'
when 经济性质 = '股份制' then '股份企业'
else 经济性质
end
update jltmp_系统经济性质字典表 set 内容 = case
when 内容 in ('国有','集体','私营') then 内容 + '企业'
when 内容 = '股份制' then '股份企业'
else 内容
end
--升级单位类别
update jltmp_单位基本情况表
set 单位类别 = case
when 单位类别 ='糕点 蜜饯' then '糕点,蜜饯'
else replace(单位类别,' ','')
end
update jltmp_系统单位类别字典表
set 内容 = case
when 内容 = '糕点 蜜饯' then '糕点,蜜饯'
else replace(内容,' ','')
end
--select distinct 单位类别 from jltmp_单位基本情况表
END
GO
create procedure dbo.jltmp_整理数据 as
BEGIN
set nocount on
--删除重复单位
delete jltmp_单位基本情况表 from jltmp_单位基本情况表 a,单位档案_单位基本信息表 b
where a.单位名称=b.单位名称 and a.片区=b.片区 and a.卫生种类=b.卫生种类 and a.经济性质=b.经济性质 and a.单位类别=b.行业类别
--删除已存在的单位类别
delete jltmp_系统单位类别字典表 from jltmp_系统单位类别字典表 a,系统管理_字典_字典内容表 b
where a.内容=b.名称 and b.ID=dbo.系统管理_获取字典类别ID('行业属性字典') and
b.Parent = (select InnerID from 系统管理_字典_字典内容表
where ID=dbo.系统管理_获取字典类别ID('行业属性字典') and
名称=a.卫生种类 and Parent=0)
--删除已存在的片区
delete jltmp_系统片区字典表 from jltmp_系统片区字典表 a,系统管理_字典_字典内容表 b
where a.内容=b.名称 and b.ID=dbo.系统管理_获取字典类别ID('片区街道字典') and Parent=0
--删除已存在的经济性质
delete jltmp_系统经济性质字典表 from jltmp_系统经济性质字典表 a,系统管理_字典_字典内容表 b
where a.内容=b.名称 and b.ID=dbo.系统管理_获取字典类别ID('经济性质字典') and Parent=0
--处理片区为null的单位
if exists (select * from jltmp_单位基本情况表 where 片区 is null)
begin
update jltmp_单位基本情况表
set 片区 ='<未定>'
where 片区 is null
insert into jltmp_系统片区字典表 values(null,'<未定>')
end
--处理经济性质为null的单位
if exists (select * from jltmp_单位基本情况表 where 经济性质 is null)
begin
update jltmp_单位基本情况表
set 经济性质 ='<未定>'
where 经济性质 is null
insert into jltmp_系统经济性质字典表 values(null,'<未定>')
end
--处理建档日期为null的单位,更改为当前日期
if exists (select * from jltmp_单位基本情况表 where 建档日期 is null)
begin
update jltmp_单位基本情况表
set 建档日期=getdate()
where 建档日期 is null
end
END
GO
create procedure dbo.jltmp_添加数据 as
BEGIN
set nocount on
declare
@申请编号 varchar(40),@单位名称 varchar(100),@单位类别 varchar(60),@经济性质 varchar(60),
@地址 varchar(100),@电话 varchar(60),@法人或法人代表 varchar(60),@负责人 varchar(60),
@经营范围 varchar(60),@主管部门 varchar(60),@职工人数 int,@从业人员数 int,
@建档日期 datetime,@卫生许可证号 varchar(60),@片区 varchar(60),@档案编号 varchar(60),
@卫生种类 varchar(60),@助记符 varchar(60),@编号 varchar(60),@内容 varchar(60),
@ID varchar(20),@InnerID int,
@有效开始日期 datetime,
@有效截止日期 datetime,
@最后复验日期 datetime
--逐个添加经济性质
declare cur经济性质 cursor for select distinct 内容 from jltmp_系统经济性质字典表
open cur经济性质
fetch cur经济性质 into @内容
select @ID=dbo.系统管理_获取字典类别ID('经济性质字典')
select @编号=isnull(max(编号),'00') from 系统管理_字典_字典内容表 where ID=@ID
while @@fetch_status=0 begin
select @编号=dbo.jltmp_IncreNumber(@编号)
insert into 系统管理_字典_字典内容表 values(@ID,@编号,@内容,'','<防疫25升级到26单位档案导入>',0)
fetch cur经济性质 into @内容
end
deallocate cur经济性质
--逐个添加片区
declare cur片区 cursor for select distinct 内容 from jltmp_系统片区字典表
open cur片区
fetch cur片区 into @内容
select @ID=dbo.系统管理_获取字典类别ID('片区街道字典')
select @编号=isnull(max(编号),'00') from 系统管理_字典_字典内容表 where ID=@ID
while @@fetch_status=0 begin
select @编号=dbo.jltmp_IncreNumber(@编号)
insert into 系统管理_字典_字典内容表 values(@ID,@编号,@内容,'','<防疫25升级到26单位档案导入>',0)
select @InnerID=max(InnerID) from 系统管理_字典_字典内容表
insert into 系统管理_字典_字典内容表 values(@ID,@编号+'01','<未定>','','<防疫25升级到26单位档案导入>',@InnerID)
fetch cur片区 into @内容
end
deallocate cur片区
--逐个添加单位类别
declare cur单位类别 cursor for select distinct 内容,卫生种类 from jltmp_系统单位类别字典表
open cur单位类别
fetch cur单位类别 into @内容,@卫生种类
select @ID=dbo.系统管理_获取字典类别ID('行业属性字典')
while @@fetch_status=0 begin
select @InnerID=InnerID from 系统管理_字典_字典内容表
where ID=@ID and 名称=@卫生种类 and Parent=0
select @编号=dbo.jltmp_IncreNumber(isnull(max(b.编号),max(a.编号)+'00'))
from 系统管理_字典_字典内容表 a left join 系统管理_字典_字典内容表 b on b.Parent = a.InnerID
where a.ID=@ID and a.名称=@卫生种类 and a.Parent=0
insert into 系统管理_字典_字典内容表 values(@ID,@编号,@内容,'','<防疫25升级到26单位档案导入>',@InnerID)
select @InnerID=max(InnerID) from 系统管理_字典_字典内容表
insert into 系统管理_字典_字典内容表 values(@ID,@编号+'0','单位类别','','<防疫25升级到26单位档案导入>',@InnerID)
insert into 系统管理_字典_字典内容表 values(@ID,@编号+'1','经营形式','','<防疫25升级到26单位档案导入>',@InnerID)
insert into 系统管理_字典_字典内容表 values(@ID,@编号+'2','许可项目','','<防疫25升级到26单位档案导入>',@InnerID)
fetch cur单位类别 into @内容,@卫生种类
end
deallocate cur单位类别
--逐个添加单位信息
declare cur单位基本情况表 cursor for
select distinct 单位名称,单位类别,经济性质,地址,电话,法人或法人代表,经营范围,主管部门,建档日期,
职工人数,从业人员数,卫生许可证号,片区,卫生种类,助记符,有效开始日期,有效截止日期,最后复验日期
from jltmp_单位基本情况表
open cur单位基本情况表
fetch cur单位基本情况表 into @单位名称,@单位类别,@经济性质,@地址,@电话,@法人或法人代表,@经营范围,@主管部门,
@建档日期,@职工人数,@从业人员数,@卫生许可证号,@片区,@卫生种类,@助记符,@有效开始日期,@有效截止日期,@最后复验日期
while @@fetch_status=0 begin
exec 系统管理_返回编号流水号 '单位档案管理','申请编号',@申请编号 output,Null
select @编号=编号 from 系统管理_字典_字典内容表 a,系统管理_字典_字典表列表 b
where a.ID=b.ID and b.名称='行业属性字典' and a.名称=@卫生种类
--以下暂时用 @编号 存储卫生种类的ID
--参考存储过程 单位档案_生成档案编号
declare @代号 char(5)
declare @编号类型 varchar(10)
if exists(select * from dbo.单位档案_档案编号使用情况表 where substring(档案编号,1,1) = @编号)
begin
select @档案编号 =min(CONVERT(int,档案编号)) from dbo.单位档案_档案编号使用情况表
with (holdlock)
where substring(档案编号,1,1) = @编号
delete from dbo.单位档案_档案编号使用情况表 where 档案编号 = @档案编号
end
else
begin
set @编号类型='档案编号' + @编号
exec 系统管理_返回编号流水号 '单位档案管理',@编号类型,@代号 output,null
set @档案编号 = @编号 + @代号
end
insert into jltmp_单位基本情况表2(单位名称,行业类别,经济性质,地址,电话,法人代表,经营内容,主管单位,
建档日期,职工人数,从业人数,卫生许可证号,片区,卫生种类,街道,申请编号,档案编号,助记符,应体检人数,已体检人数,有效开始日期,有效截止日期,最后复验日期)
values(@单位名称,@单位类别,@经济性质,@地址,@电话,@法人或法人代表,@经营范围,@主管部门,
@建档日期,@职工人数,@从业人员数,@卫生许可证号,@片区,@卫生种类,'<未定>',@申请编号,@档案编号,@助记符,0,0,@有效开始日期,@有效截止日期,@最后复验日期)
fetch cur单位基本情况表 into @单位名称,@单位类别,@经济性质,@地址,@电话,@法人或法人代表,@经营范围,@主管部门,
@建档日期,@职工人数,@从业人员数,@卫生许可证号,@片区,@卫生种类,@助记符,@有效开始日期,@有效截止日期,@最后复验日期
end
deallocate cur单位基本情况表
insert into 单位档案_单位基本信息表
(单位名称,行业类别,经济性质,地址,电话,法人代表,经营内容,主管单位,
建档日期,职工人数,从业人数,卫生许可证号,片区,卫生种类,街道,申请编号,档案编号,助记符,应体检人数,已体检人数)
select 单位名称,行业类别,经济性质,地址,电话,法人代表,经营内容,主管单位,
建档日期,职工人数,从业人数,卫生许可证号,片区,卫生种类,街道,申请编号,档案编号,助记符,应体检人数,已体检人数
from jltmp_单位基本情况表2
END
--delete dbo.单位档案_单位基本信息表
--delete dbo.许可证_使用信息表
--select a.申请编号,a.单位名称,a.卫生许可证号,a.卫生种类,a.建档日期,a.有效截止日期,a.有效开始日期,a.最后复验日期 from jltmp_单位基本情况表2 a where isnull(a.卫生许可证号,'')<>'' and a.申请编号 not in (select b.申请编号 from 许可证_使用信息表 b)
--select * from dbo.单位档案_单位基本信息表
--select * from dbo.jltmp_单位基本情况表2