清空数据库2.0.sql

Coffice协同办公管理系统标准版的功能模块覆盖了所有企业级用户的业务应用。能够协助企业员工进行日常办公管理

truncate table TabMailAttachFiles;
truncate table TabMailList;
truncate table UDS_Files;
truncate table UDS_Document;
truncate table UDS_Schedule;
truncate table UDS_Task;
truncate table UDS_Task_Comment; 

--BBS初始化
truncate table UDS_BBS_Attachment; 
truncate table uds_bbs_board
truncate table uds_bbs_boardmaster
truncate table uds_bbs_boardmember
truncate table uds_bbs_catalog
truncate table uds_bbs_forumItem
truncate table UDS_BBS_Item_Attachment
truncate table uds_bbs_replay
truncate table UDS_BBS_Replay_Attachment
--联系人初始化
truncate table UDS_Linkman_CustomLinkman
truncate table UDS_Linkman_CustomLinkmanType
truncate table UDS_Linkman_LinkmanList
truncate table UDS_Linkman_TypeOfCustomLinkman
--客户管理初始化
truncate table UDS_CM_Attachment
truncate table UDS_CM_ClientContact_Linkman
delete from UDS_CM_Linkman
truncate table UDS_CM_ClientContact_Cooperatingman
delete from UDS_CM_ClientContact
truncate table UDS_CM_ClientInfo

truncate table UDS_WorkAttendence_Data

--初始化daysetting
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_WA_InitDutyTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_WA_InitDutyTime]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_WA_InitDutyTime
@begindate datetime,
@enddate datetime,
@rest	bit
/*------------------------------------
功能： 初始化考勤日期表
参数：
	@begindate	起始时间
	@enddate	结束时间
	@rest		是否将双休设置为不用上班
------------------------------------------*/
AS
truncate table UDS_WorkAttendence_DaySetting

DECLARE @date datetime
Set @date = @begindate

WHILE(@date<=@enddate)
BEGIN
	IF(@rest=1)
	BEGIN
		IF((datepart(dw,@date)=1) or (datepart(dw,@date)=7))
		BEGIN
			INSERT INTO UDS_WorkAttendence_DaySetting(datetime,needduty) VALUES(@date,0)
		END
		ELSE
		BEGIN
			INSERT INTO UDS_WorkAttendence_DaySetting(datetime,needduty) VALUES(@date,1)
		END
	END
	ELSE
		INSERT INTO UDS_WorkAttendence_DaySetting(datetime,needduty) VALUES(@date,1)

SET @date = dateadd(dd,1,@date)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




exec sp_WA_InitDutyTime '2000-1-1','2020-12-31',1
--插入一条总公司上班时间
truncate table UDS_WorkAttendence_DutyTime
INSERT INTO UDS_WorkAttendence_DutyTime(ID,IDType,OnDutyTime,OffDutyTime) VALUES(0,'Company','8:30:00','17:00:00')
truncate table UDS_WorkAttendence_Memo






--组
delete from uds_class where classid<>classparentid
delete from uds_staff_in_team
delete from uds_staff_in_role 
delete from uds_staff_in_position

insert into uds_staff_in_position values(3,1)
insert into uds_staff_in_role values(3,1)
insert into uds_staff_in_role values(3,12)


--职位
delete from uds_position where position_id <>1

--角色
delete from uds_role where role_id<>1 and role_id<>12

delete from uds_staff where staff_id <> 3


--权限表
delete from uds_assgn_rule where role_id <>1

--组信息
delete from uds_teaminfo

--流程模块
truncate table uds_flow
truncate table uds_flow_document
truncate table uds_flow_files
truncate table uds_flow_jump
truncate table UDS_Flow_Member_Bind
truncate table UDS_Flow_Message
truncate table UDS_Flow_Path
truncate table UDS_Flow_Postil
truncate table uds_flow_status
truncate table UDS_Flow_Step
truncate table uds_flow_style
truncate table uds_flow_style_data
truncate table uds_flow_style_description


--短消息模块
truncate table UDS_SMS_MobileMsgSendBuffer
truncate table UDS_SMS_MobileMsgSendHistory
truncate table UDS_SMS_Msg
truncate table UDS_SMS_Receiver

--邮件模块
delete FROM UDS_MailAttach