IF OBJECT_ID (N'T_GoodsPlus') IS NULL
begin
CREATE TABLE [dbo].[T_GOODSPLUS](
	[F_ID] [varchar](20) NOT NULL,
	[F_GOODSID] [varchar](30) not NULL,
	[F_LEVELID] [int] not NULL,
	[F_HEADSHIPID] [varchar](10) not NULL,
	[F_GOODSPLUSID] [varchar](30) NULL,
	[F_SHOPID] [varchar](10) not NULL
	
 CONSTRAINT [PK_T_GoodsPlus] 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]
) 
END
GO


IF NOT EXISTS (select * from sysobjects WHERE NAME='FK_项目附加外键职务编号')
  alter table T_GoodsPlus add constraint [FK_项目附加外键职务编号] foreign key (F_HEADSHIPID) references T_HEADSHIP (F_ID)
  ON UPDATE CASCADE ON DELETE CASCADE;

GO


IF NOT EXISTS (select * from sysobjects WHERE NAME='FK_项目附加外键店号')
  alter table T_GoodsPlus add constraint [FK_项目附加外键店号] 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].[GOODSPLUS_ADD]'))
DROP TRIGGER [dbo].[GOODSPLUS_ADD]
GO

CREATE TRIGGER [dbo].[GOODSPLUS_ADD] ON [dbo].T_GOODSPLUS
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_GOODSPLUS','F_ID', @FNEWID, NULL,'ADD',@FNEWSHOPID,NULL;
END

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

CREATE TRIGGER [dbo].[GOODSPLUS_DELETE] ON [dbo].[T_GOODSPLUS]
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_GOODSPLUS','F_ID', @FOLDID, NULL,'DELETE',@FOLDSHOPID,NULL;
END
GO

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

CREATE TRIGGER [dbo].[GOODSPLUS_UPDATE] ON [dbo].[T_GOODSPLUS]
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_GOODSPLUS','F_ID', @FNEWID,@FOLDID,'UPDATE',@FNEWSHOPID, @FOLDSHOPID;
END

GO


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




IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_GoodsPlus')
BEGIN   
  DROP View V_GoodsPlus;
END
GO
CREATE VIEW [dbo].[V_GoodsPlus](
    "F_ID",
    "F_GoodsID",
    "F_GoodsName",
    "F_LevelID",
    "F_LevelName",
    "F_HEADSHIPID",
    "F_HEADSHIPName",
    "F_GOODSPLUSID",
    "F_GOODSPLUSNAME",
    "F_GOODSPLUSPRICE",
    "F_SHOPID")
AS
SELECT 
T_GOODSPLUS.F_ID, 
T_GOODSPLUS.F_GoodsID,
T_GOODS.F_NAME as F_GoodsName,
T_GOODSPLUS.F_LevelID,
T_HEADSHIPTRAIT.F_NAME as "F_LevelName",
T_GOODSPLUS.F_HEADSHIPID,
T_HEADSHIP.F_NAME as F_HEADSHIPNAME,
T_GOODSPLUS.F_GOODSPLUSID,
T_goods1.F_NAME as F_GOODSPLUSNAME,
T_goods1.F_PRICE as F_GOODSPLUSPRICE,
T_GOODSPLUS.F_SHOPID
FROM T_GOODSPLUS
   inner JOIN T_GOODS ON (T_GOODSPLUS.F_GOODSID = T_GOODS.F_ID)
   inner Join T_HEADSHIP ON (T_GOODSPLUS.F_HEADSHIPID = T_HEADSHIP.F_ID)
   inner JOIN T_HEADSHIPTRAIT ON (T_GOODSPLUS.F_LEVELID = T_HEADSHIPTRAIT.F_ID)
   left JOIN T_GOODS as T_goods1 ON (T_GOODSPLUS.F_GOODSPLUSID = T_goods1.F_ID)

GO


IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_CONSUME') and Name='F_GOODSPLUSPARENTID')
BEGIN  
        ALTER TABLE T_CONSUME ADD F_GOODSPLUSPARENTID VARCHAR(20);  
END

go

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsUseHeadShipPlus') 
BEGIN
INSERT INTO [T_WORKERQUEUEPARAMETER]
           ([F_ID]
           ,[F_VALUE]
           ,[F_SHOPID]
           ,[F_REMARK]
           ,[F_ISHIDE]
           ,[F_TYPE],
            F_VERSION,
            F_ADDDATE)
     VALUES
           ('IsUseHeadShipPlus'
           ,'0'
           ,null
           ,'是否启用项目等级附加费'
           ,0
           ,'基础设置'
           ,'17.2.100'
           ,'2016-10-04');

END

go
if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsAutoGenerateHeadShipLevelSurcharge') 
BEGIN
INSERT INTO [T_WORKERQUEUEPARAMETER]
           ([F_ID]
           ,[F_VALUE]
           ,[F_SHOPID]
           ,[F_REMARK]
           ,[F_ISHIDE]
           ,[F_TYPE],
            F_VERSION,
            F_ADDDATE)
     VALUES
           ('IsAutoGenerateHeadShipLevelSurcharge'
           ,'0'
           ,null
           ,'是否启用后台自动生成职务附加费'
           ,0
           ,'基础设置'
           ,'17.2.100'
           ,'2016-10-05');
END
GO


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

CREATE VIEW [dbo].[V_CONSUMENOTCHECKOUT] AS 
SELECT
	dbo.T_CONSUME.F_ID,
	dbo.T_CONSUME.F_RECEIVEID,
	dbo.T_CONSUME.F_GOODSID,
	dbo.T_CONSUME.F_PRICE,
	dbo.T_CONSUME.F_AMOUNT,
	dbo.T_CONSUME.F_ISPRESENT,
	dbo.T_CONSUME.F_CANREBATE,
	dbo.T_CONSUME.F_REBATE,
	dbo.T_CONSUMEWORKER.F_HEADSHIPID,
	dbo.T_HEADSHIP.F_NAME AS F_HEADSHIPNAME,
	dbo.T_CONSUMEWORKER.F_WORKERID,
	dbo.T_CONSUMEWORKER.F_ISNAMED,
	dbo.T_CONSUME.F_REMARK,
	dbo.T_CONSUME.F_USERID,
	dbo.T_CONSUME.F_COST,
	dbo.T_CONSUME.F_STATE,
	dbo.T_GOODS.F_NAME,
	dbo.T_GOODS.F_GOODSTYPEID,
	dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME,
	dbo.T_GOODS.F_GOODSTYPE1ID,
	dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME,
	dbo.T_GOODSTYPE1.F_STATE AS F_GOODSTYPE1STATE,
	dbo.T_GOODSTYPE1.F_ISSTORAGE,
	dbo.T_RECEIVE.F_DATETIME,
	dbo.T_RECEIVE.F_CHECKOUTID,
	dbo.T_RECEIVE.F_SHOPID,
	dbo.T_RECEIVE.F_HANDBRANDID,
	dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID,
	dbo.T_RECEIVE.F_ROOMID,
	dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID,
	dbo.T_RECEIVE.F_JOINID,
	dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT,
	dbo.T_RECEIVE.F_MALEAMOUNT,
	dbo.T_CONSUME.F_STARTDATETIME,
	dbo.T_CONSUME.F_ENDDATETIME,
	dbo.T_GOODS.F_STARTTIMES,
	dbo.T_GOODS.F_STARTTIMESPRICE,
	dbo.T_GOODS.F_TIMES,
	dbo.T_GOODS.F_TIMESPRICE,
	dbo.T_CONSUMEWORKER.F_ID AS F_CONSUMEWORKERID,
	dbo.T_CONSUME.F_ENTERDATETIME,
	(case when T_RECEIVE.F_MEMORANDUM IS not null then T_RECEIVE.F_MEMORANDUM  else dbo.T_RECEIVE.F_REMARK end) AS F_RECEIVEREMARK,
	dbo.T_CONSUMEWORKER.F_GOODSDEDUCTAUTOID,
	dbo.T_CONSUMEWORKER.F_OUTDATETIME,
	dbo.T_CONSUMEWORKER.F_BACKDATETIME,
	dbo.T_CONSUME.F_MINISTERID,
	dbo.T_GOODS.F_ISSMALL,
	dbo.T_CONSUMEWORKER.F_ISADDCLOCK,
	dbo.T_CONSUME.F_DELAY,
	dbo.T_CONSUME.F_DELAYID,
	dbo.T_RECEIVE.F_PRIORCHECKOUTID,
	dbo.T_CONSUME.F_ISPRIOR,
	dbo.T_CONSUMEWORKER.F_WALKTHROUGHINDEX,
	dbo.T_CONSUMEWORKER.F_ISSTOPWALKTHROUGH,
	dbo.T_CONSUMEWORKER.F_ENTERDATETIME AS F_WORKERENTERDATETIME,
	dbo.T_GOODS.F_HEADSHIPTRAIT AS F_GOODSHEADSHIPTRAIT,
	dbo.T_RECEIVE.F_ISSTOPAUTOTEA,
	dbo.T_WORKER.F_ID AS F_SHOWWORKERID,
	t1.F_ID AS F_SHOWMINISTERID,
	dbo.T_GOODS.F_ISEXTERNALSOUND,
        T_CONSUME.F_SATISFYCOMSUMEWORKERID,
        dbo.T_RECEIVE.F_CLIENTID,
        dbo.T_CONSUME.F_SEATNUMBER,
        dbo.T_CONSUME.F_HEADSHIPLEVEL,
        dbo.T_CONSUME.F_GOODSPLUSPARENTID
FROM
	dbo.T_RECEIVE
INNER JOIN dbo.T_CONSUME ON dbo.T_RECEIVE.F_ID = dbo.T_CONSUME.F_RECEIVEID
AND dbo.T_RECEIVE.F_CHECKOUTID IS NULL
LEFT OUTER JOIN dbo.T_CONSUMEWORKER ON dbo.T_CONSUME.F_ID = dbo.T_CONSUMEWORKER.F_CONSUMEID
LEFT OUTER JOIN dbo.T_HEADSHIP ON dbo.T_CONSUMEWORKER.F_HEADSHIPID = dbo.T_HEADSHIP.F_ID
LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID
LEFT OUTER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID
LEFT OUTER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID
LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID
LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID
LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUMEWORKER.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID
LEFT OUTER JOIN dbo.T_WORKER AS t1 ON dbo.T_CONSUME.F_MINISTERID = t1.F_ARCHIVESID

GO


--hj
IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_CARDTYPE') and Name='F_STARTDATETIME')
BEGIN  
	ALTER TABLE T_CARDTYPE ADD F_STARTDATETIME [datetime] NULL
	ALTER TABLE T_CARDTYPE ADD F_ENDDATETIME [datetime] NULL
END
GO