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,
	--����̶� �е����� �������� �dz��������
	[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'����̶� �е����� �������� �dz��������' , @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'�a' 
union all select 'an',N'��' 
union all select 'ang',N'�l' 
union all select 'ao',N'�' 
union all select 'ba',N'��' 
union all select 'bai',N'�B' --�v�ĮB 
union all select 'ban',N'��' 
union all select 'bang',N'�^' 
union all select 'bao',N'�t' 
union all select 'bei',N'��' 
union all select 'ben',N'ݙ' 
union all select 'beng',N'�a' 
union all select 'bi',N'��' 
union all select 'bian',N'׃' 
union all select 'biao',N'�B' 
union all select 'bie',N'��' 
union all select 'bin',N'�W' 
union all select 'bing',N'�h' 
union all select 'bo',N'�N' 
union all select 'bu',N'��' 
union all select 'ca',N'��' 
union all select 'cai',N'�k' --�n�k 
union all select 'can',N'�|' 
union all select 'cang',N'ى' 
union all select 'cao',N'��' 
union all select 'ce',N'�u' 
union all select 'cen',N'��' 
union all select 'ceng',N'�u' --�����K�e�u 
union all select 'cha',N'Ԍ' 
union all select 'chai',N'��' 
union all select 'chan',N'�' 
union all select 'chang',N'�o' 
union all select 'chao',N'�e' 
union all select 'che',N'��' 
union all select 'chen',N'׏' 
union all select 'cheng',N'��' 
union all select 'chi',N'�u' 
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'�E' 
union all select 'chuang',N'��' 
union all select 'chui',N'�q' 
union all select 'chun',N'��' 
union all select 'chuo',N'�W' 
union all select 'ci',N'��' --�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'�v' 
union all select 'cuo',N'�e' 
union all select 'da',N'�\' 
union all select 'dai',N'�^' 
union all select 'dan',N'��' 
union all select 'dang',N'�W' 
union all select 'dao',N'��' 
union all select 'de',N'��' 
union all select 'den',N'�Y' 
union all select 'deng',N'�' 
union all select 'di',N'�E' 
union all select 'dia',N'��' 
union all select 'dian',N'�' 
union all select 'diao',N'�S' 
union all select 'die',N'��' --���� 
union all select 'ding',N'�r' 
union all select 'diu',N'�A' 
union all select 'dong',N'�' 
union all select 'dou',N'�a' 
union all select 'du',N'�' 
union all select 'duan',N'��' --�f�� 
union all select 'dui',N'�m' 
union all select 'dun',N'�v' 
union all select 'duo',N'�z' 
union all select 'e',N'�{' 
union all select 'en',N'��' 
union all select 'eng',N'�E' 
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'�a' 
union all select 'feng',N'҅' 
union all select 'fo',N'��' 
union all select 'fou',N'�]' 
union all select 'fu',N'�g' --�v�g 
union all select 'ga',N'�p' 
union all select 'gai',N'�y' 
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'�o' 
union all select 'gen',N'�j' 
union all select 'geng',N'��' --���톯�ֆ� 
union all select 'gong',N'��' --���C���� 
union all select 'gou',N'ُ' 
union all select 'gu',N'�' 
union all select 'gua',N'ԟ' 
union all select 'guai',N'�s' 
union all select 'guan',N'�}' 
union all select 'guang',N'��' 
union all select 'gui',N'�i' 
union all select 'gun',N'֏' 
union all select 'guo',N'�B' 
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'�g' 
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'�c' 
union all select 'hu',N'�I' 
union all select 'hua',N'�s' 
union all select 'huai',N'�|' 
union all select 'huan',N'�d' 
union all select 'huang',N'�w' 
union all select 'hui',N'�' 
union all select 'hun',N'՟' 
union all select 'huo',N'��' 
union all select 'ji',N'�K' 
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'�T' 
union all select 'jin',N'��' 
union all select 'jing',N'��' 
union all select 'jiong',N'�W' 
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'�h' 
union all select 'ka',N'�l' 
union all select 'kai',N'�f' --�b�f 
union all select 'kan',N'��' 
union all select 'kang',N'�`' 
union all select 'kao',N'��' 
union all select 'ke',N'�S' 
union all select 'ken',N'�y' 
union all select 'keng',N'�H' --�|�g�{���] 
union all select 'kong',N'�W' 
union all select 'kou',N'�d' 
union all select 'ku',N'��' 
union all select 'kua',N'�g' 
union all select 'kuai',N'�d' 
union all select 'kuan',N'�U' 
union all select 'kuang',N'�k' 
union all select 'kui',N'�^' 
union all select 'kun',N'��' 
union all select 'kuo',N'�i' 
union all select 'la',N'�B' 
union all select 'lai',N'�[' 
union all select 'lan',N'�h' 
union all select 'lang',N'�}' 
union all select 'lao',N'�~' 
union all select 'le',N'�E' 
union all select 'lei',N'Ú' --��Ú 
union all select 'leng',N'��' 
union all select 'li',N'��' 
union all select 'lia',N'�z' 
union all select 'lian',N'�~' 
union all select 'liang',N'�y' 
union all select 'liao',N'�t' 
union all select 'lie',N'�v' 
union all select 'lin',N'�`' --�`�� 
union all select 'ling',N'��' 
union all select 'liu',N'��' --�F�M�޿� 
union all select 'long',N'�L' 
union all select 'lou',N'�U' 
union all select 'lu',N'�' 
union all select 'lv',N'�r' 
union all select 'luan',N'�y' 
union all select 'lue',N'�^' 
union all select 'lun',N'Փ' 
union all select 'luo',N'�w' 
union all select 'ma',N'��' 
union all select 'mai',N'�A' 
union all select 'man',N'�p' 
union all select 'mang',N'ϑ' 
union all select 'mao',N'�x' 
union all select 'me',N'�Z' --�Z�� 
union all select 'mei',N'��' 
union all select 'men',N'��' 
union all select 'meng',N'�D' --�W�_ 
union all select 'mi',N'�]' 
union all select 'mian',N'�I' 
union all select 'miao',N'�R' 
union all select 'mie',N'�x' --�x�� 
union all select 'min',N'��' 
union all select 'ming',N'Ԛ' 
union all select 'miu',N'և' 
union all select 'mo',N'��' --��i 
union all select 'mou',N'�E' --�E�w 
union all select 'mu',N'��' 
union all select 'na',N'��' 
union all select 'nai',N'�r' 
union all select 'nan',N'�R' 
union all select 'nang',N'�Q' 
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'��' --ǂ����G�� 
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'�P' 
union all select 'nou',N'�k' 
union all select 'nu',N'�x' 
union all select 'nv',N'��' 
union all select 'nue',N'��' 
union all select 'nuan',N'�\' --���Q�\�G 
union all select 'nuo',N'��' 
union all select 'o',N'�M' --�ĉ�j�M 
union all select 'ou',N'�a' 
union all select 'pa',N'В' 
union all select 'pai',N'�s' --�W�s 
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'��' --�����C�n�� 
union all select 'pi',N'�G' 
union all select 'pian',N'�_' 
union all select 'piao',N'�G' 
union all select 'pie',N'��' 
union all select 'pin',N'Ƹ' 
union all select 'ping',N'�O' 
union all select 'po',N'��' 
union all select 'pou',N'�R' --�͆R 
union all select 'pu',N'��' 
union all select 'qi',N'τ' 
union all select 'qia',N'��' 
union all select 'qian',N'�y' 
union all select 'qiang',N'��' --������ 
union all select 'qiao',N'�N' 
union all select 'qie',N'�]' 
union all select 'qin',N'�C' 
union all select 'qing',N'��' 
union all select 'qiong',N'��' 
union all select 'qiu',N'��' 
union all select 'qu',N'�Y' 
union all select 'quan',N'��' 
union all select 'que',N'�]' 
union all select 'qun',N'��' 
union all select 'ran',N'�L' 
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'�J' 
union all select 'ruan',N'�O' 
union all select 'rui',N'��' 
union all select 'run',N'��' --���� 
union all select 'ruo',N'�U' 
union all select 'sa',N'��' --���� 
union all select 'sai',N'̃' --��̃ 
union all select 'san',N'�d' 
union all select 'sang',N'��' 
union all select 'sao',N'�' 
union all select 'se',N'�o' --�S�{ 
union all select 'sen',N'�d' 
union all select 'seng',N'�L' --�~�L 
union all select 'sha',N'��' 
union all select 'shai',N'��' 
union all select 'shan',N'�X' 
union all select 'shang',N'�y' 
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'��' --�|�a���� 
union all select 'shou',N'�' 
union all select 'shu',N'̠' 
union all select 'shua',N'�X' 
union all select 'shuai',N'�i' 
union all select 'shuan',N'�Y' 
union all select 'shuang',N'��' 
union all select 'shui',N'˯' 
union all select 'shun',N'�B' 
union all select 'shuo',N'�p' 
union all select 'si',N'�r' --�[�A�r 
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'�R' 
union all select 'ta',N'�k' --�c�k 
union all select 'tai',N'�M' 
union all select 'tan',N'�y' 
union all select 'tang',N'�C' 
union all select 'tao',N'�z' --ӑ�z 
union all select 'te',N'�c' 
union all select 'teng',N'�Y' --�L�z�Y 
union all select 'ti',N'ڌ' 
union all select 'tian',N'�q' 
union all select 'tiao',N'�g' 
union all select 'tie',N'��' 
union all select 'ting',N'�h' --��h 
union all select 'tong',N'�q' 
union all select 'tou',N'͸' 
union all select 'tu',N'�r' 
union all select 'tuan',N'щ' 
union all select 'tui',N'�D' 
union all select 'tun',N'�d' 
union all select 'tuo',N'�X' 
union all select 'wa',N'�' 
union all select 'wai',N'�' 
union all select 'wan',N'�@' 
union all select 'wang',N'�R' 
union all select 'wei',N'�^' 
union all select 'wen',N'�' 
union all select 'weng',N'�N' 
union all select 'wo',N'�}' 
union all select 'wu',N'�F' 
union all select 'xi',N'�a' 
union all select 'xia',N'�]' 
union all select 'xian',N'�E' 
union all select 'xiang',N'�P' 
union all select 'xiao',N'��' 
union all select 'xie',N'��' 
union all select 'xin',N'�' 
union all select 'xing',N'�B' 
union all select 'xiong',N'��' 
union all select 'xiu',N'�M' 
union all select 'xu',N'ޣ' 
union all select 'xuan',N'�K' 
union all select 'xue',N'�y' 
union all select 'xun',N'�R' 
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'��' --�E�� 
union all select 'yi',N'�~' 
union all select 'yin',N'��' 
union all select 'ying',N'�G' 
union all select 'yo',N'��' 
union all select 'yong',N'�k' 
union all select 'you',N'��' 
union all select 'yu',N'��' 
union all select 'yuan',N'�' 
union all select 'yue',N'�V' 
union all select 'yun',N'�' 
union all select 'za',N'�{' 
union all select 'zai',N'�f' 
union all select 'zan',N'�' 
union all select 'zang',N'�K' 
union all select 'zao',N'�^' 
union all select 'ze',N'��' 
union all select 'zei',N'�e' 
union all select 'zen',N'��' 
union all select 'zeng',N'ٛ' 
union all select 'zha',N'�m' 
union all select 'zhai',N'�' 
union all select 'zhan',N'�' 
union all select 'zhang',N'�d' 
union all select 'zhao',N'�^' 
union all select 'zhe',N'�p' 
union all select 'zhen',N'�l' 
union all select 'zheng',N'�C' 
union all select 'zhi',N'�U' 
union all select 'zhong',N'�A' 
union all select 'zhou',N'�E' 
union all select 'zhu',N'�T' 
union all select 'zhua',N'צ' 
union all select 'zhuai',N'�J' 
union all select 'zhuan',N'�M' 
union all select 'zhuang',N'��' 
union all select 'zhui',N'�V' 
union all select 'zhun',N'��' 
union all select 'zhuo',N'�m' 
union all select 'zi',N'�n' --�n�� 
union all select 'zong',N'�v' 
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)  >=  '�r'  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