/* 等服务器用4.1代码跑起来之后再执行此升级脚本 */

/* 一次性物品相关 */
/* 需要建好对应的仓库，然后将‘北院供应室’替换为一级供应室名称，'北院供应室仓库'替换为建好的一级供应室仓库名称 */
delete from DisposableGoodsIdentification;
delete from DisposableGoodsBatchStock;
delete from DisposableGoodsStock;
delete from DisposableGoodsBatch;
delete from DisposableGoods;

/* 生成一次性物品定义*/
set identity_insert DisposableGoods on
insert into DisposableGoods (id,amount,certification,externalCode,guid,inventorySerialNumber,isApplicationMaterial,isPartOfTousseMaterial,isSupplyRoomGoods,maxStorage,minApplyAmount,minStorage,name,packageSpec,referencePrice,specification,spelling,type,unit,unitConvertCoefficient,wbCode) (select id,amount,certification,externalCode,guid,inventorySerialNumber,isApplicationMaterial,isPartOfTousseMaterial,isSupplyRoomGoods,maxStorage,minApplyAmount,minStorage,name,packageSpec,referencePrice,specification,spelling,type,unit,unitConvertCoefficient,wbCode from DiposableGoods)
set identity_insert DisposableGoods off

/* 生成一次性物品库存*/
set identity_insert DisposableGoodsStock on
insert into DisposableGoodsStock (id,amount,certification,externalCode,guid,inventorySerialNumber,isApplicationMaterial,isPartOfTousseMaterial,isSupplyRoomGoods,maxStorage,minApplyAmount,minStorage,name,packageSpec,referencePrice,specification,spelling,type,unit,unitConvertCoefficient,wbCode) (select id,amount,certification,externalCode,guid,inventorySerialNumber,isApplicationMaterial,isPartOfTousseMaterial,isSupplyRoomGoods,maxStorage,minApplyAmount,minStorage,name,packageSpec,referencePrice,specification,spelling,type,unit,unitConvertCoefficient,wbCode from DiposableGoods)
set identity_insert DisposableGoodsStock off
update DisposableGoodsStock set disposableGoodsID=id;
update DisposableGoodsStock set warehouseID=(select id from WareHouse where name='北院供应室仓库');
update DisposableGoodsStock set warehouseName='北院供应室仓库';
/* 生成一次性物品批次定义*/
insert into DisposableGoodsBatch (id,batchNumber,cost,expDate,manufacturer,sterileBatchNumber,storage,supplierName,diposablegoods_id,producingArea) (select d.id,d.batchNumber,d.cost,d.expDate,d.manufacturer,d.sterileBatchNumber,d.storage,d.supplierName,d.diposablegoods_id,d.producingArea from DiposableGoodBatchStock d inner join BarcodeDevice b on d.id=b.id)

/* DiposableGoodBatchStock 改名为 DisposableGoodsBatchStock ，生成一次性物品批次库存*/
delete from DisposableGoodsBatchStock;
set identity_insert DisposableGoodsBatchStock on
insert into DisposableGoodsBatchStock (id,barcode,batchNumber,cost,expDate,manufacturer,sterileBatchNumber,storage,supplierName,diposablegoods_id,producingArea) (select d.id,b.barcode,d.batchNumber,d.cost,d.expDate,d.manufacturer,d.sterileBatchNumber,d.storage,d.supplierName,d.diposablegoods_id,d.producingArea from DiposableGoodBatchStock d inner join BarcodeDevice b on d.id=b.id)
set identity_insert DisposableGoodsBatchStock off
update DisposableGoodsBatchStock set disposableGoodsID=diposablegoods_id;
update DisposableGoodsBatchStock set disposableGoodsBatchID=id;
update DisposableGoodsBatchStock set warehouseID=(select id from WareHouse where name='北院供应室仓库');
update DisposableGoodsBatchStock set warehouseName='北院供应室仓库';

/* IdentificationOfDiposableGoods 改名为 DisposableGoodsIdentification */
delete from DisposableGoodsIdentification;
set identity_insert DisposableGoodsIdentification on
insert into DisposableGoodsIdentification (id,amount,conclusion,entryDate,identification,price,batch_id) (select id,amount,conclusion,entryDate,identification,price,batch_id from IdentificationOfDiposableGoods)
set identity_insert DisposableGoodsIdentification off
update DisposableGoodsIdentification set disposableGoodsID=(select TOP 1 diposablegoods_id from DiposableGoodBatchStock where id=batch_id);
update DisposableGoodsIdentification set disposableGoodsStockID=batch_id;
update DisposableGoodsIdentification set disposableGoodsBatchID=batch_id;
update DisposableGoodsIdentification set warehouseID=(select id from WareHouse where name='北院供应室仓库');
update DisposableGoodsIdentification set warehouseName='北院供应室仓库';

/* 设置入库单的仓库信息 */
update GodownEntry set wareHouseId=(select id from WareHouse where name='北院供应室仓库');
update GodownEntry set wareHouseName='北院供应室仓库';
/* 设置入库单明细的一次性物品信息 */
update GodownEntryItem set disposableGoodsID=diposableGoodsID,disposableGoodsStockID=diposableGoodsID,disposableGoodsBatchID=batchID,disposableGoodsBatchStockID=batchID;
/* 设置入库单明细的仓库信息 */
update GodownEntryItem set wareHouseId=(select id from WareHouse where name='北院供应室仓库');
update GodownEntryItem set wareHouseName='北院供应室仓库';
/* 设置入库单一次性物品明细的一次性物品信息 */
update GodownEntryDiposableGoodsItem set disposableGoodsID=diposableGoodsID,disposableGoodsStockID=diposableGoodsID,disposableGoodsBatchID=batchID,disposableGoodsBatchStockID=batchID;


/* 设置盘点记录明细的一次性物品信息 */


/* 设置发货单一次性物品明细的一次性物品信息 */
update DiposableGoodsItem set disposableGoodsId=diposableGoodsID,disposableGoodsStockId=diposableGoodsID,disposableGoodsBatchId=batchID,disposableGoodsBatchStockId=batchID;


/* 设置退货记录明细的一次性物品信息 */
update ReturnGoodsItem set disposableGoodsId=diposableGoodsID,disposableGoodsStockId=diposableGoodsID,disposableGoodsBatchId=batchID,disposableGoodsBatchStockId=batchID;

/* 禁用外来器械，自定义器械包的标识牌 */
update TousseDefinition set isDisableIDCard='是' where tousseType in('外来器械包','自定义器械包')

/* 结算与盘点数据处理 */
update StockIdentificationOfGoods set price=0 where price is null
update StockIdentificationOfGoods set amount=0 where amount is null

--2.器械包实例所属仓库字段更新
update TousseInstance set wareHouseName='北院供应室仓库', wareHouseId = (select id from WareHouse where name='北院供应室仓库') where status in ('已灭菌','已消毒');
update TousseInstance set wareHouseName=(select w.name from WareHouse w join Invoice i on w.id=i.warehouseID where i.id=TousseInstance.invoice_id), wareHouseId = (select w.id from WareHouse w join Invoice i on w.id=i.warehouseID where i.id=TousseInstance.invoice_id) where status='已发货';
--3.申请单的处理科室
update invoicePlan set handleDepart='北院供应室', handleDepartCoding=(select orgUnitCoding from OrgUnit where name='北院供应室');
--4.退货单及明细仓库字段更新
update ReturnGoodsRecord set warehouseName='北院供应室仓库',warehouseID=(select id from WareHouse where name='北院供应室仓库');
update ReturnGoodsItem set warehouseName='北院供应室仓库',warehouseID=(select id from WareHouse where name='北院供应室仓库');
--5.材料与一次性物品盘点及明细仓库字段


--6.发货单及明细仓库字段更新
update invoice set sourceWareHouseName='北院供应室仓库',sourceWarehouseID=(select id from WareHouse where name='北院供应室仓库');
--wareHouseName=(select w.name from WareHouse w join OrgUnit o on w.orgUnitCode=o.orgUnitCoding where o.name=invoice.depart), wareHouseId = (select w.id from WareHouse w join OrgUnit o on w.orgUnitCode=o.orgUnitCoding where o.name=invoice.depart);
update InvoiceItem set warehouseName='北院供应室仓库',warehouseID=(select id from WareHouse where name='北院供应室仓库');

--8.材料入/退库单及明细仓库字段更新
update MaterialEntry set warehouseName='北院供应室仓库',warehouseID=(select id from WareHouse where name='北院供应室仓库');
update MaterialEntryItem set warehouseName='北院供应室仓库',warehouseID=(select id from WareHouse where name='北院供应室仓库');
--9.器械包/材料库存汇总入库
insert into GoodsStock(amount,goodsType,name,orgUnitName,orgUnitCode,tousseDefinitionId,wareHouseName,wareHouseId)
select count(0),'器械包',tousseName,'北院供应室',(select orgUnitCoding from OrgUnit where name='北院供应室'),tousseDefinition_id,'北院供应室仓库',(select id from WareHouse where name='北院供应室仓库') from TousseInstance where status in ('已灭菌','已消毒') group by tousseName,tousseDefinition_id;
insert into GoodsStock(amount,goodsType,name,orgUnitName,orgUnitCode,materialDefinitionId,wareHouseName,wareHouseId)
select sum(m.storage),'材料',name,'北院供应室',(select orgUnitCoding from OrgUnit where name='北院供应室'),id,'北院供应室仓库',(select id from WareHouse where name='北院供应室仓库') from MaterialDefinition m group by id , name;

--9.外部代理灭菌旧数据处理
update TousseInstance set invoicePlanId=(select foreignProxyDisinfection_id from ForeignProxyItem where id=TousseInstance.foreignProxyItem_id ) where invoicePlanId is null;
update ForeignProxyItem set invoicePlanId=foreignProxyDisinfection_id where foreignProxyDisinfection_id is not null and invoicePlanId is null;

update TousseInstance set applicationTousseItemId=(select id from TousseItem where recyclingApplication_ID=TousseInstance.invoicePlanId and tousseName=TousseInstance.tousseName ) where foreignProxyItem_id is not null and applicationTousseItemId is null;
update ForeignProxyItem set tousseItemId = (select id from TousseItem where recyclingApplication_ID=ForeignProxyItem.foreignProxyDisinfection_id and tousseName=ForeignProxyItem.tousseName) where tousseItemId is null;