-- 员工个人电话;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_self_phone')	
	ALTER table [renyuan] ADD [staff_self_phone] [varchar](11) NOT NULL

-- 员工父亲姓名;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_father')	
	ALTER table [renyuan] ADD [staff_father] [nvarchar](12) NOT NULL

-- 员工母亲姓名;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_mother')	
	ALTER table [renyuan] ADD [staff_mother] [nvarchar](12) NOT NULL

-- 员工父亲电话;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_father_phone')	
	ALTER table [renyuan] ADD [staff_father_phone] [varchar](11) NOT NULL	

-- 员工母亲电话;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_mother_phone')	
	ALTER table [renyuan] ADD [staff_mother_phone] [varchar](11) NOT NULL		

-- 紧急联系人姓名;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_emergency_name')	
	ALTER table [renyuan] ADD [staff_emergency_name] [nvarchar](12) NOT NULL	

-- 紧急联系人电话;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_emergency_phone')	
	ALTER table [renyuan] ADD [staff_emergency_phone] [varchar](11) NOT NULL	

-- 紧急联系人关系;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_emergency_relationship')	
	ALTER table [renyuan] ADD [staff_emergency_relationship] [nvarchar](8) NOT NULL		

-- 家庭住址;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_family_address')	
	ALTER table [renyuan] ADD [staff_family_address] [nvarchar](8) NOT NULL	

-- 入职时间;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_hire_date')	
	ALTER table [renyuan] ADD [staff_hire_date] [datetime] NOT NULL

-- 离职时间;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_leave_date')	
	ALTER table [renyuan] ADD [staff_leave_date] [datetime] NOT NULL	

-- 个人QQ;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_self_qq')	
	ALTER table [renyuan] ADD [staff_self_qq] [nvarchar](8) NOT NULL		

-- 个人微信;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_self_weixin')	
	ALTER table [renyuan] ADD [staff_self_weixin] [nvarchar](8) NOT NULL		

-- 个人email;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_self_email')	
	ALTER table [renyuan] ADD [staff_self_email] [nvarchar](8) NOT NULL	

-- 学历;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_educational')	
	ALTER table [renyuan] ADD [staff_educational] [nvarchar](8) NOT NULL		

-- 学历;
IF NOT EXISTS(select name FROM syscolumns WHERE id=object_id('renyuan') AND name='staff_educational')	
	ALTER table [renyuan] ADD [staff_educational] [nvarchar](8) NOT NULL	

------------------------------------------------------------------------------------------------------
--dindanbukuan2表 新添加字段;	
--对接人;pick up man;
if not exists(select name from syscolumns where id=object_id('dindanbukuan2') and name='pick_up_man')
	alter table [dindanbukuan2] add [pick_up_man] [nvarchar](12) NULL

--对接人部门;
if not exists(select name from syscolumns where id=object_id('dindanbukuan2') and name='pick_up_dept')	
	alter table [dindanbukuan2] add [pick_up_dept] [nvarchar](12) NULL

--实收款;amount received
if not exists(select name from syscolumns where id=object_id('dindanbukuan2') and name='amount_received')	
	alter table [dindanbukuan2] add [amount_received] [nvarchar](12) NULL

--接单人;Order Taker
if not exists(select name from syscolumns where id=object_id('dindanbukuan2') and name='order_taker')	
	alter table [dindanbukuan2] add [order_taker] [nvarchar](12) NULL

if not exists(select name from syscolumns where id=object_id('dindanbukuan2') and name='order_taker_dept')	
	alter table [dindanbukuan2] add [order_taker_dept] [nvarchar](12) NULL

--服务状态;service state
if not exists(select name from syscolumns where id = object_id('dindanbukuan2') and name = 'service_state')	
	alter table [dindanbukuan2] add [service_state] [nvarchar] (8) null
	
------------------------------------------------------------------------------------------------------	
--dindan表 同样添加相应字段;
--对接人;pick up man;	
if not exists(select name from syscolumns where id=object_id('dindan') and name='pick_up_man')
	alter table [dindan] add [pick_up_man] [nvarchar](12) NULL

--对接人部门;
if not exists(select name from syscolumns where id=object_id('dindan') and name='pick_up_dept')	
	alter table [dindan] add [pick_up_dept] [nvarchar](12) NULL

--实收款;amount received
if not exists(select name from syscolumns where id=object_id('dindan') and name='amount_received')	
	alter table [dindan] add [amount_received] [nvarchar](12) NULL

--接单人;Order Taker
if not exists(select name from syscolumns where id=object_id('dindan') and name='order_taker')	
	alter table [dindan] add [order_taker] [nvarchar](12) NULL
	
--服务状态;service state
if not exists(select name from syscolumns where id = object_id('dindan') and name = 'service_state')	
	alter table [dindan] add [service_state] [nvarchar] (8) null
	

-------------------------------------------------------------------------------------------------------
--服务状态;service state
if not exists(select name from syscolumns where id = object_id('workerreport') and name = 'staff_dept')	
	alter table [workerreport] add [staff_dept] [nvarchar] (12) null

-------------------------------------------------------------------------------------------------------
-- 生成加挑项目视图;
DROP VIEW [dbo].[receipt_item]
CREATE VIEW receipt_item as 	
select 
dindanbukuan2.autoid,					--自增列;
dindan.id , 							--订单号;
dindan.name1  ,							--老板;
dindan.name2 ,							--影楼;
dindan.phone1 ,							--老板电话;
dindanbukuan2.bz ,						--加挑项目;
dindanbukuan2.money ,					--加挑金额(应收款);
dindanbukuan2.order_taker ,				--开单人;
dindanbukuan2.order_taker_dept ,		--开单人部门;
dindanbukuan2.amount_received ,			--实收款;
dindanbukuan2.pick_up_man ,				--对接人;
dindanbukuan2.pick_up_dept ,			--对接人部门;
dindanbukuan2.service_state ,			--服务状态;
dindanbukuan2.ren2 ,					--录单人;
dindanbukuan2.date 						--录单时间;
from dindan inner join dbo.dindanbukuan2 ON dbo.dindan.id = dbo.dindanbukuan2.id
GO

	
	
	
-- 更新订单实收款, 从已收的收款记录中提取;	
;with tt as(
select sum(cast ([money] as bigint)) as summoney,id from dindanbukuan where kind = '3' or kind = '4' group by id
) update dindan set dindan.amount_received = cast((select summoney from tt where dindan.id = tt.id) as nvarchar)
	
-- 更新订单加挑实收款,从已收的收款中提取;
;with tt as(
select sum(cast ([money] as bigint)) as summoney,id,item as receipt_item from dindanbukuan where kind <> '3' and kind <> '4' group by id,item --order by id
) update dindanbukuan2 set amount_received = cast((select summoney from tt where dindanbukuan2.id = tt.id and dindanbukuan2.bz=tt.receipt_item) as nvarchar)