# docsify

**Repository Path**: iotcom/docsify

## Basic Information

- **Project Name**: docsify
- **Description**: 图片仓库
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-11-16
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 历史数据迁移2.4

- 本文档说明历史数据从2.2升级到2.3和2.4的历史数据操作说明
> 1.备份数据备份和还原
``` 数据库备份脚本
备份数据库：
mysqldump -p3306 -uroot -p --add-drop-table YHDB > /home/yahua/yhdb.sql
数据库密码：Yahua3585668yh
```
> 2.老人基本信息表迁移，
老人信息基本表pi_oldman_info废弃，使用新的老人信息基本表pi_oldman_base
```
先清理数据
delete from pi_oldman_base where oldmanId in (select oldmanId from pi_oldman_info)
再进行更新
insert into pi_oldman_base (  
  oldmanId,  
    orgId,  
    oldmanNum,  
    oldmanHeadUrl,  
    oldmanName,  
    oldmanSex,  
    oldmanIdcard,  
    oldmanStatus,  
    oldmanNation,  
    oldmanBirth,  
    oldmanAgent, 
	oldmanExtraInfo,
	oldmanHealthInfo, 
    isDelete,  
    createTime,  
    createUser,  
    updateTime,  
    updateUser,  
    remark  
 ) select   
 oldmanId,  
 orgId,  
 oldmanNum,  
 oldmanHeadUrl,  
 oldmanName,  
 oldmanSex,  
 oldmanIdcard,  
 oldmanStatus,  
 oldmanNation,  
 oldmanBirth,  
 oldmanAgent,
 '[]' as oldmanExtraInfo,
 '[]' as oldmanHealthInfo, 	 
 (case isDelete 
	   when 1 then 0
		 when 0 then 1
		 else isDelete
		 end
		 )
	  as isDelete,   
 createTime,  
 createUser,  
 updateTime,  
 updateUser,  
 remark  
   
 from pi_oldman_info 
```

> 3.更新长者居住信息表 pi_oldman_live_record
```
delete from pi_oldman_live_record where oldmanId in (select oldmanId from pi_oldman_info)

1.把所有的居住信息表导入居住表
insert into pi_oldman_live_record  select * from pi_live_record
2.把所有的试住开始数据导入居住表

insert into pi_oldman_live_record
select   
tryid as liveRecordId,  
orgid,  
oldmanId,  
startTime as operatorTime,  
41 as operatorStatus, 			
isDelete,  
createTime,  
createUser,  
updateTime,  
updateUser,  
remark from pi_try_record where startTime is true

3.把所有的试住结束数据导入到居住表

insert into pi_oldman_live_record
select   
md5(UUID())  as liveRecordId,  
orgid,  
oldmanId,  
endTime as operatorTime,  
42 as operatorStatus, 
			
isDelete,  
createTime,  
createUser,  
updateTime,  
updateUser,  
remark from pi_try_record where endTime is true

```
> 4.更新长者信息居住表 pi_oldman_live
```
delete from pi_oldman_live where oldmanId in (select oldmanId from pi_oldman_info)
1.把pi_oldman_info 中的 1：在住、2.待住、3.试住 信息插入到 pi_oldman_live 居住信息表
insert into pi_oldman_live(oldmanId,orgId,signContractTime,liveInTime,liveOutTime,quitCause,
billingStartTime,bedAmt,levelId,levelAmt,isDelete,createTime,createUser,updateTime,updateUser,remark)
select 
a.oldmanId,
a.orgId,
a.signContractTime,
a.liveInTime ,
a.newLiveOutTime as liveOutTime,
a.retreatCasuse as quitCause,
a.billingTimeStart as billingStartTime,

a.bedAmt ,
a.levelId ,
a.levelAmt ,
(case a.isDelete 
	   when 1 then 0
		 when 0 then 1
		 else a.isDelete
		 end
		 )
	  as isDelete,

a.createTime ,
a.createUser ,
a.updateTime ,
a.updateUser ,
a.remark 
from pi_oldman_info as a
where a. oldmanStatus=1 or a. oldmanStatus=2 or a. oldmanStatus=4 


2.更新试住信息到 pi_oldman_live

update pi_oldman_live as a inner join (select startTime,endTime,oldmanId from pi_try_record) as b  on a.oldmanId=b.oldmanId set a.tryStartTime=b.startTime,tryEndTime=b.endTime
 
3.更新 pi_oldman_checkin 位置房间数据到 pi_oldman_live 长者信息表中
update pi_oldman_live as a inner join (
select oldmanId,
( case when  houseId is not null and trim(houseId)!=''  then houseId 
       when  floorId is not null and trim(floorId)!=''  then  floorId
       when  unitId is not null and trim(unitId)!=''  then  unitId
       when  buildingId is not null and trim(buildingId)!=''  then  buildingId
       when  areaId is not null and trim(areaId)!=''  then  areaId
             else ''
    end

)  AS locationId,
roomId ,
bedId from pi_oldman_checkin
 ) as c on a.oldmanId=c.oldmanId set a.locationId=c.locationId,a.roomId=c.roomId,a.bedId=c.bedId
 
 3.如果护理级别已经删除，则设置为null
update pi_oldman_live set levelId=null where levelId in (select levelId from pi_nursing_level where isDelete=0);
```