日程管理主表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CRM_Schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CRM_Schedule]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CRM_Schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CRM_Schedule] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeID] [varchar] (30) NULL ,
[Type] [varchar] (20) NULL ,--类型,增加的为ADD
[CreatorTypeID] [varchar] (30) NULL ,--创建者
[Date] [datetime] NULL ,--日程日期
[Subject] [varchar] (100) NULL ,--主题
[Content] [varchar] (2000) NULL ,--内容
[EvolveNote] [varchar] (2000) NULL ,
[CreateDate] [datetime] NULL ,
[IsAlert] [varchar] (20) NULL ,--Yes,提醒,No不提醒
[HaveSendAlert] [varchar] (20) NULL ,
[AlertDate] [datetime] NULL ,
[Cycle] [varchar] (20) NULL ,
[CycleDay] [int] NULL ,
[CycleTime] [datetime] NULL ,
[Status] [varchar] (20) NULL ,
[ExecutorTypeID] [varchar] (30) NULL ,
[PlanEndTime] [datetime] NOT NULL ,
[ActualEndTime] [datetime] NOT NULL ,
[ViewTypeIDs] [varchar] (2000) NOT NULL ,
[CruciformStatus] [varchar] (30) NOT NULL ,
[IsAlertToMobile] [varchar] (10) NOT NULL ,
[BeginDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Style] [varchar] (20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[CRM_Schedule] WITH NOCHECK ADD
CONSTRAINT [PK_CRM_Schedule] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
exec spCRM_ScheduleAdd 'Event', '0000000000', '03 9 2007 12:00:00:000AM', '20070309主题06:30分到12:30分,', '20070309主题06:30分到12:30分,团队日程,请在2007-03-09 20:30分提醒我', 'Yes', 'No', '03 9 2007 8:30:00:000PM', 'No', 0, '01 1 2002 12:00:01:000AM', '', '05 5 1818 12:00:00:000AM', '05 5 1818 12:00:00:000AM', '0000000000', '00', '', '03 7 2007 6:30:00:000AM', '03 7 2007 12:30:00:000PM', @P1 output
--增加日程
/*
新增一条日程（CRM_Schedule表）
*/
ALTER PROCEDURE spCRM_ScheduleAdd
(
@chvType VARCHAR(20), --类型
@chvCreatorTypeID VARCHAR(30), --创建者
@dtmDate DATETIME, --日程日期
@chvSubject VARCHAR(100), --主题
@chvContent VARCHAR(2000), --内容
@chvIsAlert VARCHAR(20), --是否提醒
@chvHaveSendAlert VARCHAR(20), --是否已发送提醒
@dtmAlertDate DATETIME, --提醒的具体日期时间
@chvCycle VARCHAR(20), --循环周期
@intCycleDay INT, --循环周期中的开始天数
@dtmCycleTime DATETIME, --循环周期中的具体时间
@chvStatus VARCHAR(20), --状态
@dtmPlanEndTime DATETIME, --计划完成时间
@dtmActualEndTime DATETIME, --实际完成时间
@chvExecutorTypeID VARCHAR(30), --负责人
@chvCruciform VARCHAR(30), --日程轻重缓急
@szIsAlertToMobile VARCHAR(10), --不在线时是否以手机短信提醒
@dtmBeginDate DATETIME, --开始时间
@dtmEndDate DATETIME, --结束时间
@chvTypeID VARCHAR(30) OUTPUT --返回新增日程的TypeID
)
AS
SET NOCOUNT ON
DECLARE @chvTempTypeID VARCHAR(30)
DECLARE @numTempTypeNumber NUMERIC(10,0)
--考虑纪录为空或找不到有TypeID的纪录的情况下，正确处理编号问题
IF EXISTS(SELECT * FROM CRM_Schedule WHERE NOT ([TypeID] IS NULL))
SELECT @numTempTypeNumber = (SELECT TOP 1 CONVERT(NUMERIC(10,0),[TypeID]) FROM CRM_Schedule ORDER BY [TypeID] DESC)+1
ELSE
SELECT @numTempTypeNumber = CONVERT(NUMERIC(10,0),'0000000001')
--将字符串中的空格转换成0 空格的ASCII码是32
SELECT @chvTempTypeID = REPLACE(STR(@numTempTypeNumber),CHAR(32),0)
INSERT INTO CRM_Schedule ([TypeID],[Type],[CreatorTypeID],[Date],[Subject],[Content],[CreateDate],[IsAlert],[HaveSendAlert],[AlertDate],[Cycle],[CycleDay],[CycleTime],[Status],[PlanEndTime],[ActualEndTime],[ExecutorTypeID],[CruciformStatus],[IsAlertToMobile],[BeginDate],[EndDate])
VALUES (
@chvTempTypeID,
@chvType,
@chvCreatorTypeID,
@dtmDate,
@chvSubject,
@chvContent,
GETDATE(),
@chvIsAlert,
@chvHaveSendAlert,
@dtmAlertDate,
@chvCycle,
@intCycleDay,
@dtmCycleTime,
@chvStatus,
@dtmPlanEndTime,
@dtmActualEndTime,
@chvExecutorTypeID,
@chvCruciform,
@szIsAlertToMobile,
@dtmBeginDate,
@dtmEndDate)
SELECT @chvTypeID = @chvTempTypeID