-- 远程服务(remote_service);
CREATE TABLE [dbo].[remote_service](
	[service_id] [varchar](38) NOT NULL,							-- 远程服务id(上传截图时使用,保存在服务器目录下);
	[order] [nvarchar](24) NOT NULL,								-- 远程的订单号; 
	[remote_date] [nvarchar](24) NOT NULL,							-- 远程日期; 
	[remote_submit_staff] [nvarchar](12) NOT NULL,					-- 远程提交员工;
	[remote_service_staff] [nvarchar](12) NOT NULL,					-- 远程服务员工;
	[remote_service_commodity][nvarchar](24) NOT NULL,				-- 远程服务的商品(经典片,铂金版,小程序等, 有配置表);
	[remote_service_problem][nvarchar](24) NOT NULL,				-- 远程服务问题(每种商品,都有独立的问题, 表配置表);
	[remote_service_status][nvarchar](8) NOT NULL,					-- 远程服务状态(已解决,未解决);
	[remote_service_result][nvarchar](8) NOT NULL,					-- 远程服务结果(员工对远程服务的满意结果:非常满意,满意,一般,客怨);
	[remote_service_describe][nvarchar](8) NOT NULL,				-- 远程服务描述;
	[remote_customer_contact][nvarchar](12) NOT NULL,				-- 远程客户的联系人;
	[remote_customer_qq][nvarchar](16) NOT NULL,					-- 远程客户的qq;
	[remote_customer_phone][nvarchar](16) NOT NULL,					-- 远程客户的手机;
	[return_visit_status][nvarchar](8) NOT NULL,					-- 回访状态(已回访,未回访);
	[return_visit_date][nvarchar](24) NOT NULL,						-- 回访日期;
	[return_visit_staff][nvarchar](12) NOT NULL,					-- 回访员工;
	[return_visit_result][nvarchar](12) NOT NULL,					-- 回访结果(客户对远程服务的满意度:非常满意,满意,一般,客怨);
	[return_visit_remark][nvarchar](250) NOT NULL,					-- 回访备注;
	CONSTRAINT [PK_service_id] PRIMARY KEY CLUSTERED ([service_id] ASC),    
) ON [PRIMARY]
GO

-- 远程商品表(remote_commodity);
CREATE TABLE [dbo].[remote_commodity](
	[commodity_id] [int] IDENTITY(1,1) NOT NULL,					-- 远程商品id;
	[commodity_name][nvarchar](24) NOT NULL,						-- 远程商品名;
	[commodity_remark][nvarchar](50) NOT NULL,						-- 远程商品备注;
	CONSTRAINT [PK_remote_commodity] PRIMARY KEY CLUSTERED ([commodity_id] ASC),
	CONSTRAINT [UK_remote_commodity] UNIQUE NONCLUSTERED ([commodity_name] ASC)
) ON [PRIMARY]
GO

-- 远程商品问题表(remote_commodity_problem);
CREATE TABLE [dbo].[remote_commodity_problem](
	[problem_id] [int] IDENTITY(1,1) NOT NULL,						-- 问题id;
	[commodity_id][nvarchar](40) NOT NULL,							-- 商品id;
	[problem_name][nvarchar](50) NOT NULL,							-- 问题名称;
	[problem_remark][nvarchar](250) NOT NULL,						-- 问题备注;
	CONSTRAINT [PK_remote_commodity_problem] PRIMARY KEY CLUSTERED ([problem_id] ASC),
	CONSTRAINT [UK_remote_commodity_problem] UNIQUE NONCLUSTERED ([commodity_id] ASC,[problem_name] ASC)
) ON [PRIMARY]
GO

-- 远程服务视图(remote_services);
CREATE VIEW [remote_services] AS
select
dbo.dindan.name1,		-- 老板姓名;
dbo.dindan.name2,		-- 影楼名称;
dbo.dindan.phone1,		-- 老板电话;
dbo.dindan.ren,			-- 接单人;
dbo.client.addr1,		-- 影楼地址;
dbo.client.qq1,			-- 影楼QQ;
dbo.dindan.[pinyin1],	-- 老板姓名拼音;
dbo.dindan.[pinyin2],	-- 影楼名称拼音;	
[remote_service].*
from [dindan] inner join [client]
on [dindan].[id] = [client].[id] inner join [remote_service]
on [remote_service].[order] = [client].[id]