lyfz_repos/铂金版源码/DBFile/SQL/创建表.sql

USE [DYBBERPDB]
GO
--isnull(ltrim(id),'合计')
--要使用.Net写的自定义聚合函数，需开启clr功能
--reconfigure;
--exec sp_configure 'clr enabled', '1'
--注册assembly

--CREATE ASSEMBLY StringUtilities FROM 'D:\LYFZCLR.dll'
--WITH PERMISSION_SET=SAFE;
--注册聚合函数

--CREATE AGGREGATE Concatenate(@input nvarchar(4000))
--RETURNS nvarchar(4000)
--EXTERNAL NAME [StringUtilities].[Concat];
--GO
 --删除聚合

--drop aggregate dbo.AggregateString
--drop assembly LYFZCLR

--SQL Server 如何启用CLR函数
exec sp_configure 'clr enabled' --查看状态  
exec sp_configure 'clr enabled',1 --1,启用clr,禁用clr
reconfigure with override
--要使用.Net写的自定义聚合函数，需以上代码开启clr功能

CREATE ASSEMBLY [LYFZCLR] AUTHORIZATION [dbo]
FROM 'D:\LYFZCLR.dll'
WITH PERMISSION_SET = SAFE;
GO
 
CREATE AGGREGATE [dbo].[AggregateString] (@FieldValue [nvarchar](4000))
RETURNS [nvarchar](4000)
EXTERNAL NAME [LYFZCLR].[AggregateString];
GO

--drop FUNCTION dbo.FunTrim
CREATE FUNCTION [dbo].[FunTrim] (@FieldValue [nvarchar](4000),@trimChar [nvarchar](4000))
RETURNS [nvarchar](4000) 
AS
EXTERNAL NAME [LYFZCLR].[UserDefinedFunctions].[FunTrim];
GO

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpCustomerGroup]') AND type in (N'U'))
begin
--创建客户组表
CREATE TABLE [dbo].[tb_ErpCustomerGroup](
	[id] [int]  IDENTITY(1,1) NOT NULL,
	--订单号 老客户时订单号为空
	[GP_OrderNumber] [nvarchar](50) not NULL,
	--客户关系组ID编号
	[GP_CustomerGroupID] [nvarchar](50) not NULL,
	--客户组类型 0 为其它客户/订单客户 1 为老客户  2 为意向客户
	[GP_GroupType] [int] not NULL,
	--客户类型  0 为婚纱订单客户  1 为儿童订单客户 2 写真订单客户 3 为服务套系 4 为其它订单客户 
	[GP_CustomerType] [int] not NULL,
	--客户状态 正常 意向 溜单 流失
	[GP_CustomerStatus] [nvarchar](50) not NULL,
	--客户流失或溜单原因 少于800 字
	[GP_LossReason] [nvarchar](800) not NULL,
	--意向程度 有点意向 很有意向 非常有意向等
	[GP_DegreeOfIntent] [nvarchar](50) not NULL,
	--客户来源
	[GP_CustomerSource] [nvarchar](50) not NULL,
	--介绍人CustomerID  如果为老客户转介绍来的客户时填写介绍人ID
	[GP_IntroducerCustomerID] [nvarchar](50) NULL,
	--备注说明  少于800 字
	[GP_Remark] [nvarchar](800)  NULL,
	--本组客户所属人的ID  (隶属于谁的客户) 如果为空时为公共客户
	[GP_BelongsPersonID] [nvarchar](50) NULL,
	--客户添加时间
	[GP_CreateDatetime] [datetime] not NULL,
	--客户修改时间
	[GP_UpdateDatetime] [datetime] not NULL,
    [GP_CreateName] [nvarchar](50) not NULL,
    [GP_UpdateName] [nvarchar](50) not NULL,
) 
--设置主键
ALTER TABLE [dbo].[tb_ErpCustomerGroup] ADD CONSTRAINT
PK_tb_ErpCustomerGroup_CustomerGroupID PRIMARY KEY CLUSTERED 
(
GP_CustomerGroupID
)

--唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tb_ErpCustomerGroup_CustomerGroupID] ON [dbo].[tb_ErpCustomerGroup] 
(
	[GP_CustomerGroupID] ASC
)
--索引
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomerGroup_OrderNumber] ON [dbo].[tb_ErpCustomerGroup] 
(
	[GP_OrderNumber] ASC
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户组表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单号 老客户时订单号为空' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_OrderNumber'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户关系组ID编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_CustomerGroupID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户组类型 0 为其它客户/订单客户 1 为老客户  2 为意向客户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_GroupType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户类型  0 为婚纱订单客户  1 为儿童订单客户 2 写真订单客户 3 为服务套系 4 为其它订单客户 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_CustomerType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户状态 正常 意向 溜单 流失' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_CustomerStatus'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户流失或溜单原因 少于800 字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_LossReason'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'意向程度 有点意向 很有意向 非常有意向等' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_DegreeOfIntent'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户来源' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_CustomerSource'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'介绍人CustomerID  如果为老客户转介绍来的客户时填写介绍人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_IntroducerCustomerID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注说明 少于800字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_Remark'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本组客户所属人的ID  (隶属于谁的客户) 如果为空时为公共客户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_BelongsPersonID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_CreateDatetime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroup', @level2type=N'COLUMN',@level2name=N'GP_UpdateDatetime'

end

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpCustomerGroupMembers]') AND type in (N'U'))
begin
--创建客户组成员表
CREATE TABLE [dbo].[tb_ErpCustomerGroupMembers](
	[id] [int]  IDENTITY(1,1) NOT NULL,
	--客户关系组ID编号
	[GM_CustomerGroupID] [nvarchar](50) not NULL,
	--客户人员表中的客户编号ID
	[GM_CustomerID] [varchar](50) not NULL,
	--是否为主联系人 1 为主联系人 
	[GM_Master] [int] not NULL,
	--是否为摄影主角 1 为摄影主角 表示在本次订单中要参与拍摄的客户
	[GM_ProtagonistCustomer] [int] not NULL,
	--相关人ID 儿童版订单客户时为宝贝ID   其它订单客户时为主联系人ID
	[GM_RelatedPersonID] [nvarchar](50) not NULL,
	--与相关人的关系  如 本人、情吕、夫妻、父子、母子、父女、母女、兄弟、姐妹、亲人和朋友等
	[GM_Relation] [nvarchar](50) not NULL,
	--是否已成功下单  1 为已成功下单  0 为下单未成功
	[GM_IsOrder] [int] not NULL,
	--已订单号 如果 IsOrder 为 1 填入订单号
	[GM_IsOrderNumber] [nvarchar](50) NULL,
	
) 
--联合主键
ALTER TABLE [dbo].[tb_ErpCustomerGroupMembers] ADD CONSTRAINT
PK_tb_ErpCustomerGroupMembers_CustomerGroupID_And_CustomerID PRIMARY KEY CLUSTERED 
(
GM_CustomerGroupID,
GM_CustomerID
)
--索引
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomerGroupMembers_GM_CustomerGroupID] ON [dbo].[tb_ErpCustomerGroupMembers] 
(
	[GM_CustomerGroupID] ASC
)
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomerGroupMembers_GM_CustomerID] ON [dbo].[tb_ErpCustomerGroupMembers] 
(
	[GM_CustomerID] ASC
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户组成员表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户关系组ID编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_CustomerGroupID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户人员表中的客户编号ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_CustomerID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为主联系人 1 为主联系人 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_Master'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为摄影主角 1 为摄影主角 表示在本次订单中要参与拍摄的客户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_ProtagonistCustomer'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'相关人ID 儿童版订单客户时为宝贝ID   其它订单客户时为主联系人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_RelatedPersonID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'与相关人的关系  如 本人、情吕、夫妻、父子、母子、父女、母女、兄弟、姐妹、亲人和朋友等' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_Relation'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否已成功下单  1 为已成功下单  0 为下单未成功' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_IsOrder'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已订单号 如果 IsOrder 为 1 填入订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomerGroupMembers', @level2type=N'COLUMN',@level2name=N'GM_IsOrderNumber'

end

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpCustomersTrackRecord]') AND type in (N'U'))
begin
--创建客户跟踪记录表
CREATE TABLE [dbo].[tb_ErpCustomersTrackRecord](
	[id] [int]  IDENTITY(1,1) NOT NULL,
	--跟踪的客户组ID编号
	[TR_CustomerGroupID] [nvarchar](50) not NULL,
	--本次联系的客户人员表中的客户编号ID
	[TR_CustomerID] [nvarchar](50) not NULL,
	--本次跟踪方式  面谈、坐机电话、手机通话、邮件、短信、QQ、微信、网络留言、其它通信 
	[TR_TraceWay] [nvarchar](50) not NULL,
	--本次跟踪类型  流程按排、业务咨询、满意度调查、意向跟踪、溜单追回、关系维护、活动推广
	[TR_TraceType] [nvarchar](50) not NULL,
	--本次沟通具体事项  如：按排拍摄时间、按排选片时间和打结婚纪念日电话等
	[TR_SpecificMatters] [nvarchar](4000) not NULL,
	--本次沟通细节记录  如果客户返馈的信息 短信内容 QQ聊天记录等
	[TR_TraceDetailedly] [nvarchar](max) not NULL,
	--如果是电话(一搬指通过来电精灵通话)沟通的通话录音文件路径
	[TR_CallRecording] [nvarchar](500) not NULL,
	--少于4000字的备注说明 本次跟踪的特殊说明
	[TR_Remark] [nvarchar](4000) NULL,
	--本次跟踪时间（为与客户沟通的开始时间）
	[TR_TraceDateTime] [DateTime] not NULL,
	--本次路踪时长(为与客户沟通所用时间 单位为分钟)  如：15  (分钟)
	[TR_TraceTimeLength] int not null,
	--本次跟踪人员ID(如门市)
	[TR_TracePersonID] [nvarchar](50) not NULL,
	--记录录入时间
	[TR_CreateDateTime] [DateTime] not NULL,
	--记录修改时间
	[TR_UpdateDateTime] [DateTime] not NULL,
) 
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpCustomersTrackRecord]') AND name = N'PK_tb_ErpCustomersTrackRecord_CustomerGroupID_And_CustomerID')
ALTER TABLE [dbo].[tb_ErpCustomersTrackRecord] DROP CONSTRAINT [PK_tb_ErpCustomersTrackRecord_CustomerGroupID_And_CustomerID]
GO
--设置主键
ALTER TABLE [dbo].[tb_ErpCustomersTrackRecord] ADD CONSTRAINT
PK_tb_ErpCustomersTrackRecord_id PRIMARY KEY CLUSTERED 
(
  id
)

--索引
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomersTrackRecord_CustomerGroupID] ON [dbo].[tb_ErpCustomersTrackRecord] 
(
	[TR_CustomerGroupID] ASC
)
--索引
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomersTrackRecord_CustomerID] ON [dbo].[tb_ErpCustomersTrackRecord] 
(
	[TR_CustomerID] ASC
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户跟踪记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户关系组ID编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_CustomerGroupID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次联系的客户人员表中的客户编号ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_CustomerID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次跟踪方式  面谈、坐机电话、手机通话、邮件、短信、QQ、微信、网络留言、其它通信  ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TraceWay'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次跟踪类型  流程按排、业务咨询、满意度调查、意向跟踪、溜单追回、关系维护、活动推广' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TraceType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次沟通具体事项  如：按排拍摄时间、按排选片时间和打结婚纪念日电话等' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_SpecificMatters'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次沟通细节记录  如果客户返馈的信息 短信内容 QQ聊天记录等' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TraceDetailedly'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'如果是电话(一搬指通过来电精灵通话)沟通的通话录音文件路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_CallRecording'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'少于4000字的备注说明 本次跟踪的特殊说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_Remark'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次跟踪时间（为与客户沟通的开始时间）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TraceDateTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次路踪时长(为与客户沟通所用时间 单位为分钟)  如：15  (分钟)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TraceTimeLength'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本次跟踪人员ID(如门市)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_TracePersonID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录录入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_CreateDateTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomersTrackRecord', @level2type=N'COLUMN',@level2name=N'TR_UpdateDateTime'

end

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpCustomer]') AND type in (N'U'))
begin
--创建客户人员信息表
CREATE TABLE [dbo].[tb_ErpCustomer](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Cus_CustomerNumber] [nvarchar](50) primary key  NOT NULL,
	[Cus_CustomizeNumber] [nvarchar](50) NULL,
	[Cus_Type] [nvarchar](20) NOT NULL,
	[Cus_ServiceType] [nvarchar](max) NULL,
	[Cus_Grade] [nvarchar](20) NULL,
	[Cus_Name] [nvarchar](20) NOT NULL,
	[Cus_Sex] [bit] NULL,
	[Cus_Birthday] [datetime] NULL,
	[Cus_BirthdayLunar] [nvarchar](1) NULL,
	[Cus_DayForMarriage] [datetime] NULL,
	[Cus_DayForMarriageLunar] [nvarchar](1) NULL,
	[Cus_Relations] [nvarchar](max) NULL,
	[Cus_QQ] [nvarchar](20) NULL,
	[Cus_MicroSignal] [nvarchar](20) NULL,
	[Cus_Telephone] [nvarchar](20) NULL,
	[Cus_FixedPhone] [nvarchar](20) NULL,
	[Cus_Region] [nvarchar](100) NULL,
	[Cus_Address] [nvarchar](100) NULL,
	[Cus_WorkUnit] [nvarchar](100) NULL,
	[Cus_BabyWeight] [nvarchar](20) NULL,
	[Cus_BornHospital] [nvarchar](50) NULL,
	[Cus_Zodiac] [nvarchar](2) NULL,
	[Cus_CustomerSource] [nvarchar](100) NULL,
	[Cus_Status] [nvarchar](10) NULL,
	[Cus_LossReason] [nvarchar](max) NULL,
	[Cus_DegreeOfIntent] [nvarchar](20) NULL,
	[Cus_TrackName] [nvarchar](300) NULL,
	[Cus_Remark] [nvarchar](max) NULL,
	[Cus_CreateDateTime] [datetime] NULL,
	[Cus_CreateName] [nvarchar](20) NULL,
	[Cus_UpdateDateTime] [datetime] NULL,
	[Cus_UpdateName] [nvarchar](20) NULL,
) 
CREATE NONCLUSTERED INDEX [IX_tb_ErpCustomer_Cus_Name] ON [dbo].[tb_ErpCustomer] 
(
	[Cus_Name] ASC
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'ID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_CustomerNumber'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户自定义编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_CustomizeNumber'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户类型 订单客户 老客户  意向客户  儿童客户 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Type'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客服标记  Dress:婚纱客户 Baby:儿童客户 Portrait:写真客户 Other:客户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_ServiceType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Grade'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Name'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户性别 0(false):男,1(true):女' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Sex'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户生日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Birthday'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生日历类  0:公历 1:农历 2:农历润月' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_BirthdayLunar'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户婚期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_DayForMarriage'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'婚期历类  0:公历 1:农历 2:农历润月' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_DayForMarriageLunar'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'称呼、关系、与宝贝关系' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Relations'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户QQ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_QQ'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'微信号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_MicroSignal'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Telephone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户固定电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_FixedPhone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户所在地区' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Region'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Address'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户工作单位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_WorkUnit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'宝贝体重' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_BabyWeight'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'宝贝出生医院' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_BornHospital'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生肖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Zodiac'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户来源' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_CustomerSource'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户状态  正常 意向  流失' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Status'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户流失原因' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_LossReason'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'意向程度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_DegreeOfIntent'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'意向有权跟踪人   可以有多个跟踪人，多个时中间用'',''分割' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_TrackName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_Remark'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'录入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_CreateDateTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'录入者编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_CreateName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_UpdateDateTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改者编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer', @level2type=N'COLUMN',@level2name=N'Cus_UpdateName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户资料' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCustomer'

end

--创建视图 客户转公历生日(婚期)日期视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_ErpCustomer]'))
DROP VIEW [dbo].[View_ErpCustomer]
GO
CREATE VIEW [View_ErpCustomer]
AS
SELECT  
      [Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      --,[Cus_Type]
      --,[Cus_ServiceType]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,Cus_NamePinyin as [Cus_Name_py]
      ,[Cus_Sex]
      ,dbo.Fun_ConvertSex([Cus_Sex]) as [Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,dbo.Fun_GetGregorianCalendar([Cus_Birthday],[Cus_BirthdayLunar]) as GregorianCalendar_Birthday
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([Cus_Birthday],[Cus_BirthdayLunar]) as GregorianCalendar_NowYearBirthday
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,dbo.Fun_GetGregorianCalendar([Cus_DayForMarriage],[Cus_DayForMarriageLunar]) as GregorianCalendar_DayForMarriage
      ,dbo.Fun_GetGregorianCalendarNowYearBirthday([Cus_DayForMarriage],[Cus_DayForMarriageLunar]) as GregorianCalendar_NowYearDayForMarriage
      --,[Cus_Relations] 关系
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
     -- ,[Cus_LossReason]客户流失原因
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      --,[Cus_Remark]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
  FROM  tb_ErpCustomer 
go


--创建视图
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_CustomerGroupMembersAndErpCustomer]'))
DROP VIEW [dbo].[View_CustomerGroupMembersAndErpCustomer]
GO
CREATE VIEW [View_CustomerGroupMembersAndErpCustomer]
AS
SELECT  [tb_ErpCustomerGroupMembers].[id]
      ,[GM_CustomerGroupID]
      ,[GM_CustomerID]
      ,[GM_Master]
      ,[GM_ProtagonistCustomer]
      ,[GM_RelatedPersonID]
      ,[GM_Relation]
      ,[GM_IsOrder]
      ,[GM_IsOrderNumber]
      ,[Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,[Cus_Name_py]
      ,[Cus_Sex]
      ,[Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,GregorianCalendar_Birthday
      ,GregorianCalendar_NowYearBirthday
      ,FLOOR(datediff(day,GregorianCalendar_Birthday,getdate())/365.25) as Age_Year
      ,DATEDIFF(day, GregorianCalendar_Birthday,Getdate()) as Age_Day
      ,dbo.Fun_GetAgeString(GregorianCalendar_Birthday) as Age_String
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,GregorianCalendar_DayForMarriage
      ,GregorianCalendar_NowYearDayForMarriage
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
      ,[dbo].[tb_ErpMemberCard].[Mc_CradNumber]
      ,[dbo].[tb_ErpMemberCard].[Mc_CardType]
  FROM [tb_ErpCustomerGroupMembers]
  left join [View_ErpCustomer] 
  on [tb_ErpCustomerGroupMembers].[GM_CustomerID]=[View_ErpCustomer].Cus_CustomerNumber
  left join
		[dbo].[tb_ErpMemberCard]
		on [View_ErpCustomer].Cus_CustomerNumber=[dbo].[tb_ErpMemberCard].[Mc_CustomerNumber]
go

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]'))
DROP VIEW [dbo].[View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]
GO
CREATE VIEW [View_CustomerGroupAndCustomerGroupMembersAndErpCustomer]
AS
SELECT  [tb_ErpCustomerGroup].[id]
      ,[GP_OrderNumber]
      ,[GP_CustomerGroupID]
      ,[GP_GroupType]
      ,[GP_CustomerType]
      ,[GP_CustomerStatus]
      ,[GP_LossReason]
      ,[GP_DegreeOfIntent]
      ,[GP_CustomerSource]
      ,[GP_IntroducerCustomerID]
      ,[GP_Remark]
      ,[GP_BelongsPersonID]
      ,[GP_CreateDatetime]
      ,[GP_UpdateDatetime]

      ,[GM_CustomerGroupID]
      ,[GM_CustomerID]
      ,[GM_Master]
      ,[GM_ProtagonistCustomer]
      ,[GM_RelatedPersonID]
      ,[GM_Relation]
      ,[GM_IsOrder]
      ,[GM_IsOrderNumber]
      
      ,[Cus_CustomerNumber]
      ,[Cus_CustomizeNumber]
      ,[Cus_Grade]
      ,[Cus_Name]
      ,[Cus_Name_py]
      ,[Cus_Sex]
      ,[Cus_Sex_cs]
      ,[Cus_Birthday]
      ,[Cus_BirthdayLunar]
      ,[GregorianCalendar_Birthday]
      ,[GregorianCalendar_NowYearBirthday]
      ,FLOOR(datediff(day,GregorianCalendar_Birthday,getdate())/365.25) as Age_Year
      ,DATEDIFF(day, GregorianCalendar_Birthday,Getdate()) as Age_Day
      ,dbo.Fun_GetAgeString(GregorianCalendar_Birthday) as Age_String
      ,[Cus_DayForMarriage]
      ,[Cus_DayForMarriageLunar]
      ,GregorianCalendar_DayForMarriage
      ,GregorianCalendar_NowYearDayForMarriage
      ,[Cus_QQ]
      ,[Cus_MicroSignal]
      ,[Cus_Telephone]
      ,[Cus_FixedPhone]
      ,[Cus_Region]
      ,[Cus_Address]
      ,[Cus_WorkUnit]
      ,[Cus_BabyWeight]
      ,[Cus_BornHospital]
      ,[Cus_Zodiac]
      ,[Cus_CustomerSource]
      ,[Cus_Status]
      ,[Cus_DegreeOfIntent]
      ,[Cus_TrackName]
      ,[Cus_CreateDateTime]
      ,[Cus_CreateName]
      ,[Cus_UpdateDateTime]
      ,[Cus_UpdateName]
  FROM [tb_ErpCustomerGroup]
  left join
  dbo.tb_ErpCustomerGroupMembers
  on [tb_ErpCustomerGroup].[GP_CustomerGroupID]=dbo.tb_ErpCustomerGroupMembers.[GM_CustomerGroupID]
 left join
  [dbo].[View_ErpCustomer]
  on dbo.tb_ErpCustomerGroupMembers.GM_CustomerID=[dbo].[View_ErpCustomer].Cus_CustomerNumber
  go
  
  

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_ConvertSex]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Fun_ConvertSex]
GO

CREATE FUNCTION dbo.Fun_ConvertSex(
@value int --性别值
)
/****** 
转换性别

创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS nvarchar(10)
AS
BEGIN
    DECLARE @ret nvarchar(10)
    SET @ret = '男'
    if @value=0
    SELECT @ret = '男'
    else 
    SELECT @ret = '女'
   Return @ret	
END
GO



CREATE FUNCTION dbo.Fun_GetAgeString(
@Birthday datetime
)
/****** 
根据公历生日日期计算返回年龄大小字符串 小于1岁的返回天数


创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS nvarchar(100)
AS
begin
  declare @ret nvarchar(100)
  declare @age int
  declare @age_day int
  set @age=FLOOR(datediff(day,@Birthday,getdate())/365.25) 
  set @age_day=DATEDIFF(day, @Birthday,Getdate())
  if @age < 1
  set @ret =CONVERT(varchar(100), @age_day)+'天'
  else
  set @ret =CONVERT(varchar(100), @age)+'岁'
return @ret
end
GO



CREATE FUNCTION dbo.Fun_GetGregorianCalendar(
@Birthday varchar(100),
@BirthdayLunar nvarchar(10)--, --是否为农历日期 0 为公历直接原日期返回 1 为农历将转为公历日期 2 为农历闰月将转为对闰月对应的公历日期 
)
/****** 
转农历为公历 获取公历生日日期 


创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS datetime
AS
begin

  declare @retdates datetime
  declare @isLeapMonth bit
if CONVERT(int, left(@Birthday,4))>1900
 begin
  if len(@Birthday)>=8
  begin
  set @isLeapMonth=0
  if @BirthdayLunar='2'
  set @isLeapMonth=1
  if @BirthdayLunar='0'
  set @retdates=@Birthday
  else 
  set @retdates= dbo.GetCalender(Month(Replace(Replace(@Birthday,'-29','-28'),'-30','-28')),Replace(Right(@Birthday,2),'-',''),Left(@Birthday,4),@isLeapMonth)
  end
end
return CONVERT(varchar(100), @retdates, 20)
end
GO

CREATE FUNCTION dbo.Fun_GetGregorianCalendarBirthday(
@month varchar(3),
@day   varchar(3),
@now datetime,
@BirthdayLunar nvarchar(10)--, --是否为农历日期 0 为公历直接原日期返回 1 为农历将转为公历日期 2 为农历闰月将转为对闰月对应的公历日期 
)

/****** 
转农历为公历 获取公历生日日期 如果@BirthdayLunar=1 为农历将转为公历 如是0 直接返回
@isLeapMonth 如果为 1 返回闰月对应的公历日期

创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS datetime
AS
begin

  declare @retdates datetime
  declare @isLeapMonth bit
  set @isLeapMonth=0
  if @BirthdayLunar='2'
  set @isLeapMonth=1
  if @BirthdayLunar='0'
  set @retdates=CONVERT(varchar(100),YEAR(@now))+'-'+@month+'-'+@day
  else 
  set @retdates= dbo.GetCalender(@month,@day,@now,@isLeapMonth)

return CONVERT(varchar(100), @retdates, 20)
end
GO


CREATE FUNCTION dbo.Fun_GetGregorianCalendarNowYearBirthday(
@Birthday varchar(100),
@BirthdayLunar nvarchar(10)--, --是否为农历日期 0 为公历直接原日期返回 1 为农历将转为公历日期 2 为农历闰月将转为对闰月对应的公历日期 
)

/****** 
根据生日获取当前年的公历生日日期

创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS datetime
AS
begin

  declare @retdates datetime
  declare @isLeapMonth bit
  set @isLeapMonth=0
  declare @month varchar(3)
  declare @day   varchar(3)
  declare @now datetime
   if CONVERT(int, left(@Birthday,4))>1900
 begin
  if len(@Birthday)>=8
  begin
  set @month=Month(Replace(Replace(@Birthday,'-29','-28'),'-30','-28'))
  set @day =Replace(Right(@Birthday,2),'-','')
  set @now=Getdate()
  
  if @BirthdayLunar='2'
  set @isLeapMonth=1
  
  if @BirthdayLunar='0'
  set @retdates=CONVERT(varchar(100),YEAR(@now))+'-'+@month+'-'+@day
  else 
  set @retdates= dbo.GetCalender(@month,@day,@now,@isLeapMonth)
  end
  end
return CONVERT(varchar(100), @retdates, 20)
end
GO

CREATE FUNCTION dbo.Fun_GetLunarCalendar(
@Birthday nvarchar(100),
@BirthdayLunar nvarchar(10)--, --是否为农历日期 0 为公历将转为农历日期返回 大于0 为农历直接原日期返回 
)
/****** 
转公历为农历 获取农历生日日期 


创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS nvarchar(10)
AS
begin

  declare @retdates nvarchar(100)

  if @BirthdayLunar='0'
  --set @retdates= dbo.Proc_ConvertLunar(@Birthday)
  set @retdates=(select top 1 Lunar from LunarCalenderContrastTable where  Calender=@Birthday)
  else 
  set @retdates=CONVERT(varchar(10), @Birthday)
  set @retdates=replace(@retdates,' 0','')
  
return CONVERT(varchar(10), @retdates)
end
GO

CREATE FUNCTION dbo.Fun_GetLunarCalendarBirthday(
@month varchar(3),
@day   varchar(3),
@now datetime,
@BirthdayLunar nvarchar(10)--, --是否为农历日期 0 为公历直接原日期返回 1 为农历将转为公历日期 2 为农历闰月将转为对闰月对应的公历日期 
)

/****** 
转公历为农历 获取农历生日日期 

创建人：刘超
创建日期：2015-6-11 14:10 

修改人：
修改说明：
修改日期：

******/
RETURNS nvarchar(10)
AS
begin

  declare @retdates nvarchar(100)

  if @BirthdayLunar='0' 
  --set @retdates= dbo.Proc_ConvertLunar( CONVERT(varchar(10),YEAR(@now))+'-'+@month+'-'+@day)
   set @retdates=(select top 1 Lunar from LunarCalenderContrastTable where  Calender=(CONVERT(varchar(10),YEAR(@now))+'-'+@month+'-'+@day))
  else 
  set @retdates= CONVERT(varchar(10),YEAR(@now))+'-'+@month+'-'+@day

return CONVERT(varchar(100), @retdates)
end
GO


--sql汉字转拼音 
create function [dbo].[Fun_GetPinyin](@words nvarchar(4000)) 
returns varchar(8000) 
as 
begin 
declare @word nchar(1) 
declare @pinyin varchar(8000) 
declare @i int 
declare @words_len int 
declare @unicode int 
set @i = 1 
set @words = ltrim(rtrim(@words)) 
set @words_len = len(@words) 
while (@i <= @words_len) --循环取字符 
begin 
set @word = substring(@words, @i, 1) 
set @unicode = unicode(@word) 
set @pinyin = ISNULL(@pinyin+space(1),'')+ 
(case when unicode(@word) between 19968 and 19968+20901 then 
(select top 1 py from ( 
select 'a' as py,N'厑' as word 
union all select 'ai',N'靉' 
union all select 'an',N'黯' 
union all select 'ang',N'醠' 
union all select 'ao',N'驁' 
union all select 'ba',N'欛' 
union all select 'bai',N'瓸' --韛兡瓸 
union all select 'ban',N'瓣' 
union all select 'bang',N'鎊' 
union all select 'bao',N'鑤' 
union all select 'bei',N'鐾' 
union all select 'ben',N'輽' 
union all select 'beng',N'鏰' 
union all select 'bi',N'鼊' 
union all select 'bian',N'變' 
union all select 'biao',N'鰾' 
union all select 'bie',N'彆' 
union all select 'bin',N'鬢' 
union all select 'bing',N'靐' 
union all select 'bo',N'蔔' 
union all select 'bu',N'簿' 
union all select 'ca',N'囃' 
union all select 'cai',N'乲' --縩乲 
union all select 'can',N'爘' 
union all select 'cang',N'賶' 
union all select 'cao',N'鼜' 
union all select 'ce',N'簎' 
union all select 'cen',N'笒' 
union all select 'ceng',N'乽' --硛硳岾猠乽 
union all select 'cha',N'詫' 
union all select 'chai',N'囆' 
union all select 'chan',N'顫' 
union all select 'chang',N'韔' 
union all select 'chao',N'觘' 
union all select 'che',N'爡' 
union all select 'chen',N'讖' 
union all select 'cheng',N'秤' 
union all select 'chi',N'鷘' 
union all select 'chong',N'銃' 
union all select 'chou',N'殠' 
union all select 'chu',N'矗' 
union all select 'chuai',N'踹' 
union all select 'chuan',N'鶨' 
union all select 'chuang',N'愴' 
union all select 'chui',N'顀' 
union all select 'chun',N'蠢' 
union all select 'chuo',N'縒' 
union all select 'ci',N'嗭' --賜嗭 
union all select 'cong',N'謥' 
union all select 'cou',N'輳' 
union all select 'cu',N'顣' 
union all select 'cuan',N'爨' 
union all select 'cui',N'臎' 
union all select 'cun',N'籿' 
union all select 'cuo',N'錯' 
union all select 'da',N'橽' 
union all select 'dai',N'靆' 
union all select 'dan',N'饏' 
union all select 'dang',N'闣' 
union all select 'dao',N'纛' 
union all select 'de',N'的' 
union all select 'den',N'扽' 
union all select 'deng',N'鐙' 
union all select 'di',N'螮' 
union all select 'dia',N'嗲' 
union all select 'dian',N'驔' 
union all select 'diao',N'鑃' 
union all select 'die',N'嚸' --眰嚸 
union all select 'ding',N'顁' 
union all select 'diu',N'銩' 
union all select 'dong',N'霘' 
union all select 'dou',N'鬭' 
union all select 'du',N'蠹' 
union all select 'duan',N'叾' --籪叾 
union all select 'dui',N'譵' 
union all select 'dun',N'踲' 
union all select 'duo',N'鵽' 
union all select 'e',N'鱷' 
union all select 'en',N'摁' 
union all select 'eng',N'鞥' 
union all select 'er',N'樲' 
union all select 'fa',N'髮' 
union all select 'fan',N'瀪' 
union all select 'fang',N'放' 
union all select 'fei',N'靅' 
union all select 'fen',N'鱝' 
union all select 'feng',N'覅' 
union all select 'fo',N'梻' 
union all select 'fou',N'鴀' 
union all select 'fu',N'猤' --鰒猤 
union all select 'ga',N'魀' 
union all select 'gai',N'瓂' 
union all select 'gan',N'灨' 
union all select 'gang',N'戇' 
union all select 'gao',N'鋯' 
union all select 'ge',N'獦' 
union all select 'gei',N'給' 
union all select 'gen',N'搄' 
union all select 'geng',N'堩' --亙堩啹喼嗰 
union all select 'gong',N'兣' --熕贑兝兣 
union all select 'gou',N'購' 
union all select 'gu',N'顧' 
union all select 'gua',N'詿' 
union all select 'guai',N'恠' 
union all select 'guan',N'鱹' 
union all select 'guang',N'撗' 
union all select 'gui',N'鱥' 
union all select 'gun',N'謴' 
union all select 'guo',N'腂' 
union all select 'ha',N'哈' 
union all select 'hai',N'饚' 
union all select 'han',N'鶾' 
union all select 'hang',N'沆' 
union all select 'hao',N'兞' 
union all select 'he',N'靏' 
union all select 'hei',N'嬒' 
union all select 'hen',N'恨' 
union all select 'heng',N'堼' --堼囍 
union all select 'hong',N'鬨' 
union all select 'hou',N'鱟' 
union all select 'hu',N'鸌' 
union all select 'hua',N'蘳' 
union all select 'huai',N'蘾' 
union all select 'huan',N'鰀' 
union all select 'huang',N'鎤' 
union all select 'hui',N'顪' 
union all select 'hun',N'諢' 
union all select 'huo',N'夻' 
union all select 'ji',N'驥' 
union all select 'jia',N'嗧' 
union all select 'jian',N'鑳' 
union all select 'jiang',N'謽' 
union all select 'jiao',N'釂' 
union all select 'jie',N'繲' 
union all select 'jin',N'齽' 
union all select 'jing',N'竸' 
union all select 'jiong',N'蘔' 
union all select 'jiu',N'欍' 
union all select 'ju',N'爠' 
union all select 'juan',N'羂' 
union all select 'jue',N'钁' 
union all select 'jun',N'攈' 
union all select 'ka',N'鉲' 
union all select 'kai',N'乫' --鎎乫 
union all select 'kan',N'矙' 
union all select 'kang',N'閌' 
union all select 'kao',N'鯌' 
union all select 'ke',N'騍' 
union all select 'ken',N'褃' 
union all select 'keng',N'鏗' --巪乬唟厼怾 
union all select 'kong',N'廤' 
union all select 'kou',N'鷇' 
union all select 'ku',N'嚳' 
union all select 'kua',N'骻' 
union all select 'kuai',N'鱠' 
union all select 'kuan',N'窾' 
union all select 'kuang',N'鑛' 
union all select 'kui',N'鑎' 
union all select 'kun',N'睏' 
union all select 'kuo',N'穒' 
union all select 'la',N'鞡' 
union all select 'lai',N'籟' 
union all select 'lan',N'糷' 
union all select 'lang',N'唥' 
union all select 'lao',N'軂' 
union all select 'le',N'餎' 
union all select 'lei',N'脷' --嘞脷 
union all select 'leng',N'睖' 
union all select 'li',N'瓈' 
union all select 'lia',N'倆' 
union all select 'lian',N'纞' 
union all select 'liang',N'鍄' 
union all select 'liao',N'瞭' 
union all select 'lie',N'鱲' 
union all select 'lin',N'轥' --轥拎 
union all select 'ling',N'炩' 
union all select 'liu',N'咯' --瓼甅囖咯 
union all select 'long',N'贚' 
union all select 'lou',N'鏤' 
union all select 'lu',N'氇' 
union all select 'lv',N'鑢' 
union all select 'luan',N'亂' 
union all select 'lue',N'擽' 
union all select 'lun',N'論' 
union all select 'luo',N'鱳' 
union all select 'ma',N'嘛' 
union all select 'mai',N'霢' 
union all select 'man',N'蘰' 
union all select 'mang',N'蠎' 
union all select 'mao',N'唜' 
union all select 'me',N'癦' --癦呅 
union all select 'mei',N'嚜' 
union all select 'men',N'們' 
union all select 'meng',N'霥' --霿踎 
union all select 'mi',N'羃' 
union all select 'mian',N'麵' 
union all select 'miao',N'廟' 
union all select 'mie',N'鱴' --鱴瓱 
union all select 'min',N'鰵' 
union all select 'ming',N'詺' 
union all select 'miu',N'謬' 
union all select 'mo',N'耱' --耱乮 
union all select 'mou',N'麰' --麰蟱 
union all select 'mu',N'旀' 
union all select 'na',N'魶' 
union all select 'nai',N'錼' 
union all select 'nan',N'婻' 
union all select 'nang',N'齉' 
union all select 'nao',N'臑' 
union all select 'ne',N'呢' 
union all select 'nei',N'焾' --嫩焾 
union all select 'nen',N'嫩' 
union all select 'neng',N'能' --莻嗯鈪銰啱 
union all select 'ni',N'嬺' 
union all select 'nian',N'艌' 
union all select 'niang',N'釀' 
union all select 'niao',N'脲' 
union all select 'nie',N'钀' 
union all select 'nin',N'拰' 
union all select 'ning',N'濘' 
union all select 'niu',N'靵' 
union all select 'nong',N'齈' 
union all select 'nou',N'譳' 
union all select 'nu',N'搙' 
union all select 'nv',N'衄' 
union all select 'nue',N'瘧' 
union all select 'nuan',N'燶' --硸黁燶郍 
union all select 'nuo',N'桛' 
union all select 'o',N'鞰' --毮夞乯鞰 
union all select 'ou',N'漚' 
union all select 'pa',N'袙' 
union all select 'pai',N'磗' --鎃磗 
union all select 'pan',N'鑻' 
union all select 'pang',N'胖' 
union all select 'pao',N'礮' 
union all select 'pei',N'轡' 
union all select 'pen',N'喯' 
union all select 'peng',N'喸' --浌巼闏乶喸 
union all select 'pi',N'鸊' 
union all select 'pian',N'騙' 
union all select 'piao',N'慓' 
union all select 'pie',N'嫳' 
union all select 'pin',N'聘' 
union all select 'ping',N'蘋' 
union all select 'po',N'魄' 
union all select 'pou',N'哛' --兺哛 
union all select 'pu',N'曝' 
union all select 'qi',N'蟿' 
union all select 'qia',N'髂' 
union all select 'qian',N'縴' 
union all select 'qiang',N'瓩' --羻兛瓩 
union all select 'qiao',N'躈' 
union all select 'qie',N'籡' 
union all select 'qin',N'藽' 
union all select 'qing',N'櫦' 
union all select 'qiong',N'瓗' 
union all select 'qiu',N'糗' 
union all select 'qu',N'覻' 
union all select 'quan',N'勸' 
union all select 'que',N'礭' 
union all select 'qun',N'囕' 
union all select 'ran',N'橪' 
union all select 'rang',N'讓' 
union all select 'rao',N'繞' 
union all select 're',N'熱' 
union all select 'ren',N'餁' 
union all select 'reng',N'陾' 
union all select 'ri',N'馹' 
union all select 'rong',N'穃' 
union all select 'rou',N'嶿' 
union all select 'ru',N'擩' 
union all select 'ruan',N'礝' 
union all select 'rui',N'壡' 
union all select 'run',N'橍' --橍挼 
union all select 'ruo',N'鶸' 
union all select 'sa',N'栍' --櫒栍 
union all select 'sai',N'虄' --簺虄 
union all select 'san',N'閐' 
union all select 'sang',N'喪' 
union all select 'sao',N'髞' 
union all select 'se',N'飋' --裇聓 
union all select 'sen',N'篸' 
union all select 'seng',N'縇' --閪縇 
union all select 'sha',N'霎' 
union all select 'shai',N'曬' 
union all select 'shan',N'鱔' 
union all select 'shang',N'緔' 
union all select 'shao',N'潲' 
union all select 'she',N'欇' 
union all select 'shen',N'瘮' 
union all select 'sheng',N'賸' 
union all select 'shi',N'瓧' --鰘齛兙瓧 
union all select 'shou',N'鏉' 
union all select 'shu',N'虪' 
union all select 'shua',N'誜' 
union all select 'shuai',N'卛' 
union all select 'shuan',N'腨' 
union all select 'shuang',N'灀' 
union all select 'shui',N'睡' 
union all select 'shun',N'鬊' 
union all select 'shuo',N'鑠' 
union all select 'si',N'乺' --瀃螦乺 
union all select 'song',N'鎹' 
union all select 'sou',N'瘶' 
union all select 'su',N'鷫' 
union all select 'suan',N'算' 
union all select 'sui',N'鐩' 
union all select 'sun',N'潠' 
union all select 'suo',N'蜶' 
union all select 'ta',N'襨' --躢襨 
union all select 'tai',N'燤' 
union all select 'tan',N'賧' 
union all select 'tang',N'燙' 
union all select 'tao',N'畓' --討畓 
union all select 'te',N'蟘' 
union all select 'teng',N'朰' --霯唞朰 
union all select 'ti',N'趯' 
union all select 'tian',N'舚' 
union all select 'tiao',N'糶' 
union all select 'tie',N'餮' 
union all select 'ting',N'乭' --濎乭 
union all select 'tong',N'憅' 
union all select 'tou',N'透' 
union all select 'tu',N'鵵' 
union all select 'tuan',N'褖' 
union all select 'tui',N'駾' 
union all select 'tun',N'坉' 
union all select 'tuo',N'籜' 
union all select 'wa',N'韤' 
union all select 'wai',N'顡' 
union all select 'wan',N'贎' 
union all select 'wang',N'朢' 
union all select 'wei',N'躛' 
union all select 'wen',N'璺' 
union all select 'weng',N'齆' 
union all select 'wo',N'齷' 
union all select 'wu',N'鶩' 
union all select 'xi',N'衋' 
union all select 'xia',N'鏬' 
union all select 'xian',N'鼸' 
union all select 'xiang',N'鱌' 
union all select 'xiao',N'斆' 
union all select 'xie',N'躞' 
union all select 'xin',N'釁' 
union all select 'xing',N'臖' 
union all select 'xiong',N'敻' 
union all select 'xiu',N'齅' 
union all select 'xu',N'蓿' 
union all select 'xuan',N'贙' 
union all select 'xue',N'瀥' 
union all select 'xun',N'鑂' 
union all select 'ya',N'齾' 
union all select 'yan',N'灩' 
union all select 'yang',N'樣' 
union all select 'yao',N'鑰' 
union all select 'ye',N'岃' --鸈膶岃 
union all select 'yi',N'齸' 
union all select 'yin',N'檼' 
union all select 'ying',N'譍' 
union all select 'yo',N'喲' 
union all select 'yong',N'醟' 
union all select 'you',N'鼬' 
union all select 'yu',N'爩' 
union all select 'yuan',N'願' 
union all select 'yue',N'鸙' 
union all select 'yun',N'韻' 
union all select 'za',N'雥' 
union all select 'zai',N'縡' 
union all select 'zan',N'饡' 
union all select 'zang',N'臟' 
union all select 'zao',N'竈' 
union all select 'ze',N'稄' 
union all select 'zei',N'鱡' 
union all select 'zen',N'囎' 
union all select 'zeng',N'贈' 
union all select 'zha',N'醡' 
union all select 'zhai',N'瘵' 
union all select 'zhan',N'驏' 
union all select 'zhang',N'瞕' 
union all select 'zhao',N'羄' 
union all select 'zhe',N'鷓' 
union all select 'zhen',N'黮' 
union all select 'zheng',N'證' 
union all select 'zhi',N'豒' 
union all select 'zhong',N'諥' 
union all select 'zhou',N'驟' 
union all select 'zhu',N'鑄' 
union all select 'zhua',N'爪' 
union all select 'zhuai',N'跩' 
union all select 'zhuan',N'籑' 
union all select 'zhuang',N'戅' 
union all select 'zhui',N'鑆' 
union all select 'zhun',N'稕' 
union all select 'zhuo',N'籱' 
union all select 'zi',N'漬' --漬唨 
union all select 'zong',N'縱' 
union all select 'zou',N'媰' 
union all select 'zu',N'謯' 
union all select 'zuan',N'攥' 
union all select 'zui',N'欈' 
union all select 'zun',N'銌' 
union all select 'zuo',N'咗') t 
where word >= @word collate Chinese_PRC_CS_AS_KS_WS 
order by word ASC) else @word end) 
set @i = @i + 1 
end 
return @pinyin 
END

GO



  Create  function  dbo.Fun_GetPy(@str  nvarchar(4000))  
  returns  nvarchar(4000)  
  --用于加密
  --WITH  ENCRYPTION  
  as  
  begin  
  declare  @intLen int  
  declare  @strRet nvarchar(4000)  
  declare  @temp  nvarchar(100)  
  set  @intLen  =  len(@str)  
  set  @strRet  =  ''  
  while  @intLen  >  0  
  begin  
  set  @temp  =  ''  
  select  @temp  =  case    
  when  substring(@str,@intLen,1)  >=  '帀'  then  'Z'  
  when  substring(@str,@intLen,1)  >=  '丫'  then  'Y'  
  when  substring(@str,@intLen,1)  >=  '夕'  then  'X'  
  when  substring(@str,@intLen,1)  >=  '屲'  then  'W'  
  when  substring(@str,@intLen,1)  >=  '他'  then  'T'  
  when  substring(@str,@intLen,1)  >=  '仨'  then  'S'  
  when  substring(@str,@intLen,1)  >=  '呥'  then  'R'  
  when  substring(@str,@intLen,1)  >=  '七'  then  'Q'  
  when  substring(@str,@intLen,1)  >=  '妑'  then  'P'  
  when  substring(@str,@intLen,1)  >=  '噢'  then  'O'  
  when  substring(@str,@intLen,1)  >=  '拏'  then  'N'  
  when  substring(@str,@intLen,1)  >=  '嘸'  then  'M'  
  when  substring(@str,@intLen,1)  >=  '垃'  then  'L'  
  when  substring(@str,@intLen,1)  >=  '咔'  then  'K'  
  when  substring(@str,@intLen,1)  >=  '丌'  then    'J'  
  when  substring(@str,@intLen,1)  >=  '铪'  then  'H'  
  when  substring(@str,@intLen,1)  >=  '旮'  then  'G'  
  when  substring(@str,@intLen,1)  >=  '发'  then  'F'  
  when  substring(@str,@intLen,1)  >=  '妸'  then  'E'  
  when  substring(@str,@intLen,1)  >=  '咑'  then  'D'  
  when  substring(@str,@intLen,1)  >=  '嚓'  then  'C'  
  when  substring(@str,@intLen,1)  >=  '八'  then  'B'  
  when  substring(@str,@intLen,1)  >=  '吖'  then  'A'  
  else  rtrim(ltrim(substring(@str,@intLen,1)))  
  end  
  --对于汉字特殊字符，不生成拼音码  
  if  (ascii(@temp)>127)  set  @temp  =  ''  
  --对于英文中小括号，不生成拼音码  
  if  @temp  =  '('  or  @temp  =  ')'  set  @temp  =  ''  
  select  @strRet  =  @temp  +  @strRet  
  set  @intLen  =  @intLen  -  1  
  end  
  return  lower(@strRet)  
  end
  go
  
  
  --创系统类别表唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tb_ErpSystemCategory_Sc_ClassCode] ON [dbo].[tb_ErpSystemCategory] 
(
	[Sc_ClassCode] ASC
)

go


IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_SMSTemplates]') AND type in (N'U'))
begin
--创建短信模板表
CREATE TABLE [dbo].[tb_SMSTemplates](
	[id] [int] PRIMARY KEY  IDENTITY(1,1) NOT NULL,
	--信息发送通道 0 为短信通道发送 1 为微信通道发送
	[ST_SendChannel] int not NULL,
	--短信模板类型   0 为人工短信  1 流程短信  2 事件短信  
	[ST_Type] int not NULL,
	--短信模板类别   0 儿童摄影客户   1 为其它摄影客户(如婚纱客户、写真客户、积分和财务短信等)
	[ST_Category] [int] not NULL,
	--短信发送事件
	[ST_SendEvent] [int] not NULL,
	--事件值 如：X分钟的分钟数 或积分 X的积分分值  X天  X岁 X日期/时间 
	[ST_SendEventValue] [nvarchar](50) not NULL,
	--短信接收对象  AllC 所有客户 AllE所有员工  或 X手机号(最多500个手机号)
	[ST_ReceiveObjects] [varchar](8000) not NULL,
	--短信模板标题
	[ST_Title] [nvarchar](200) not NULL,
	--短信模板内容 (建义不要超过270个字符长度)
	[ST_SMSContent] [nvarchar](500) not NULL,
	--是否启用模板 0 为禁用  1 为启用 
	[ST_IsEnabled] int  not NULL,
	--备注说明  少于800 字
	[ST_Remark] [nvarchar](800)  NULL,	
	--添加时间
	[ST_CreateDatetime] [datetime] not NULL,
	--修改时间
	[ST_UpdateDatetime] [datetime] not NULL
) 

--索引
CREATE NONCLUSTERED INDEX [IX_tb_SMSTemplates_SendEvent] ON [dbo].[tb_SMSTemplates] 
(
	[ST_SendEvent] ASC
)


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息发送通道 0 为短信通道发送 1 为微信通道发送' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_SendChannel'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板类型   0 为人工短信  1 流程短信  2 事件短信 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_Type'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板类别   0 儿童摄影客户   1 为其它摄影客户(如婚纱客户、写真客户、积分和财务短信等)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_Category'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送事件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_SendEvent'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'事件值 如：X分钟的分钟数 或积分 X的积分分值  X天  X岁 X日期/时间 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_SendEventValue'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信接收对象  AllC 所有客户 AllE所有员工  或 X手机号(最多500个手机号)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_ReceiveObjects'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板标题' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_Title'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板内容 (建义不要超过270个字符长度)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_SMSContent'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用模板 0 为禁用  1 为启用 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_IsEnabled'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注说明  少于800 字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_Remark'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_CreateDatetime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSTemplates', @level2type=N'COLUMN',@level2name=N'ST_UpdateDatetime'

end

go

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_SMSRecords]') AND type in (N'U'))
begin
--创建短信发送记录表
CREATE TABLE [dbo].[tb_SMSRecords](
	[id] [int] PRIMARY KEY  IDENTITY(1,1) NOT NULL,
	--信息发送通道 0 为短信通道发送 1 为微信通道发送
	[SR_SendChannel] int not NULL,
	--短信模板类型   0 为人工短信  1 流程短信  2 事件短信  
	[SR_Type] int not NULL,
	--短信发送事件
	[SR_SendEvent] [int] not NULL,
	--短信生成标识 （生成短信记录时的唯一标识） 避免短信成复生成 
	[SR_SMSGenerateIdentity] [nvarchar](50) not NULL,
	--短信发送后获取服务器返回的发送短信唯一标识(用于查询短信发送情况) 短信记录在服务器上只保存1个月
	[SR_SMSSendIdentity] [nvarchar](50) not NULL,
	--接收人手机号码，多个时用“,”号分隔
	[SR_RecipientNumber] [nvarchar](4000) not NULL,
	--短信内容 (建义不要超过270个字符长度)
	[SR_Content] [nvarchar](500) not NULL,
	--短信预设开始发送时间
	[SR_StartSendDatetime] [datetime]  not NULL,
	--短信发送条数
	[SR_SendSumCount] int not NULL,
	--发送状态  -1 为发送失败 0 为等待发送 1 为发送成功 2 为发送中 3 为超时短信
	[SR_Status] int not NULL,
	--发送日志
	[SR_SendLogs] [nvarchar](max)  NULL,	
	--发送报告
	[SR_SendReport] [nvarchar](800)  NULL,	
	--短信生成时间
	[SR_CreateDatetime] [datetime] not NULL,
	--短信发送时间 短信实际发送时间
	[SR_SendDatetime] [datetime]  NULL,
	--短信发送人
	[SR_SMSSendSender] [nvarchar](50) not NULL,
) 

  --创建表唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_tb_SMSRecords_SMSGenerateIdentity] ON [dbo].[tb_SMSRecords] 
(
	[SR_SMSGenerateIdentity] ASC
)
  --创建表非唯一索引
CREATE NONCLUSTERED INDEX [IX_tb_SMSRecords_SMSSendIdentity] ON [dbo].[tb_SMSRecords] 
(
	[SR_SMSSendIdentity] ASC
)

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息发送通道 0 为短信通道发送 1 为微信通道发送' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendChannel'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信模板类型   0 为人工短信  1 流程短信  2 事件短信 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_Type'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送事件' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendEvent'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信生成标识 （生成短信记录时的唯一标识） 避免短信成复生成 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SMSGenerateIdentity'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送后获取服务器返回的发送短信唯一标识(用于查询短信发送情况) 短信记录在服务器上只保存1个月 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SMSSendIdentity'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收人手机号码，多个时用“,”号分隔' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_RecipientNumber'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信内容 (建义不要超过270个字符长度)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_Content'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信预设开始发送时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_StartSendDatetime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送条数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendSumCount'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送状态  -1 为发送失败 0 为等待发送 1 为发送成功 2 为发送中 3 为超时短信' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_Status'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送日志' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendLogs'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送报告' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendReport'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信生成时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_CreateDatetime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信发送时间 短信实际发送时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_SMSRecords', @level2type=N'COLUMN',@level2name=N'SR_SendDatetime'

end

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_tb_Version]') AND type in (N'U'))
begin
--软件版本表
CREATE TABLE [dbo].[tb_Version](
	[id] [int]  IDENTITY(1,1) NOT NULL,
	--版本号
	[TV_Version] [nvarchar](50) not NULL,
	--控制命
	[TV_ControlCommand] [nvarchar](50) NULL,
	[TV_ValidDateRun] [datetime]  NULL,
	[TV_CreateDatetime] [datetime] not NULL,
	[TV_UpdateDatetime] [datetime] not NULL,
) 
end