铁山枰药品目录导入

--1.药品数据集
 select * from medicine_class_tree where hosp_id = '500105015'--药品类别表
 select * from medicine_base_info where hosp_id = '500105015'--药品品种表
 select * from medicine_spec where hosp_id = '500105015'--药品规格表
 select * from medicine_price where hosp_id = '500105015'--药品价格表

--2.删除测试数据
 delete from medicine_class_tree where hosp_id = '500105015' 
 delete from medicine_base_info where hosp_id = '500105015' 
 delete from medicine_spec where hosp_id = '500105015' 
 delete from medicine_price where hosp_id = '500105015' 

--(*)3.根据在医院收集的药品数据根据表结构分别导入四张药品表里

--4.生成药品类别(手工完成)
-- case1:查询出药品的不同类别，手工在程序里添加
 select distinct(father) from medicine_base_info where hosp_id = '500105015' 

-- case2:将品种表里的father由中文名改为代码
 declare @R_code varchar(50)
 declare @R_name varchar(50)              
 declare mycursor cursor for select child,name from medicine_class_tree where hosp_id = '500105015'  
 open mycursor                  
 fetch next from mycursor  into @R_code,@R_name   -
 while(@@fetch_status=0)    
 begin
   update medicine_base_info
   set medicine_base_info.father = @R_code
   where medicine_base_info.father = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name  
 end
 close mycursor   
 deallocate mycursor

--5.生成药品剂型单位(手工完成)
-- case1:查询出药品的不同剂型，手工在程序里添加
 select * from Medicine_DoseTypeUnit where  hosp_id = '500105015' 
 select distinct(dose_type) from medicine_base_info where hosp_id = '500105015' 

-- case2:将品种表里的dose_type由中文名改为代码
 declare @R_code varchar(50)
 declare @R_name varchar(50)                 
 declare mycursor cursor for select rguid,name from Medicine_DoseTypeUnit where hosp_id = '500105015' 
 open mycursor                  
 fetch next from mycursor  into @R_code,@R_name 
 while(@@fetch_status=0)   
 begin
   update medicine_base_info
   set medicine_base_info.dose_type = @R_code
   where medicine_base_info.dose_type = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name   
 end
 close mycursor      
 deallocate mycursor

--6.生成药品剂量单位(手工完成)
-- case1:查询出药品的不同剂量单位，手工在程序里添加
 select * from Medicine_DoseUnit where  hosp_id = '500105015' 
 select distinct(dose_unit) from medicine_spec where hosp_id = '500105015' 

-- case2:将规格表里的dose_unit由中文名改为代码
 declare @R_code varchar(50)
 declare @R_name varchar(50)            
 declare mycursor cursor for select rguid,name from Medicine_DoseUnit where hosp_id = '500105015'  
 open mycursor                
 fetch next from mycursor  into @R_code,@R_name  
 while(@@fetch_status=0)    
 begin
   update medicine_spec
   set medicine_spec.dose_unit = @R_code
   where medicine_spec.dose_unit = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name  
 end
 close mycursor      
 deallocate mycursor

--7.生成药品大零单位(手工完成)
-- case1:查询出药品的不同价格大零单位，手工在程序里添加
 select * from Medicine_BaseUnit where  hosp_id = '500105015' 
 select distinct(max_unit) from medicine_price where hosp_id = '500105015' 

-- case2:将价格表里的dose_unit由中文名改为代码
 declare @R_code varchar(50)
 declare @R_name varchar(50)            
 declare mycursor cursor for select rguid,name from Medicine_BaseUnit where hosp_id = '500105015'  
 open mycursor                 
 fetch next from mycursor  into @R_code,@R_name   -
 while(@@fetch_status=0)   
 begin
   update medicine_price
   set medicine_price.max_unit = @R_code
   where medicine_price.max_unit = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name   
 end
 close mycursor     
 deallocate mycursor

--8.生成药品小零单位(手工完成)
-- case1:查询出药品的不同价格大零单位，手工在程序里添加
 select * from Medicine_BaseUnit where  hosp_id = '500105015' 
 select distinct(min_unit) from medicine_price where hosp_id = '500105015' 

-- case2:将价格表里的min_unit由中文名改为代码
 declare @R_code varchar(50) 
 declare @R_name varchar(50)              
 declare mycursor cursor for select rguid,name from Medicine_BaseUnit where hosp_id = '500105015'  
 open mycursor                 
 fetch next from mycursor  into @R_code,@R_name
 while(@@fetch_status=0)    
 begin
   update medicine_price
   set medicine_price.min_unit = @R_code
   where medicine_price.min_unit = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name   
 end
 close mycursor    
 deallocate mycursor

--9.生成药品收入类别(手工完成)
-- case1:查询出药品的不同价格大零单位，手工在程序里添加
 select * from item_income where  hosp_id = '500105015' 
 select distinct(item_code) from medicine_price where hosp_id = '500105015' 

-- case2:将价格表里的item_code由中文名改为代码
 declare @R_code varchar(50)
 declare @R_name varchar(50)               
 declare mycursor cursor for select item_code,item_name from item_income where hosp_id = '500105015' 
 open mycursor                 
 fetch next from mycursor  into @R_code,@R_name 
 while(@@fetch_status=0)    
 begin
   update medicine_price
   set medicine_price.item_code = @R_code
   where medicine_price.item_code = @R_name and hosp_id = '500105015'
   fetch next from mycursor into @R_code,@R_name   
 end
 close mycursor       
 deallocate mycursor

 

--10.检查非空字段
 select * from medicine_base_info where father is null and hosp_id = '500105015'  --检查父代码是否为空
 select * from medicine_base_info where dose_type is null and hosp_id = '500105015'  --检查剂型是否为空

 select * from medicine_spec where dose is null and hosp_id = '500105015'--检查剂量代码是否为空
 select * from medicine_spec where (dose_unit is null or dose_unit = '') and hosp_id = '500105015'--检查剂量单位是否为空

 select * from medicine_price where retail_price is null and  hosp_id = '500105015'--检查药品价格是否为空
 select * from medicine_price where max_unit is null and hosp_id = '500105015'--检查大零单位是否为空
 select * from medicine_price where min_unit is null and  hosp_id = '500105015'--检查小零单位是否为空
 select * from medicine_price where item_code is null and  hosp_id = '500105015'--检查小零单位是否为空