update T_WORKERQUEUEPARAMETER set F_VALUE = '[工号]技师在[房间]有预约' WHERE F_ID = 'Destine';
GO
update T_WORKERQUEUEPARAMETER set F_VALUE = '[工号]技师在[房间]预约已取消' WHERE F_ID = 'DestineCancel'
GO

IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_CARDTYPE') and Name='F_ISROOMCOSTFREE')
BEGIN  
        ALTER TABLE T_CARDTYPE ADD F_ISROOMCOSTFREE SMALLINT DEFAULT 0 
END
go
update T_CARDTYPE set F_ISROOMCOSTFREE = 0 where F_ISROOMCOSTFREE is null;
go



--项目加钟规则 linx
IF OBJECT_ID (N'T_GOODSADDCLOCKRULE') IS NULL
BEGIN  --如果不存在该表，则进行创建

CREATE TABLE [dbo].[T_GOODSADDCLOCKRULE](
	[F_ID] [varchar](10) NOT NULL,
	[F_SHOPID] [varchar](10) NULL,
	[F_PREGOODSID] [varchar](30) NULL,
	[F_CURGOODSID] [varchar](30) NULL,
	[F_NOTASNAMED] [smallint] NULL,
 CONSTRAINT [PK_T_GOODSADDCLOCKRULE] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
END
GO

--waijian
IF NOT EXISTS (select * from sysobjects WHERE NAME='FK_GOODSADDCLOCK_T_SHOP')
  alter table T_GOODSADDCLOCKRULE add constraint [FK_GOODSADDCLOCK_T_SHOP] foreign key (F_SHOPID) references T_SHOP (F_ID)
  ON UPDATE CASCADE ON DELETE CASCADE;

GO

--触发器

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[GOODSADDCLOCKRULE_ADD]'))
DROP TRIGGER [dbo].[GOODSADDCLOCKRULE_ADD]
GO

CREATE TRIGGER [dbo].[GOODSADDCLOCKRULE_ADD] ON [dbo].T_GOODSADDCLOCKRULE
FOR INSERT 
AS
BEGIN
  DECLARE @FNEWID VARCHAR(20);
  SELECT @FNEWID = F_ID FROM inserted;
  DECLARE @FNEWSHOPID VARCHAR(20);
  SELECT @FNEWSHOPID = f_shopid FROM inserted;  
  EXECUTE UPDATETABLEINDEX 'T_GOODSADDCLOCKRULE','F_ID', @FNEWID, NULL,'ADD',@FNEWSHOPID,NULL;
END

GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[GOODSADDCLOCKRULE_DELETE]'))
DROP TRIGGER [dbo].[GOODSADDCLOCKRULE_DELETE]
GO

CREATE TRIGGER [dbo].[GOODSADDCLOCKRULE_DELETE] ON [dbo].[T_GOODSADDCLOCKRULE]
FOR DELETE 
AS
BEGIN
  DECLARE @FOLDID VARCHAR(20);
  SELECT @FOLDID = F_ID FROM deleted;
  DECLARE @FOLDSHOPID VARCHAR(20);
  SELECT @FOLDSHOPID = f_shopid FROM deleted;  
  EXECUTE UPDATETABLEINDEX 'T_GOODSADDCLOCKRULE','F_ID', @FOLDID, NULL,'DELETE',@FOLDSHOPID,NULL;
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[GOODSADDCLOCKRULE_UPDATE]'))
DROP TRIGGER [dbo].[GOODSADDCLOCKRULE_UPDATE]
GO

CREATE TRIGGER [dbo].[GOODSADDCLOCKRULE_UPDATE] ON [dbo].[T_GOODSADDCLOCKRULE]
FOR UPDATE
AS
BEGIN
  DECLARE @FNEWID VARCHAR(20);
  DECLARE @FOLDID VARCHAR(20);
  SELECT @FNEWID = F_ID FROM inserted;
  SELECT @FOLDID = F_ID FROM deleted;
  DECLARE @FOLDSHOPID VARCHAR(20);
  SELECT @FOLDSHOPID = f_shopid FROM deleted;  
  DECLARE @FNEWSHOPID VARCHAR(20);
  SELECT @FNEWSHOPID = f_shopid FROM inserted;  
  EXECUTE UPDATETABLEINDEX 'T_GOODSADDCLOCKRULE','F_ID', @FNEWID,@FOLDID,'UPDATE',@FNEWSHOPID, @FOLDSHOPID;
END

GO



if not exists(select * from T_SYN_BASETABLE where F_TABLENAME = 'T_GOODSADDCLOCKRULE') 
BEGIN
  insert into T_SYN_BASETABLE values('T_GOODSADDCLOCKRULE', null, null, 100, null, -1, 0);
END
GO


if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableGoodsAddClockRule') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableGoodsAddClockRule', '0', NULL, '是否启用项目加钟股则设置', 0, '其他','17.2.102.1504','2016-10-29');
END

GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_WORKERNOQUEUE]'))
DROP VIEW [dbo].[V_WORKERNOQUEUE]
GO

CREATE VIEW [dbo].[V_WORKERNOQUEUE] AS 
SELECT
	t_worker.F_ARCHIVESID F_WORKERID,
	t_worker.f_name F_WORKERNAME,
	t_worker.F_SEX,
	t_worker.f_headshipflag,
	(
		CASE
		WHEN t1.F_FLAG >= 1 THEN
			2
		ELSE
			(
				CASE
				WHEN t_worker.F_STATE = '正常' THEN
					1
				ELSE
					3
				END
			)
		END
	) AS F_WORKERSTATE,
	0 AS F_QUEUESTATE,
	t_worker.F_SHOPID,
	0 AS F_CURRENTINDEX,
	t_worker.F_HEADSHIPID,
	t_headship.f_name AS F_HEADSHIPNAME,
	'' AS f_scheduleid,
	0 AS F_TRUNTIMES,
	0 AS F_ISOVERTIME,
	0 AS F_ORIGINALINDEX,
	0 AS F_NEXTINDEX,
	CAST (NULL AS DATE) f_lastdatetime,
	0 AS F_GlomSecond,
  T_WORKER.F_HEADSHIPTRAIT,
	T_WORKER.F_ID AS F_ShowWORKERID,
	0 AS F_ISBREAKTIME
FROM
	(
		SELECT DISTINCT
			F_WORKERID,
			SUM (F_FLAG) AS F_FLAG
		FROM
			(
				SELECT
					t_worker.f_id F_WORKERID,
					0 AS F_FLAG
				FROM
					t_worker
				WHERE
					t_worker.F_STATE <> '离职'
				UNION ALL
					SELECT DISTINCT
						F_WORKERID,
						1 AS F_FLAG
					FROM
						T_consumeworker,
						T_RECEIVE
					WHERE
						T_consumeworker.f_receiveid = T_RECEIVE.f_id
					AND T_RECEIVE.f_checkoutid IS NULL
			) t2
		GROUP BY
			t2.F_WORKERID
	) t1
INNER JOIN t_worker ON (
	t1.F_WORKERID = t_worker.F_ARCHIVESID
	AND t_worker.F_STATE <> '离职'
)
INNER JOIN t_headship ON (
	t_worker.F_HEADSHIPID = t_headship.f_id
	AND t_headship.F_ISQUEUE = 1
);


GO




--项目自定义分类
IF OBJECT_ID (N'T_GOODSCUSTOMTYPE') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_GOODSCUSTOMTYPE](
	[F_ID] [varchar](10) NOT NULL,
	[F_NAME] [varchar](50) NULL,
	[F_SHOPID] [varchar](10) NULL,
	[F_REMARK] [varchar](100) NULL,
 CONSTRAINT [PK_T_GOODSCUSTOMTYPE] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[T_GOODSCUSTOMTYPE]  WITH CHECK ADD  CONSTRAINT [FK_T_GOODSCUSTOMTYPE_T_SHOP] FOREIGN KEY([F_SHOPID])
REFERENCES [dbo].[T_SHOP] ([F_ID])

ALTER TABLE [dbo].[T_GOODSCUSTOMTYPE] CHECK CONSTRAINT [FK_T_GOODSCUSTOMTYPE_T_SHOP]
END

GO

IF OBJECT_ID (N'T_GOODSCUSTOMTYPEDETAIL') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_GOODSCUSTOMTYPEDETAIL](
	[F_ID] [varchar](10) NOT NULL,
	[F_CUSTOMTYPEID] [varchar](10) NULL,
	[F_GOODSID] [varchar](30) NULL,
 CONSTRAINT [PK_T_GOODSCUSTOMTYPEDETAIL] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[T_GOODSCUSTOMTYPEDETAIL]  WITH CHECK ADD  CONSTRAINT [FK_T_GOODSCUSTOMTYPEDETAIL_T_GOODS] FOREIGN KEY([F_GOODSID])
REFERENCES [dbo].[T_GOODS] ([F_ID])

ALTER TABLE [dbo].[T_GOODSCUSTOMTYPEDETAIL] CHECK CONSTRAINT [FK_T_GOODSCUSTOMTYPEDETAIL_T_GOODS]
END
GO



if not exists(select * from T_OPERATIONS where F_ID = '318') 
BEGIN
  INSERT INTO T_OPERATIONS VALUES(318,'项目加钟规则设置', 0, 1, 0, 'MainForm','pmGoods')
END
GO

if not exists(select * from T_OPERATIONS where F_ID = '319') 
BEGIN
INSERT INTO T_OPERATIONS VALUES(319,'项目自定义分类', 0, 1, 0, 'MainForm','pmGoods')
END
GO

if not exists(select * from T_OPERATIONS where F_ID = '320') 
BEGIN
INSERT INTO T_OPERATIONS VALUES(320,'员工钟数明细核对', 204, 1, 1, 'MainForm','mmMain')
END

GO