--   انتقال از بیماران به جدول پر
--   bimaran -->> per
--   Darmangah.dbo.bimaran -->>  myns_db.dbo.per



if exists (select * from Darmangah.dbo.sysobjects where id = object_id(N'[Darmangah].[dbo].[__transfer_bimaran]') )
drop table [Darmangah].[dbo].[__transfer_bimaran]

GO

select 
      code_bimar as cp ,
	  code_bimar as num,
      name as fname,
	  famil as lname,
	  name+' '+famil as np,
	  pedar as father,
	  tavalod as bdate,
	  code_gens as c_sex,
	  tarikh as sdate,
	  mobil ,
	  address as addr1,
	(select MAX(tarikh) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as edate,
	(select SUM(tedad_darman) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as onum,
	(select SUM(r_azad) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_cost,
	(select SUM(r_bimeh) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_insura,
	(select SUM(r_bimar) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_person,
	(select SUM(r_mokammel) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_insura1,
	(select SUM(r_yaraneh) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_insura2,
	(select SUM(r_takhfif) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_discount,
	(select SUM(r_takhfif_p) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_discount1,
	(select SUM(r_mabeh) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as a_differ,
	(select SUM(r_bed) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as cost,
	(select SUM(r_bes) from Darmangah.dbo.morajeat where code_bimar=dbm.code_bimar) as peymanet,
	myns.[dbo].[Fn_String_Mandeh_Bedehi_Be_Tafkik_Pezeshk_Old_Darmangah](dbm.code_bimar) as descr

		  into Darmangah.dbo.__transfer_bimaran

from Darmangah.dbo.bimaran dbm  order by  code_bimar

GO


update Darmangah.dbo.__transfer_bimaran set  sdate='1300/01/01'  where sdate is null
GO
update Darmangah.dbo.__transfer_bimaran set  onum=0  where onum is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_cost=0  where a_cost is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_insura=0  where a_insura is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_person=0  where a_person is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_insura1=0  where a_insura1 is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_insura2=0  where a_insura2 is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_discount=0  where a_discount is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_discount1=0  where a_discount1 is null
GO
update Darmangah.dbo.__transfer_bimaran set  a_differ=0  where a_differ is null
GO
update Darmangah.dbo.__transfer_bimaran set  cost=0  where cost is null
GO
update Darmangah.dbo.__transfer_bimaran set  peymanet=0  where peymanet is null
GO
update Darmangah.dbo.__transfer_bimaran set  cp=0  
GO

-- حذف شماره های منفی و ایجاد شمارهای جدید


ALTER TABLE Darmangah.dbo.__transfer_bimaran
   drop COLUMN  [cp]   

GO

ALTER TABLE Darmangah.dbo.__transfer_bimaran
   ADD  [cp]   Int not null  Identity(11111, 1)

GO

delete myns_db.dbo.per
GO

Insert into 
myns_db.dbo.per(cp,num,fname,lname,np,father,bdate,c_sex,sdate,mobil,addr1,edate) 
select 
		cp ,
		num,
		fname,
		lname,
		np,
		father,
		bdate,
		c_sex,
		sdate,
		mobil ,
		addr1,
		edate

from Darmangah.dbo.__transfer_bimaran order by  cp

GO


--  select top 10 * from  Darmangah.dbo.bimaran order by code_bimar DESC
--  select top 10 * from  myns_db.dbo.per order by cp DESC


--  انتقال از مراجعات به رسیپشن
--   morajeat -->> reception
--  Darmangah.dbo.morajeat -->>  myns_db.dbo.reception

delete myns_db.dbo.reception_hdr
GO
delete myns_db.dbo.reception
GO



Insert into 
myns_db.dbo.reception_hdr(id_hdr,cp,sdate,descr,ins_t,ins_cp) 
select 
	  cp as  id_hdr,
	  cp,
	  sdate,
	  descr,
	  GETDATE(),
	  -100

from Darmangah.dbo.__transfer_bimaran  order by  cp
GO

-- select top 10 * from Darmangah.dbo.__transfer_bimaran  order  by cp DESC


Insert into 
myns_db.dbo.reception(id_reception,id_hdr,sdate,cp,onum,a_cost,a_insura,a_person,a_insura1,a_insura2,a_discount,a_discount1,a_differ,cost,payment,descr,ins_t,ins_cp) 
select 
     cp as id_reception ,
	  cp as id_hdr,
	   sdate,
	   cp,
	   onum,
       a_cost,
	   a_insura,
	   a_person,
	   a_insura1,
	   a_insura2,
	  a_discount,
	  a_discount1,
	  a_differ,
	   cost,
	  peymanet,
	   descr,
	  GETDATE(),
	  -100

from Darmangah.dbo.__transfer_bimaran    order by  cp
GO