--数据库版本
Update sn_sys_parameter set vch_value = 'V4_120827' where vch_parameter = 'DataBase_Version'
go

/*==============================================================*/
/* Table: sn_u_uncheckout_log                                   */
/*==============================================================*/
if not exists (select 1 from  sysobjects where  id = object_id('sn_u_uncheckout_log') and   type = 'U')
	create table sn_u_uncheckout_log (
	   int_flowid           int                  identity,
	   ch_billno            char(12)             null,
	   ch_payno_un          char(12)             null,
	   num_cost_un          numeric(10,2)        null,
	   vch_operid           varchar(6)           null,
	   dt_operdate          datetime             null
	)
go

--20140724
if not exists (select * from  syscolumns where  id = object_id('sn_bt_roomsitem') and name = 'dt_operdate')
	alter table  sn_bt_roomsitem add  dt_operdate datetime null
go
if not exists (select * from  syscolumns where  id = object_id('sn_bt_roomsitem') and name = 'ch_stop1')
	alter table  sn_bt_roomsitem add  ch_stop1 varchar(1) null
go


if not exists (select * from syscolumns where id = object_id('sn_u_checkout_master') and name = 'vch_cancel')
  alter table sn_u_checkout_master add vch_cancel Varchar(6)
go

if not exists (select * from syscolumns where id = object_id('sn_u_checkout_master') and name = 'dt_cancel')
  alter table sn_u_checkout_master add dt_cancel Datetime
go


if not exists (select * from syscolumns where id = object_id('sn_u_costitem') and name = 'vch_team')
  alter table sn_u_costitem add vch_team Varchar(3)
go

--服务项目类型表，添加无需上钟标识, 买钟项目
if exists (select * from  syscolumns where  id = object_id('sn_bt_serveritem_type') and name = 'ch_noclock')
	alter table sn_bt_serveritem_type drop COLUMN ch_noclock
go

if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'ch_noclock')
    alter table sn_bt_serveritem add ch_noclock char(1) default 'N'
go

if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'ch_array')
    alter table sn_bt_serveritem add ch_array char(1) default 'N'
go

If not exists (Select * From syscolumns Where id = object_id('sn_bt_roomitem_types') and name = 'ch_auto_sell')
   alter Table sn_bt_roomitem_types add ch_auto_sell char(1) default 'N' 
go

If not exists (Select * From syscolumns Where id = object_id('sn_bt_handcard_type') and name = 'ch_auto_sell')
   alter Table sn_bt_handcard_type add ch_auto_sell char(1) default 'N' 
go

--增加议价属性
If not exists (Select * From syscolumns Where id = object_id('sn_bt_goodsitem') and name = 'ch_change_price')
   alter Table sn_bt_goodsitem add ch_change_price char(1) default 'N'
go

If not exists (Select * From syscolumns Where id = object_id('sn_bt_serveritem') and name = 'ch_change_price')
   alter Table sn_bt_serveritem add ch_change_price char(1) default 'N'
go

----临时会员积分表
if  exists (select 1 from  sysobjects where id = object_id('dbo.t_sys_point_temp') and type = 'U')
drop table t_sys_point_temp
go
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_sys_member_point') and type = 'U')
	create table dbo.t_sys_member_point (
   int_flowID           int                  identity,
   ch_billno         	char(15)        not null,
   ----ch_tableno           char(4)         not null,
   vch_memberno		varchar(20)	not null,
   ch_hqflag		char(1)		not null,
   vch_operid		varchar(6)	not null,
   dt_operdate          datetime        not null,
   constraint PK_t_sys_member_point primary key clustered (int_flowID))
go

--自动点项目表
if not exists (select 1 from  sysobjects where  id = object_id('sn_bt_auto_item') and   type = 'U')
create table sn_bt_auto_item (
   int_flowId           int                  identity,
   ch_typeno             char(3)              null,
   ch_hor               char(1)              null,
   ch_kind              char(1)              null,
   ch_itemno            char(6)              null,
   constraint PK_SN_BT_AUTO_ITEM primary key  (int_flowId)
)
go

--日结记录表
if not exists (select 1 from  sysobjects where  id = object_id('sn_u_day_sumup') and   type = 'U')
create table sn_u_day_sumup (
   dt_date              datetime             not null,
   dt_begin             datetime             not null,
   dt_end               datetime             not null,
   vch_reg              varchar(6)           null,
   dt_regdate           datetime             null,
   ch_upflag            char(1)              null,
   constraint PK_SN_U_DAY_SUMUP primary key  (dt_date)
)
go


if not exists (select 1
            from  sysobjects
           where  id = object_id('sn_u_room_change_log')
            and   type = 'U')
create table sn_u_room_change_log (
   int_flowId           int                  identity,
   ch_itemno            char(6)              null,
   ch_new_itemno        char(6)              null,
   ch_kind              char(1)              null,
   dt_start             datetime             null,
   dt_change            datetime             null,
   vch_reason           varchar(100)         null,
   ch_operId            char(6)              null,
   dt_operdate          datetime             null,
   constraint PK_SN_U_ROOM_CHANGE_LOG primary key  (int_flowId)
)
go

--本地挂帐会员结算情况表
if not exists (select 1 from  sysobjects where  id = object_id('t_m_hang_balance_detail') and type = 'U')
	create table t_m_hang_balance_detail (
	   ch_branchno 		char(4)		not null,
	   vch_memberno          char(20)       not null,
	   vch_member           char(40)        not null,
	   num_hangamount       decimal(10,2)   not null,
	   num_free	        decimal(10,2)   not null,
	   num_payamount        decimal(10,2)   not null,
       	   num_waitamount       decimal(10,2)   not null,
	   num_amount           decimal(10,2)   not null,
	   num_point            decimal(10,2)   not null,
	   dt_operdate          datetime        not null,
	   constraint PK_t_m_hang_balance_detail primary key  (ch_branchno, vch_memberno)
	)
go

if not exists (select 1 from sysobjects where id = object_id('sn_bt_handcard_floor') and type = 'U')
	create table sn_bt_handcard_floor (
		ch_floorno char(2) NOT NULL ,
		vch_floorname char(12) NOT NULL ,
		constraint pk_sn_bt_handcard_floor primary key (ch_floorno)
)
go

if not exists (select 1 from sysobjects where id = object_id('sn_bt_costtype') and type = 'U')
create table sn_bt_costtype (
	ch_costtypeno char(3) NOT NULL ,
	ch_coltype char(1) not null,
	vch_costtypename varchar(30) NOT NULL ,
	ch_deduct_mode char(1) NOT NULL DEFAULT ('1'),
	ch_manflag char(1) NULL DEFAULT ('Y'),
	ch_deptno char(2) NULL ,
	ch_printID char(2) NULL ,
	constraint pk_sn_bt_costtype primary key(ch_costtypeno,ch_coltype)
)
go

if not exists (select 1 from sysobjects where id = object_id('sn_bt_cp_project') and type = 'U')
	create table sn_bt_cp_project (
		ch_printID char(2) NOT NULL ,
		vch_name char(20) NOT NULL ,
		int_printer int NULL DEFAULT (0),
		vch_printer1 varchar(50) NULL ,
		vch_printer2 varchar(50) NULL ,
		vch_printer3 varchar(50) NULL ,
		constraint pk_sn_bt_cp_project primary key(ch_printID)
)
go

if not exists (select 1 from sysobjects where id = object_id('sn_bt_floor_cookprint') and type = 'U')
	create table sn_bt_floor_cookprint (
		ch_floorno char(2) NOT NULL ,
		ch_costtypeno char(3) NOT NULL ,
		ch_coltype char(1) not null,
		ch_printID char(2) NULL ,
		constraint pk_sn_bt_floor_cookprint primary key (ch_floorno,ch_costtypeno,ch_coltype)
)
go


if not exists(select * from syscolumns where id = object_id('sn_bt_handcard') and name = 'ch_floorno')
	alter table sn_bt_handcard add ch_floorno char(2) null
go

if not exists(select * from syscolumns where id = object_id('sn_bt_roomsitem') and name = 'ch_floorno')
	alter table sn_bt_roomsitem add ch_floorno char(2) null
go


ALTER  view v_bt_item as 
select ch_itemno as ch_itemno, vch_itemname, ch_unitno, vch_spell, ch_payno, num_first_price, num_next_price, 'A' as ch_kind, ch_stop, ch_discount, ch_typeno as ch_typeno,ch_saletype,num_sale
from sn_bt_serveritem
union
select ch_itemno as ch_itemno, vch_itemname, ch_unitno, vch_spell, '000' as ch_payno, num_price as num_first_price, 0 as num_next_price, 'B' as ch_kind, ch_stop, ch_discount, ch_typeno as ch_typeno,ch_saletype,num_sale
from sn_bt_goodsitem
union
select ch_itemno as ch_itemno, vch_itemname, '' as ch_unitno, '' as vch_spell, '' as ch_payno,0 as num_first_price, 0 as num_next_price, 'C' as ch_kind, ch_stop, ch_discount, ch_type as ch_typeno,ch_saletype,num_sale
from sn_bt_roomsitem
union
select ch_bedno as ch_itemno, vch_bedname, '' as ch_unitno, '' as vch_spell, '' as ch_payno, 0 as num_first_price, 0 as num_next_price, 'D' as ch_kind, ch_stop, ch_discount, ch_type as ch_typeno,ch_saletype,num_sale
from sn_bt_roomsitem_detail
union
select ch_ticketno, vch_ticketname, '' as ch_unitno, vch_spell as vch_spell, '' as ch_payno, 0 as num_first_price, 0 as num_next_price, 'E' as ch_kind, ch_stop, 'N' as ch_discount, '' as ch_typeno,ch_saletype,num_sale
from sn_bt_ticket_master
GO

if not exists (select 1 from sn_sys_purview_list  where ch_purviewno = '0200')
	insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm)
	values('0200','添加打印项目','N','EDIT','w_bt_add_print_costtype','项目打印','02','0','N',null)
go

if not exists (select 1 from sn_sys_purview_list  where ch_purviewno = '0201')
	insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm)
	values('0201','打印参数设置','N','EDIT','w_bt_cp_parameter','项目打印','02','0','N',null)
go

if not exists (select 1 from sn_sys_purview_list  where ch_purviewno = '0202')
	insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm)
	values('0202','打印方案设置','N','EDIT','w_bt_cp_project','项目打印','02','0','N',null)
go

if not exists (select 1 from sn_sys_purview_list  where ch_purviewno = '0203')
	insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm)
	values('0203','项目打印关联','N','EDIT','w_bt_handcard_floor','项目打印','02','0','N',null)
go

if not exists(select * from syscolumns where id = object_id('t_m_member') and name = 'vch_commend')
	alter table t_m_member add vch_commend char(6) null
go

if not exists(select * from syscolumns where id = object_id('t_mhq_member') and name = 'vch_commend')
	alter table t_mhq_member add vch_commend char(6) null
go

if not exists(select * from syscolumns where id = object_id('t_hq_m_member') and name = 'vch_commend')
	alter table t_hq_m_member add vch_commend char(6) null
go

if not exists(select * from syscolumns where id = object_id('sn_u_costitem') and name = 'ch_state_again')
	alter table sn_u_costitem add ch_state_again char(1) null default ('N')
go

if not exists(select * from syscolumns where id = object_id('sn_bt_employee_order') and name = 'dt_startdate')
	alter table sn_bt_employee_order add dt_startdate datetime null
go

if not exists(select * from syscolumns where id = object_id('sn_bt_employee_order') and name = 'dt_enddate')
	alter table sn_bt_employee_order add dt_enddate datetime null
go

if not exists(select * from syscolumns where id = object_id('sn_bt_employee_order') and name = 'int_order')
	alter table sn_bt_employee_order add int_order int null
go

if not exists (select 1 from sysobjects where id = object_id('sn_sys_device_group') and type = 'U')
	create table sn_sys_device_group (
		int_id int IDENTITY (1, 1) not null,
		com_no char (5) not null,
		ch_itemno char(6) not null,
		device_no char(2) not null,
		constraint pk_sn_sys_device_group primary key (int_id)
)
go

if not exists(select * from syscolumns where id = object_id('sn_bt_employee') and name = 'ch_cardno')
	alter table sn_bt_employee add ch_cardno char(10) null
go

if not exists(select * from syscolumns where id = object_id('t_m_member') and name = 'vch_commend_memberno')
	alter table t_m_member add vch_commend_memberno char(20) null
go

if not exists(select * from syscolumns where id = object_id('t_mhq_member') and name = 'vch_commend_memberno')
	alter table t_mhq_member add vch_commend_memberno char(20) null
go

if not exists(select * from syscolumns where id = object_id('t_hq_m_member') and name = 'vch_commend_memberno')
	alter table t_hq_m_member add vch_commend_memberno char(20) null
go

if not exists(select * from syscolumns where id = object_id('t_m_member') and name = 'vch_sex')
	alter table t_m_member add vch_sex char(1) null
go

if not exists(select * from syscolumns where id = object_id('t_mhq_member') and name = 'vch_sex')
	alter table t_mhq_member add vch_sex char(1) null
go

if not exists(select * from syscolumns where id = object_id('t_hq_m_member') and name = 'vch_sex')
	alter table t_hq_m_member add vch_sex char(1) null
go

if not exists(select * from syscolumns where id = object_id('sn_u_costitem') and name = 'ch_clock_next')
	alter table sn_u_costitem add ch_clock_next char(1) null default ('N')
go

if not exists(select * from syscolumns where id = object_id('sn_u_emp_time') and name = 'int_orderid')
	alter table sn_u_emp_time add int_orderid int null default (0)
go

if not exists(select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_first_price')
	alter table sn_bt_serveritem add num_m_first_price decimal(18, 2) null DEFAULT(0)
go

if not exists(select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_next_price')
	alter table sn_bt_serveritem add num_m_next_price decimal(18, 2) null DEFAULT(0)
go

if not exists(select * from syscolumns where id = object_id('sn_bt_goodsitem') and name = 'num_m_price')
	alter table sn_bt_goodsitem add num_m_price decimal(18, 2) null DEFAULT(0)
go

if not exists(select * from syscolumns where id = object_id('t_m_member') and name = 'im_photo')
	alter table t_m_member add im_photo image null
go

if not exists(select * from syscolumns where id = object_id('t_mhq_member ') and name = 'im_photo')
	alter table t_mhq_member  add im_photo image null
go

if not exists(select * from syscolumns where id = object_id('t_hq_m_member  ') and name = 'im_photo')
	alter table t_hq_m_member   add im_photo image null
go

--总部分部的项目表中增加营销员名
if not exists(select * from syscolumns where id = object_id('sn_u_costitem') and [name] = 'ch_salename')
alter table sn_u_costitem
add ch_salename varchar(10)
GO

if not exists(select * from syscolumns where id = object_id('snhq_u_costitem') and [name] = 'ch_salename')
alter table snhq_u_costitem
add ch_salename varchar(10)
GO

--增加客人入住信息
if not exists (select 1 from sysobjects where id = object_id('sn_bt_client') and type = 'U')
create table sn_bt_client(
	ch_billno char(12) not null,
	ch_id char(10) not null,
	ch_idtype char(16) not null,
	vch_idcode varchar(20)not null,
	ch_name char(10) not null,
	ch_sex char(1) not null,
	dt_date datetime,
	vch_country varchar(30),
	ch_roomno char(8),
	dt_indate datetime,
	dt_outdate datetime,
	vch_province varchar(20),
	vch_addr varchar(50),
	vch_memo varchar(50),
	constraint pk_sn_bt_client primary key (ch_billno,ch_id))

go

if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'dt_date')
	alter table sn_bt_client alter column dt_date datetime null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'vch_country')
	alter table sn_bt_client alter column vch_country varchar(30) null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'ch_roomno')
	alter table sn_bt_client alter column ch_roomno char(8) null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'dt_indate')
	alter table sn_bt_client alter column dt_indate datetime null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'dt_outdate')
	alter table sn_bt_client alter column dt_outdate datetime null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'vch_province')
	alter table sn_bt_client alter column vch_province varchar(20) null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'vch_addr')
	alter table sn_bt_client alter column vch_addr varchar(50) null
go
if exists (select * from syscolumns where id = object_id('sn_bt_client') and name = 'vch_memo')
	alter table sn_bt_client alter column vch_memo varchar(50) null
go

--录入项目备注信息
if not exists (select * from syscolumns where id = object_id('sn_u_costitem') and name = 'vch_memo')
  	alter table sn_u_costitem add vch_memo Varchar(255)
go
--审核手单号
if not exists (select * from syscolumns where id = object_id('sn_u_costitem') and name = 'ch_handbill_check')
  	alter table sn_u_costitem add ch_handbill_check char(1)
go


if not exists(select * from syscolumns where id = object_id('sn_u_trans') and name = 'int_flowid')
	alter table sn_u_trans add int_flowid int identity
go
if exists(select 1 from sysobjects where name = 'pk_sn_u_trans')
	alter table sn_u_trans drop constraint pk_sn_u_trans
	alter table sn_u_trans add constraint pk_sn_u_trans primary key(int_flowid)
go
	

--增加存物柜功能
if not exists (select 1
            from  sysobjects
           where  id = object_id('sn_u_deposit_card')
            and   type = 'U')
CREATE TABLE [sn_u_deposit_card] (
	[vch_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_cardno] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_flag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_identity] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_tel] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_memo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_CYBR_U_DEPOSIT_CARD] PRIMARY KEY  CLUSTERED 
	(
		[vch_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if not exists (select 1
            from  sysobjects
           where  id = object_id('sn_u_deposit_coffer')
            and   type = 'U')
CREATE TABLE [sn_u_deposit_coffer] (
	[vch_cofferno] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_coffer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_safedepositno] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_CYBR_U_DEPOSIT_COFFER] PRIMARY KEY  CLUSTERED 
	(
		[vch_cofferno]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

if not exists (select 1
            from  sysobjects
           where  id = object_id('sn_u_deposit_goods')
            and   type = 'U')
CREATE TABLE [sn_u_deposit_goods] (
	[ch_safedepositno] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_cofferno] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_deposit] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_cardID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_other] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_otherID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_address] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_tel] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_voucherno] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[dt_deposit] [datetime] NULL ,
	[dt_fetch_about] [datetime] NULL ,
	[num_amount] [numeric](10, 2) NULL CONSTRAINT [DF__cybr_u_de__num_a__4C0144E4] DEFAULT (0),
	[num_receivable] [numeric](10, 2) NULL CONSTRAINT [DF__cybr_u_de__num_r__4CF5691D] DEFAULT (0),
	[num_realamount] [numeric](10, 2) NULL CONSTRAINT [DF__cybr_u_de__num_r__4DE98D56] DEFAULT (0),
	[vch_dmemo] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_fmemo] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_cancel] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__cybr_u_de__ch_ca__4EDDB18F] DEFAULT ('N'),
	[vch_deposit_operID] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[vch_fetch_operID] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_upflag] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__cybr_u_de__ch_up__4FD1D5C8] DEFAULT ('N'),
	[dt_validdate] [datetime] NULL ,
	CONSTRAINT [PK_cybr_u_safedeposit] PRIMARY KEY  CLUSTERED 
	(
		[ch_safedepositno]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

--增加会员合同功能
if not exists (select 1
            from  sysobjects
           where  id = object_id('t_m_contract')
            and   type = 'U')
/*==============================================================*/
/* Table: t_m_contract                                          */
/*==============================================================*/
create table t_m_contract (
   vch_contractno       varchar(20)          not null,
   vch_memberno         varchar(20)          not null,
   num_item_amt         numeric(10,2)        null default 0,
   num_contract         numeric(10,2)        null default 0,
   num_pay_amt          numeric(10,2)        null default 0,
   dt_limit             datetime             null,
   ch_stopflag          char(1)              null default 'N',
   num_back_amt         numeric(10,2)        null default 0,
   vch_operid           varchar(6)           null,
   dt_operdate          datetime             null,
   vch_stop_operid      varchar(6)           null,
   dt_stopdate          datetime             null,
   vch_salesman         varchar(6)           null,
   num_deduct           numeric(10,2)        null default 0,
   ch_branchno          char(4)              null,
   ch_chain             char(1)              null default '0',
   vch_memo             varchar(100)         null,
   constraint PK_T_M_CONTRACT primary key  (vch_contractno)
)
go

if not exists (select 1
            from  sysobjects
           where  id = object_id('t_m_contract_item_detail')
            and   type = 'U')
/*==============================================================*/
/* Table: t_m_contract_item_detail                              */
/*==============================================================*/
create table t_m_contract_item_detail (
   vch_contractno       varchar(20)          not null,
   ch_itemno            char(6)              not null,
   ch_type              char(1)              not null,
   num_num              numeric(10,2)        null default 0,
   num_use              numeric(10,2)        null default 0,
   num_price            numeric(10,2)        null default 0,
   constraint PK_T_M_CONTRACT_ITEM_DETAIL primary key  (vch_contractno, ch_itemno, ch_type)
)
go

if not exists (select 1
            from  sysobjects
           where  id = object_id('t_m_contract_rooms_detail')
            and   type = 'U')
/*==============================================================*/
/* Table: t_m_contract_rooms_detail                             */
/*==============================================================*/
create table t_m_contract_rooms_detail (
   vch_contractno       varchar(20)          not null,
   ch_typeno            char(4)              not null,
   ch_kind              char(1)              not null,
   ch_bed               char(1)              not null,
   num_num              numeric(10,2)        null default 0,
   num_use              numeric(10,2)        null default 0,
   num_price            numeric(10,2)        null default 0,
   constraint PK_T_M_CONTRACT_ROOMS_DETAIL primary key  (vch_contractno, ch_typeno, ch_kind, ch_bed)
)
go

--会员合同消费明细表
if not exists (select 1
            from  sysobjects
           where  id = object_id('sn_u_member_contract')
            and   type = 'U')

/*==============================================================*/
/* Table: sn_u_member_contract                                  */
/*==============================================================*/
create table sn_u_member_contract (
   int_flowid           int                  identity,
   vch_contractno       varchar(20)          null,
   vch_memberno         varchar(20)          null,
   int_ordflow          int                  null,
   ch_type              char(1)              null,
   ch_itemno            char(6)              null,
   ch_suitflag          char(1)              null,
   num_num              numeric(10,2)        null,
   num_price            numeric(10,2)        null,
   num_price2           numeric(10,2)        null,
   num_amount           numeric(10,2)        null,
   ch_billno            char(12)             null,
   ch_payno             char(12)             null,
   ch_state             char(1)              null,
   ch_hqflag            char(1)              null,
   ch_up                char(1)              null,
   constraint PK_SN_U_MEMBER_CONTRACT primary key  (int_flowid)
)
go

if not exists(Select 1 from syscolumns where id = object_id('sn_u_member_contract') and name = 'int_ordflow')
	alter table sn_u_member_contract add int_ordflow int null 
go
--会员合同签订收银流水记录
if not exists (select 1
            from  sysobjects
           where  id = object_id('t_m_contract_pay')
            and   type = 'U')
/*==============================================================*/
/* Table: t_m_contract_pay                                      */
/*==============================================================*/
create table t_m_contract_pay (
   int_flowid           int                  identity,
   vch_contractno       varchar(20)          null,
   vch_memberno         varchar(20)          null,
   ch_opertype          char(1)              null,
   num_amount           numeric(10,2)        null,
   ch_paymode           char(2)              null,
   vch_operid           char(6)              null,
   dt_operdate          datetime             null,
   constraint PK_T_M_CONTRACT_PAY primary key  (int_flowid)
)
go

--本地会员合同功能语句
delete from sn_sys_purview_list where ch_purviewno = '0322'
go
insert into sn_sys_purview_list values ('0322','会员合同管理','N','SHEE','w_m_member_contract','本地会员','03','0','Y',1,'3')
go
--本地会员合同报表功能语句
delete from sn_sys_purview_list where ch_purviewno = '0323'
go
insert into sn_sys_purview_list values ('0323','会员合同报表查询','N','SHEE','w_rpt_member_contract','本地会员','03','0','Y',1,'3')
go
--会员合同消费位。
if not exists(Select 1 from syscolumns where id = object_id('sn_u_costitem') and name = 'ch_pactflag')
	alter table sn_u_costitem add ch_pactflag char(1) null default 'N'
go

--会员类型表中增加积分兑换金额的比例值
if not exists (Select 1  FROM syscolumns a,sysobjects b
where a.name = 'num_point_change' and a.id = b.id and  b.name = 't_m_member_type')
alter table t_m_member_type
add num_point_change numeric(10,2)

go 

if not exists (Select 1  FROM syscolumns a,sysobjects b
where a.name = 'num_point_change' and a.id = b.id and  b.name = 't_mhq_member_type')
alter table t_mhq_member_type
add num_point_change numeric(10,2)

go 

if not exists (Select 1  FROM syscolumns a,sysobjects b
where a.name = 'num_point_change' and a.id = b.id and  b.name = 't_hq_m_member_type')
alter table t_hq_m_member_type
add num_point_change numeric(10,2)

go 

if exists (select * from sysobjects where id = object_id('tr_turn_technicain_end') and xtype = 'TR')   
   drop trigger tr_turn_technicain_end
go


if not exists (select * from syscolumns where id = object_id('sn_bt_employee') and name = 'int_preorder')
   alter table sn_bt_employee add int_preorder integer
go

if not exists (select * from syscolumns where id = object_id('sn_bt_employee') and name = 'ch_modify')
   alter table sn_bt_employee add ch_modify char(1) default 'N'
go

if exists (select * from sysobjects where id = object_id('tr_member_hang') and xtype='TR')
   drop trigger tr_member_hang
go


CREATE  TRIGGER tr_member_hang ON t_m_member
FOR UPDATE 
AS
begin   ---更新num_limit列才触发
declare @limit_now numeric(10,2),
	@limit_old numeric(10,2),
	@memberno varchar(10),
	@amount numeric(10,2)
	
if update(num_limit)
   select @memberno = vch_memberno,@limit_now = num_limit from inserted
   select @limit_old = num_limit from deleted
   select @amount = num_amount from t_m_curamount where vch_memberno = @memberno
   if (@@rowcount = 0) return  
   update t_m_curamount
   set num_amount = @limit_now - @limit_old + @amount
   where vch_memberno = @memberno
end
go

--增加同单已结账的多个手牌
if not exists(select * from syscolumns where id = object_id('sn_u_checkout_master') and name = 'vch_pay_handno')
	alter table sn_u_checkout_master add vch_pay_handno varchar(100)
GO

--自动组合套票功能，增加字段标识当前记录是否已经被组合过
if not exists(select * from syscolumns where id = object_id('sn_u_costitem') and [name] = 'ch_autoticket')
	alter table sn_u_costitem add ch_autoticket char(1)
go

if not exists (select 1 from  sysobjects where  id = object_id('sn_u_costitem_backup')and type = 'U')
	CREATE TABLE sn_u_costitem_backup(
		Int_flowid int IDENTITY (1, 1) not null,
		Ch_billno char(12) NOT NULL,
		Ch_no char(6) NOT NULL,
		Ch_itemno char(6) NOT NULL,
		ch_kind char(1) null,
		Ch_itemno_m char(6) null,
		ch_tickno char(4) null,
		ch_state char(1) null,
		Num_num decimal(18, 2) null,
		num_back decimal(18, 2) null,
		int_add int null,
		Num_price decimal(18, 2) null,
		Num_price_o decimal(18, 2) null,
		num_next_price decimal(18, 2) null,
		Num_amount decimal(18, 2) null,
		Dt_start datetime null,
		Dt_end datetime null,
		Dt_end1 datetime null,
		Num_minute decimal(18, 2) null,
		Ch_empno varchar(6) null,
		ch_empno2 varchar(6) null,
		ch_empstate char(1) null,
		ch_empstate2 char(1) null,
		ch_presentflag char(1) null,
		ch_payflag char(1) null,
		vch_handbill varchar(40) null,
		ch_costtype char(1) null,
		ch_costtype2 char(1) null,
		ch_transflag char(1) null,
		Dt_regdate datetime null,
		ch_discount_type char(1) null,
		ch_printflag char(1) null,
		Vch_reg varchar(6) null,
		vch_present varchar(6) null,
		dt_present datetime null,
		num_discount decimal(18, 2) null,
		ch_room char(1) null,
		vch_memberno varchar(20) null,
		ch_payno char(12) null,
		ch_payway char(3) null,
		ch_mode char(1) null,
		ch_awoke char(1) null,
		ch_saveflag char(1) null,
		ch_suitflag char(1) null,
		vch_computer varchar(30) null,
		vch_team varchar(3) null,
		ch_state_again char(1) null,
		ch_clock_next char(1) null,
		ch_saletype char(1) null,
		ch_saleid char(6) null,
		num_sale numeric(10, 4) null,
		ch_salename varchar(10) null,
		vch_memo varchar(255) null,
		ch_handbill_check char(1) null,
		ch_pactflag char(1) null,
		ch_cur_no char(6) null,
		ch_origin_no char(6) null,
		ch_autoticket char(1) null,
		PRIMARY  key(Int_flowid)
)
go

--发票发放情况查询功能语句
delete from sn_sys_purview_list where ch_purviewno = '0619'
go
insert into sn_sys_purview_list values ('0619','发票发放情况查询','N','SHEE','w_rpt_checkout_ticketquery','数据查询','07','0','Y',null,'3')
go

--增加会员充值现金返现设置
--新建充值返现表（单店）
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_m_type_deposit') and type = 'U')
   create table dbo.t_m_type_deposit (
   int_flowID           int                  identity,
   ch_typeno        	char(2)		     not null,
   int_id            	int		     not null,
   num_begin            numeric(10,2)             null,
   num_end		numeric(10,2)		  null,
   num_deposit		numeric(10,2)		  null,
   vch_operid           char(6)			  null,
   dt_operdate		datetime		  null,  
 constraint PK_t_m_type_deposit primary key  (int_flowID)
)
go

--新建充值返现表（连锁）
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_mhq_type_deposit') and type = 'U')
   create table dbo.t_mhq_type_deposit (
   int_flowID           int                  identity,
   ch_typeno        	char(2)		     not null,
   int_id            	int		     not null,
   num_begin            numeric(10,2)             null,
   num_end		numeric(10,2)		  null,
   num_deposit		numeric(10,2)		  null,
   vch_operid           char(6)			  null,
   dt_operdate		datetime		  null,
 constraint PK_t_mhq_type_deposit primary key  (int_flowID)
)
go


--新建充值返现表（总部）
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_hq_m_type_deposit') and type = 'U')
   create table dbo.t_hq_m_type_deposit (
   int_flowID           int                  identity,
   ch_typeno        	char(2)		     not null,
   int_id            	int		     not null,
   num_begin            numeric(10,2)             null,
   num_end		numeric(10,2)		  null,
   num_deposit		numeric(10,2)		  null,
   vch_operid           char(6)			  null,
   dt_operdate		datetime		  null,
 constraint PK_t_hq_m_type_deposit primary key  (int_flowID)
)
go

--总部和分部数据库都需要增加会员短信群发和短信功能说明的菜单接口。
if not exists (select 1 from sn_sys_purview_list where ch_purviewno = '1507')
insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm) values ('1507','会员短信群发','N','SHEE','w_message_vip','短信管理','15','0','Y','')
go


if not exists (select 1 from sn_sys_purview_list where ch_purviewno = '1509')
insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm) values ('1509','短信使用说明','N','SHEE','w_message_info','短信管理','15','0','Y','')
go

--总部和分部数据库都需要添加常用短语表
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_ms_comm_used') and type = 'U')
create table t_ms_comm_used
(
	id  int  not null,
	mess_title varchar(20) null,
	mess_content varchar(200) null,
	constraint PK_t_ms_comm_used primary key  (id)
)
go


--增加桑拿操作日志记录表：
if not exists (select 1 from  sysobjects where id = object_id('dbo.t_sys_journal') and type = 'U')
	create table t_sys_journal
(
	int_flowID  int  identity,
	vch_module varchar(20) not null,
	vch_function varchar(20) not null,
	vch_description varchar(100) null,
	vch_billtype  varchar(20)  null,
	vch_billno  varchar(20)  null,
	vch_operid  varchar(6)  null,
	vch_powerid  varchar(6) null,
	dt_operdate  datetime null,
	vch_computer  varchar(20) null,
	constraint PK_t_sys_journal primary key  (int_flowID)
)

if not exists(select 1 from syscolumns a,sysobjects b where a.id = b.id and a.name = 'dt_build' and b.name = 'sn_bt_roomsitem')
	alter table sn_bt_roomsitem add dt_build datetime null
go
 
if not exists(select 1 from syscolumns a,sysobjects b where a.id = b.id and a.name = 'dt_build' and b.name = 'sn_bt_serveritem')
	alter table sn_bt_serveritem add dt_build datetime null
go

if not exists(select 1 from syscolumns a,sysobjects b where a.id = b.id and a.name = 'dt_build' and b.name = 'sn_bt_goodsitem')
	alter table sn_bt_goodsitem add dt_build datetime null
go

If not exists (Select * From syscolumns Where id = object_id('sn_u_openbill') and name = 'ch_union_no')
	alter table sn_u_openbill add ch_union_no char(12) 
go

if not exists (select 1 from sn_sys_purview_list  where ch_purviewno = '0324')
	insert into sn_sys_purview_list (ch_purviewno,vch_purviewname,ch_day_cue,ch_open_type,vch_window,vch_purview_typename,ch_purview_typeno,ch_type,ch_report,vch_withparm)
	values('0324','客房充值会员充值','N','SHEE','w_m_member_deposit_hotel','客房会员','03','0','N','1')
go

if not exists(select 1 from systypes where name='u_oper_id')
  execute sp_addtype u_oper_id, 'char(4)'
go

if not exists (select 1
            from  sysobjects
           where  id = object_id('dbo.t_sys_sheet_master_info')
            and   type = 'U')
  
create table dbo.t_sys_sheet_master_info (
   vch_mainname         varchar(40)          not null,
   vch_templatename     varchar(40)          not null,
   vch_titledesc        varchar(40)          not null,
   vch_titlecode        varchar(40)          not null,
   vch_titletext        varchar(40)          null,
   ch_printflag         char(1)              not null default '1',
   ch_displayflag       char(1)              not null default '1',
   ch_headfooterflag    char(1)              not null default '0',
   ch_typeflag          char(1)              not null default '1',
   vch_otherproperty    varchar(512)         null,
   ch_operid            u_oper_id            not null,
   constraint AK_KEY_1_T_SYS_SH unique (vch_mainname, vch_templatename, vch_titledesc, ch_headfooterflag, ch_typeflag, ch_operid)
)
go

/*==================================================================
-----------------------系统数据-----------------------------------
==================================================================*/
----增加基本付款方式‘转客房账’
if not exists(select 1 from sn_bt_pay where ch_payno = '89')
	insert into sn_bt_pay(ch_payno,vch_payname,ch_idflag,ch_deno,ch_change,ch_income,ch_forcur,ch_forno,vch_quickkey,num_maxface)
	values('89','转客房账','N','N','N','Y','N',NULL,'Z',0)
go

----增加基本付款方式‘客房充值卡’
if not exists(select 1 from sn_bt_pay where ch_payno = '90')
	insert into sn_bt_pay(ch_payno,vch_payname,ch_idflag,ch_deno,ch_change,ch_income,ch_forcur,ch_forno,vch_quickkey,num_maxface)
	values('90','客房充值卡','N','N','N','Y','N',NULL,'X',0)
go
----增加基本付款方式 '餐饮充值卡'
if not exists (select 1 from sn_bt_pay where ch_payno = '91')
	insert into sn_bt_pay values ( '91','餐饮充值卡','N','N','N','Y','N',NULL,'C',0)
go

----操作员权限里面增加控制赠送权限
if not exists(select 1 from sysobjects a,syscolumns b where a.id = b.id and a.name = 'sn_bt_operate' and b.name = 'ch_present_mode')
		alter table sn_bt_operate
		add ch_present_mode char(1) null 
go
----总部不管理原料
update t_stbr_material set ch_stock='Y'
go


if not exists (select 1 from sysindexes a, sysobjects b where a.id = b.id and a.name = 'idx_sn_bt_technician_deduct_detail' and b.name = 'sn_bt_technician_deduct_detail')
	create index idx_sn_bt_technician_deduct_detail on sn_bt_technician_deduct_detail(ch_empno,ch_billno,ll_flowid)
go

if not exists (select 1 from sysindexes a, sysobjects b where a.id = b.id and a.name = 'idx_snhq_bt_technician_deduct_detail' and b.name = 'snhq_bt_technician_deduct_detail')
	create index idx_snhq_bt_technician_deduct_detail on snhq_bt_technician_deduct_detail(ch_branchno,ch_empno,ch_billno,ll_flowid)
go
if not exists(select 1 from sn_sys_purview_list where ch_purviewno = '0533')
INSERT INTO sn_sys_purview_list
VALUES('0533','消费情况查询','N','SHEE','w_oth_all_cost_rpt','前台管理','05','0', 'N', '','3')
go
if not exists(select 1 from sn_sys_purview_list where ch_purviewno = '0534')
INSERT INTO sn_sys_purview_list
VALUES('0534','当前消费查询','N','SHEE','w_oth_billno_cost','前台管理','05','0', 'N', '','3')
go

if not exists(select 1 from sn_sys_purview_list where ch_purviewno = '3404')
INSERT INTO sn_sys_purview_list
VALUES('3404','操作日志','N','SHEE','w_rpt_emp_oper','系统管理','08','0','Y','','3')
go


if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_first_price2')
  alter table sn_bt_serveritem add num_m_first_price2 [decimal](18, 2)
go


if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_first_price3')
  alter table sn_bt_serveritem add num_m_first_price3 [decimal](18, 2)
go

if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_next_price2')
  alter table sn_bt_serveritem add num_m_next_price2 [decimal](18, 2)
go


if not exists (select * from syscolumns where id = object_id('sn_bt_serveritem') and name = 'num_m_next_price3')
  alter table sn_bt_serveritem add num_m_next_price3 [decimal](18, 2)
go



if not exists (select * from syscolumns where id = object_id('sn_bt_goodsitem') and name = 'num_m_price2')
  alter table sn_bt_goodsitem add num_m_price2 [decimal](18, 2)
go


if not exists (select * from syscolumns where id = object_id('sn_bt_goodsitem') and name = 'num_m_price3')
  alter table sn_bt_goodsitem add num_m_price3 [decimal](18, 2)
go


if not exists (select * from syscolumns where id = object_id('t_m_contract') and name = 'ch_typeno')
  alter table t_m_contract add [ch_typeno] [char] (2)
go
--一键优化
if not exists(select 1 from sn_sys_purview_list where ch_purviewno = '3405')
INSERT INTO sn_sys_purview_list
VALUES('3405','一键优化','N','EDIT','w_ub_onekey_deal','系统管理','08','0','Y','','3')
go


if not exists(select * from sysobjects where id = object_id('cybr_book_detail') and xtype='U')
CREATE TABLE [cybr_book_detail] (
	[ch_bookno] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[int_id] [int] NOT NULL ,
	[ch_dishno] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[vch_spec] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[num_num] [numeric](10, 2) NULL ,
	[num_price] [numeric](10, 2) NULL ,
	[vch_print_memo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[num_price_add] [numeric](10, 2) NULL ,
	[ch_suitflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_suitno] [char] (7) COLLATE Chinese_PRC_CI_AS NULL ,
	[int_material_rate] [int] NULL ,
	[ch_fixprice] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_specialflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
	[ch_eatflag] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__cybr_book__ch_ea__76897FFB] DEFAULT ('N'),
	CONSTRAINT [PK_CYBR_BOOK_DETAIL] PRIMARY KEY  CLUSTERED 
	(
		[ch_bookno],
		[int_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

