--truncate table dbo.business_head
--truncate table dbo.business_list
--truncate table dbo.business_head_cancel
--truncate table dbo.business_list_cancel
--truncate table dbo.CRM_ACCOUNT_CONSUME_DT
--truncate table dbo.CRM_integral_date
--truncate table business_payment_type
truncate table dbo.business_jobsreport
--truncate table system_blno
update dbo.Base_Sign_number
set state = '0',blno =''
--
truncate table dbo.business_branch_clerk
select * from cdzb where cdzj = 1;套票明细
select * from bzcd;套票头表
update BASE_COPY_list
set name = (select name from base_list where base_list.mainkey = BASE_COPY_list.base_listmk),
unit = (select unit from base_list where base_list.mainkey = BASE_COPY_list.base_listmk)
Create Table Update_tmp(
file_name varchar(28) not null primary key,
pbd image default '0101',
updatedt datetime default getdate()
);
--2007-06-28 索引
CREATE
INDEX [IX_business_list_1] ON [dbo].[business_list] ([belncompany], [Report_date], [pay_up], [artificerID], [terminal_time])
UPDATE CRM_BASE
SET account_type = 10
WHERE (card_no IN
(SELECT card_no
FROM dbo.CRM_account))
//卡型更改
select yygl.dbo.khzh.khzh from yygl.dbo.khzh where yygl.dbo.khzh.kxzj= 5
select * from data_xqr.dbo.crm_base
update data_xqr.dbo.crm_base
set data_xqr.dbo.crm_base.account_type = 19
where data_xqr.dbo.crm_base.card_no in(select yygl.dbo.khzh.khzh from yygl.dbo.khzh where yygl.dbo.khzh.kxzj= 5)
khkx 会员卡类型表
khzh 会员帐户表 (kxzj 卡型ID)
2007-02-17
--是否需要厨师
alter table Base_list
add is_Is_chef char(1) default 'N'
//在业务数据中增加厨师
Alter table business_list
add chef varchar(28)
押金模块
w_account_cash_pledge
//押金表
CREATE TABLE Account_cash_pledge(Mainkey int IDENTITY, belncompany int,
code varchar(28) NULL, name varchar(28) NULL, tel varchar(28) NULL,
ID_number varchar(38) NOT NULL, sex varchar(2) NULL, cash_pledge dec(13, 2) NULL,
Is_valid char(1) NULL
DEFAULT 'Y', memo varchar(100) NULL, Create_user varchar(27) NULL,
Create_name varchar(72) NULL, Create_date datetime NULL,
CONSTRAINT PK_ACCOUNT_CASH_PLEDGE PRIMARY KEY (ID_number))
--导出标示
alter table dbo.business_head
add is_export char(1) null default 'N'
alter table dbo.business_head
add export_blno varchar(28) null
UPDATE business_head
SET is_export = 'N'
WHERE (is_export IS NULL)
//卡是否仅限于某限使用
alter table CRM_BASE
add is_area_card char(1) default 'N';
CRM_BASE 表 belncompany 由varchar 改为 int型
//头表视图
Create View view_member as
SELECT business_head.Card_no,
business_head.cash,
business_head.Mainkey,
business_head.Team_blno,
business_head.day_blno,
business_head.blno,
business_head.base_listID,
business_head.mannumber,
business_head.pay_up,
business_head.Sign_number,
business_head.consult_number,
business_head.belncompany,
business_head.discount,
business_head.Is_bill,
business_head.Bill,
business_head.Create_Date,
business_head.pay_date,
business_head.Is_Membconsume,
business_head.other_fee,
business_head.print_number,
business_head.Is_team,
business_head.state,
business_head.memo,
business_head.is_trust,
business_head.cash_man,
business_head.Report_date,
business_head.pre_print,
business_head.cash_pledge,
business_head.is_add_buffet,
business_head.is_export,
business_head.export_blno
FROM business_head
;
//会员卡视图
Create view view_CRM_BASE
as
SELECT CRM_BASE.Mainkey,
CRM_BASE.belncompany,
CRM_BASE.account_type,
CRM_BASE.Is_del,
CRM_BASE.is_Disable,
CRM_BASE.card_no,
CRM_BASE.name,
CRM_BASE.password,
CRM_BASE.account_date,
CRM_BASE.end_date,
CRM_BASE.link_man,
CRM_BASE.tel,
CRM_BASE.fax,
CRM_BASE.sex,
CRM_BASE.Email,
CRM_BASE.address,
CRM_BASE.papers_type,
CRM_BASE.papers_number,
CRM_BASE.by_name,
CRM_BASE.Is_check,
CRM_BASE.checker,
CRM_BASE.checker_date,
CRM_BASE.Create_user,
CRM_BASE.create_date,
CRM_BASE.update_user,
CRM_BASE.update_time,
CRM_BASE.remark,
CRM_BASE.is_master_card,
CRM_BASE.master_card,
CRM_BASE.is_integral,
CRM_BASE.Is_export,
CRM_BASE.last_consume_date,
CRM_BASE.Max_consume_number,
CRM_BASE.Date_consume_number,
CRM_BASE.cent,
CRM_BASE.is_trust,
CRM_BASE.is_area_card
FROM CRM_BASE;
w_report_member_total 会员消费查询
w_report_member_month 会员月消费查询
w_report_member_stop 待停卡查询
w_sheet_note_money_bill 浴券明细
w_sheet_note_extend 浴券分发
w_blno_extend 浴券申请
//2007-03-06
//加单增加加单流水号
alter table dbo.business_list
add serial_number varchar(28)
代金券管理
note_money_bill
Create Table
serno unprice unit create_code,create_name,create_time
create table note_money_bill (
Mainkey int Identity ,
serno varchar(28) not null,
unprice dec(12,3) null,
unit varchar(28) null,
Is_valid char(1) null default 'Y',
memo varchar(100) null,
Create_user varchar(27) null,
Create_name varchar(72) null,
Create_date datetime null,
constraint PK_NOTE_MONEY_BILL primary key (serno)
)
//是否已经分发
alter table note_money_bill
add yesno char(1) default 'N';
//申请流水号
alter table note_money_bill
add apply_blno varchar(28) ;
//使用时间
alter table note_money_bill
add consume_date datetime ;
//是否被使用
alter table note_money_bill
add is_use char(1) default 'N';
//浴券发放头表()
create table note_apply_Hd (
Mainkey int Identity Not null,
apply_blno varchar(28),
apply varchar(28) not null,
blno_number Integer not null,
Is_valid char(1) null default 'Y',
memo varchar(100) null,
Create_user varchar(27) null,
Create_name varchar(72) null,
Create_date datetime null,
constraint PK_note_apply_Hd primary key (Mainkey)
);
//浴券发放明细没有用到
create table note_apply_dt(
apply_blno varchar(28),
serno varchar(28) not null,
Is_valid char(1) null default 'Y',
consume_date datetime ,
constraint PK_note_apply_dt primary key (apply_blno,serno)
);
//add lvxz 2007-03-13
Alter table business_head
add
bath_bill decimal(18,2) NULL,
bankcard decimal(18,2) NULL,
careguest decimal(18,2) NULL,
innersale decimal(18,2) NULL,
taxi decimal(18,2) NULL,
difamount decimal(18,2) NULL DEFAULT (0)
//add lvxz 2007-03-15增加会员生日
alter table crm_base
add birthday datetime;
//2007-03-16 浴券有效期
alter table dbo.note_apply_Hd
add column check_date datetime
alter table dbo.note_money_bill
add column check_date datetime
check_date
//浴券管理视图
SELECT note_money_bill.Mainkey,
note_money_bill.serno,
note_money_bill.unprice,
note_money_bill.unit,
note_money_bill.Is_valid,
note_money_bill.memo,
note_money_bill.Create_user,
note_money_bill.Create_name,
note_money_bill.Create_date,
note_money_bill.yesno,
note_money_bill.apply_blno,
note_money_bill.start_using_date ,
note_money_bill.check_date,
note_money_bill.consume_date,
note_money_bill.is_use
FROM note_money_bill
//浴券发放日期
alter table note_money_bill
add start_using_date datetime
w_report_note_query 浴券查询
//加入试用期结束后的限制
结帐不能打印结帐单
开牌一次只能开一个
加单不能打印结帐单
alter table business_head
add acct_blno varchar(28)
//结帐营业日
alter table business_list
add pay_report_date datetime;
dw_detail.Object.pay_report_date.Initial = String(Gnv_app.of_getbusiness_date())
//2007-03-22
//充值卡消费数据
CREATE INDEX CRM_ACCOUNT_CONSUME_DT_cardno
ON CRM_ACCOUNT_CONSUME_DT (card_no)
CREATE INDEX CRM_ACCOUNT_cardno
ON CRM_ACCOUNT (card_no)
Create View View_CRM_ACCOUNT_CONSUME_DT
as
SELECT CRM_ACCOUNT_CONSUME_DT.Mainkey,
CRM_ACCOUNT_CONSUME_DT.card_no,
CRM_ACCOUNT_CONSUME_DT.cash_blno,
CRM_ACCOUNT_CONSUME_DT.belncompany,
CRM_ACCOUNT_CONSUME_DT.blno,
CRM_ACCOUNT_CONSUME_DT.del_amount,
CRM_ACCOUNT_CONSUME_DT.create_user,
CRM_ACCOUNT_CONSUME_DT.create_time
FROM CRM_ACCOUNT_CONSUME_DT
Create View View_CRM_ACCOUNT
as
SELECT CRM_ACCOUNT.Mainkey,
CRM_ACCOUNT.cash_blno,
CRM_ACCOUNT.card_no,
CRM_ACCOUNT.print_date,
CRM_ACCOUNT.print_type,
CRM_ACCOUNT.print_amount,
CRM_ACCOUNT.pay_type,
CRM_ACCOUNT.use_amount,
CRM_ACCOUNT.total,
CRM_ACCOUNT.operater,
CRM_ACCOUNT.maketime,
CRM_ACCOUNT.remake
FROM CRM_ACCOUNT
w_member_rechargeable_car
充值卡余额不足
//2007-03-25
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER View view_business_list_zero
--将套票中的赠项设置为0，用于报表统计
as
SELECT business_list.Mainkey,
business_list.belncompany,
business_list.Report_date,
business_list.day_blno,
business_list.blno,
business_list.base_listID,
business_list.Sign_number,
business_list.base_item_typeMK,
business_list.Base_list_mainkey,
business_list.Base_list_name,
business_list.consume_area,
business_list.pay_type,
business_list.unprice,
case Is_freeItem
when 'N' Then business_list.unprice
when 'Y' Then 0
end as unprice_zero,
case Is_freeItem
when 'N' Then business_list.original_unprice
when 'Y' Then 0
end as original_unprice_zero,
business_list.number,
business_list.unit,
business_list.original_discount,
business_list.discount,
business_list.club_discount,
case Is_freeItem
when 'N' Then business_list.amount
when 'Y' Then 0
end as amount_zero,
business_list.amount,
business_list.del_decimal,
business_list.Is_move,
business_list.mov_user,
business_list.move_date,
business_list.serv_price,
business_list.serviceId,
business_list.artificer_price,
business_list.artificerID,
business_list.Is_copy_bill,
business_list.copy_bill_Sort,
business_list.copy_mainkey,
business_list.copy_price,
business_list.copy_bill,
business_list.Create_user,
business_list.Create_time,
business_list.end_time,
business_list.count_length,
business_list.remark,
business_list.payee,
business_list.pay_up,
business_list.pay_date,
business_list.Is_freeItem,
business_list.Is_del,
business_list.Is_default,
business_list.Is_remit,
business_list.Is_discount,
business_list.pay_report_date
FROM business_list
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//add lvxz 2007-03-28
//卡类
Alter table business_head
add card_type varchar(100);
//add lvxz 2007-03-28
//房间号
alter table dbo.business_list
Add room_number varchar(28)
//add lvxz 2007-04-01
//续卡属性（续卡，售卡）
alter table dbo.CRM_CARD_VERIFY
Add CRM_CARD_VERIFY varchar(20)
//ADD LVXZ 2007-04-2
//重新设计员工模块，增加部门表
Create Table CORP_DEPARTMENT
(MAINKEY INT IDENTITY PRIMARY KEY,
CODE VARCHAR(28) NOT NULL ,
NAME VARCHAR(28),
MEMO VARCHAR(200))
CREATE TABLE CORP_EMPLOYEES (
[DEPARTMENTID] [decimal](12, 0) NOT NULL ,
[CODE] [varchar] (20) NOT NULL Primary Key,
[NAME] [varchar] (50) NULL ,
[SEX] [varchar] (2) NULL ,
[BIRTHDAY] [datetime] NULL ,
[NATIVE] [varchar] (30) NULL ,
[IDENTITYID] [varchar] (30) NULL ,
[EDUCATIONAL] [varchar] (30) NULL ,
[POSITION] [varchar] (30) NULL ,
[INTOPOSTIME] [datetime] NULL ,
[TELEPHONE] [varchar] (30) NULL ,
[ADDRESS] [varchar] (200) NULL ,
[POST] [varchar] (6) NULL ,
[Status] [decimal](1, 0) NULL ,
[memo] [varchar] (200) NULL
)
sign_out_time
//2007-04-09
Create view view_member_balance_sheet
as
--充值卡余额表
select A.card_no,A.total,B.del_amount,(A.total - isnull(B.del_amount,0))balance from
(select card_no,sum(total)total from View_CRM_ACCOUNT Group by card_no)A,
(select card_no,sum(del_amount)del_amount from View_CRM_ACCOUNT_CONSUME_DT Group by card_no)B
WHERE (A.card_no *= B.card_no);
//2007-04-15
BASE_COPY_LIST
更改主键(Parent,Base_listMK)
//2007-04-17
//增加结帐标示
alter table hotel_man
add is_pay char(1) default 'N';
//修改权限表结构 PFC权限表
alter table security_template
add
sort varchar(20) default ('8888');
//更改折扣小数discount decimal(9,3)
BASE_CARD_discountset
//add lvxz 2007-04-21 头表视图
Create View view_business_head
as
SELECT business_head.Mainkey,
business_head.Team_blno,
business_head.day_blno,
business_head.blno,
business_head.base_listID,
business_head.mannumber,
business_head.pay_up,
business_head.Sign_number,
business_head.Pay_sign_number,
business_head.consult_number,
business_head.Card_no,
business_head.belncompany,
business_head.discount,
business_head.Is_bill,
business_head.Bill,
business_head.Create_Date,
business_head.pay_date,
business_head.Is_Membconsume,
business_head.cash,
business_head.other_fee,
business_head.account_receivable,
business_head.print_number,
business_head.Is_team,
business_head.state,
business_head.memo,
business_head.is_trust,
business_head.cash_man,
business_head.Report_date,
business_head.pre_print,
business_head.cash_pledge,
business_head.is_add_buffet,
business_head.is_export,
business_head.export_blno,
business_head.bath_bill,
business_head.bankcard,
business_head.careguest,
business_head.innersale,
business_head.taxi,
business_head.difamount,
business_head.rechargeable_card,
business_head.ACCT_BLNO,
business_head.card_type
FROM business_head ;
//2007-04-22
//房间ＩＤ
Alter table Hotel_man
add roomid integer
//增加午休房，钟点房，团队开房价格
Alter table Hotel_room
add noon_fee dec(9,2) default 0,hours_fee dec(9,2) default 0,
team_fee dec(9,2) default 0
//增加床位编号表
Create Table Hotel_room_bed(
room_mainkey int,
room_code varchar(28) not null,
room_bed varchar(28),
memo varchar(100),
Create_user varchar(28),
Create_time dateTime
);
//add 2007-04-28品项成本价
alter table Base_list
add cost_price decimal(9,2) default 0
//成本价
alter table business_list
add cost_price decimal(9,2) default 0
//成本价
alter table business_list_cancel
add cost_price decimal(9,2) default 0
alter table hotel_room_bed
add state varchar(28)
//日交班数据
CREATE TABLE dbo.business_jobsreport (day_blno varchar(28) NOT NULL, day_report image NULL , CONSTRAINT PK_business_jobsreport PRIMARY KEY CLUSTERED (day_blno)) ;
新泉日
//add lvxz 2007-04-30
alter table business_list
add sm_bill varchar(28),area varchar(28)
//小票号，场地号
alter table business_list_cancel
add sm_bill varchar(28),area varchar(28)
alter table Hotel_room_bed
add is_enable char(1) default'N'
//2007-04-30
//等级
alter table hotel_public_info
add grade varchar(28)
//区域
alter table hotel_public_info
add district varchar(28)
//传真，邮编
alter table hotel_public_info
add fax varchar(28),post varchar(28)
CREATE INDEX IX_BASE_COPY_LIST
ON dbo.BASE_COPY_LIST (belncompany)
CREATE
INDEX [IX_business_list] ON [dbo].[business_list] ([belncompany], [blno], [pay_up], [Is_del], [is_buffet])
WITH
DROP_EXISTING
ON [PRIMARY]
//add lvxz 2007-05-03
CREATE
INDEX [IX_business_branch_clerk] ON [dbo].[business_branch_clerk] ([branchKey], [Is_check])
WITH
DROP_EXISTING
ON [PRIMARY]
//add lvxz 2007-05-04
CREATE
INDEX [IX_Base_sales_promotion] ON [dbo].[Base_sales_promotion] ([belncompany], [Is_valid])
WITH
DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [IX_BASE_CARD_discountset] ON [dbo].[BASE_CARD_discountset] ([belncompany], [typeid], [item_typename], [Is_valid])
WITH
DROP_EXISTING
ON [PRIMARY]
//房价折扣
CREATE TABLE [dbo].[Hotel_room_discount] (
[over_hour] [decimal](18, 2) NOT NULL ,
[discount] [decimal](18, 2) NULL ,
[create_user] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[create_name] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[create_time] [datetime] NULL ,
[Memo] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
//5-5在头表中增加结帐营业日
alter table dbo.business_head
add pay_report_date datetime
//5-5在头表中增加结帐营业日
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER View view_business_head
as
SELECT business_head.Mainkey,
business_head.Team_blno,
business_head.day_blno,
business_head.blno,
business_head.base_listID,
business_head.mannumber,
business_head.pay_up,
business_head.Sign_number,
business_head.Pay_sign_number,
business_head.consult_number,
business_head.Card_no,
business_head.belncompany,
business_head.discount,
business_head.Is_bill,
business_head.Bill,
business_head.Create_Date,
business_head.pay_date,
business_head.Is_Membconsume,
business_head.cash,
business_head.other_fee,
business_head.account_receivable,
business_head.print_number,
business_head.Is_team,
business_head.state,
business_head.memo,
business_head.is_trust,
business_head.cash_man,
business_head.Report_date,
business_head.pre_print,
business_head.cash_pledge,
business_head.is_add_buffet,
business_head.is_export,
business_head.export_blno,
business_head.bath_bill,
business_head.bankcard,
business_head.careguest,
business_head.innersale,
business_head.taxi,
business_head.difamount,
business_head.rechargeable_card,
business_head.ACCT_BLNO,
business_head.card_type,business_head.pay_report_date
FROM business_head
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Proc Pro_quantity_serviceId
As
declare @sql nvarchar(1000)
--先添加头部！
set @sql='select pay_report_date,serviceId 服务员 '
--动态构造，仿造红色部分
select @sql = @sql+',sum(case Base_list_name when '''+Base_list_name+''' then number else 0 end) '+Base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct Base_list_name from business_list where pay_up ='Y') a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' group by pay_report_date,serviceId'
print @sql
exec(@sql)
Create Proc Pro_quantity_artificer
As
declare @sql nvarchar(1000)
--先添加头部！
set @sql='select pay_report_date,artificerID 技师 '
--动态构造，仿造红色部分
select @sql = @sql+',sum(case Base_list_name when '''+Base_list_name+''' then number else 0 end) '+Base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct Base_list_name from business_list where pay_up ='Y') a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' group by pay_report_date,artificerID '
print @sql
exec(@sql)
//add lvxz 2007-05-16 入库表，出库表
create Table Store_in(Mainkey int not null primary Key,
blno varchar(28),
Base_list_mainkey int,
Base_list_name varchar(28),
number integer,
unprice dec(13,2),
unit varchar(28),
Create_user varchar(28),
Create_name varchar(28),
Create_Time datetime,
Memo varchar(100)
)
create Table Store_out(Mainkey int not null primary Key,
blno varchar(28),
Base_list_mainkey int,
Base_list_name varchar(28),
number integer,
unit varchar(28),
Create_user varchar(28),
Create_name varchar(28),
Create_Time datetime,
Memo varchar(100)
)
CREATE
INDEX [IX_Store_in] ON [dbo].[Store_in] ([Base_list_mainkey], [Base_list_name], [unit])
WITH
DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [IX_Store_out] ON [dbo].[Store_out] ([Base_list_mainkey], [Base_list_name], [unit])
WITH
DROP_EXISTING
ON [PRIMARY]
//add lvxz 2007-05-19
//余额
alter CRM_ACCOUNT_CONSUME_DT
add balance dec(13,2)
//add lvxz 2007-05-26
Create view view_store_in
as
(
select base_list_mainkey,base_list_name,sum(number)number,unit
from store_in
group by base_list_mainkey,base_list_name,unit );
Create view view_store_out
as
select base_list_mainkey,base_list_name,sum(number)number,unit
from store_out
group by base_list_mainkey,base_list_name,unit ;
//以下天池没有
alter table business_list
add client_ip varchar(28),client_host varchar(28)
//07-05-31
//是否临时品项
alter table Base_list
add tmp_item_flag char(1) default 'N'
UPDATE Base_list
SET tmp_item_flag = 'N'
//加单明细里临时品项标识
alter table business_list
add tmp_item_flag char(1) default 'N'
//记次卡
alter table CRM_BASE
add timer integer default 0
//记次卡每次消息记录
Create Table business_card_timer(
Mainkey int identity primary key,
blno varchar(28) Not Null,
card_no varchar(28),
timer integer,
cur_timer integer,
create_user varchar(28),
create_name varchar(28),
create_timer datetime );
//记次卡参照表（保存）
Create table crm_timer_item(
item_id Integer Not Null Primary Key,
item_name varchar(28)
);
//前台会员卡信息
Create Table crm_info_front(
Mainkey int identity primary key,
regist_date datetime,
property varchar(10),
card_no varchar(28) not null,
old_card_no varchar(28),
custom_name varchar(28),
tel varchar(50),
sex char(2),
handle_sb varchar(28),
registrant varchar(28),
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime,
memo varchar(28));
//是否上钟
alter table CORP_DEPARTMENT
add up_clock char(1) default 'N';
UPDATE Base_list
SET inner_price = pre_price
WHERE (LEFT(name, 2) = '内卖')
//会员卡有效期
alter table crm_info_front
add validity_date datetime
//add lvxz 2007-06-03
//增加索引
CREATE INDEX [IX_Base_list] ON [dbo].[Base_list]([parent])
//会员卡折扣品项
Create Table CRM_ITEM_remission(
Mainkey integer identity primary key,
belncompany integer,
CARD_typeid integer,
item_id integer,
item_name varchar(28),
Is_valid char(1) default 'Y',
memo varchar(200),
Create_user varchar(28),
Create_name varchar(28),
Create_time datetime
);
//服务员
alter table business_list
add sale_sb varchar(28)
//记次
Create table business_crm_timer(
Mainkey integer identity primary key,
Business_Day datetime,
card_no varchar(28),
blno varchar(28),
pay_blno varchar(28),
create_user varchar(28),
create_name varchar(28),
create_time datetime
);
CREATE
INDEX [IX_CRM_ITEM_remission] ON [dbo].[CRM_ITEM_remission] ([CARD_typeid], [Is_valid])
--增加开单员代码，开单员名称，收银员代码，收银员名称，
alter table business_head
add Create_user varchar(28),Create_name varchar(28),cash_user varchar(28),cash_name varchar(28)
alter table business_head
add open_pos varchar(28),open_pos_ip varchar(28),pay_pos varchar(28),pay_pos_ip varchar(28)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc Pro_quantity_artificerID
@pay_datebg varchar(28),
@pay_dateed varchar(28),
@fyr varchar(28)
as
declare @sql nvarchar(1000)
--先添加头部！
set @sql='select report_date 营业日, artificerID 技师编号'
if @fyr is null or @fyr = ''
begin
--print '服务员项为空'
set @fyr = ''
end
if @pay_datebg is null or @pay_datebg = ''
begin
--print '交班日期为空'
set @pay_datebg = '1900-01-01'
end
--动态构造，仿造红色部分
if @pay_datebg = '1900-01-01' and @fyr = ''
begin
--print '=======查询全部数据============'
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' and Is_move ='N' ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' group by report_date,artificerID '
end
else
begin
if len(@fyr)>0 and @pay_datebg != '1900-01-01'
begin
--print '==================='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And artificerID=@fyr and (report_date>=@pay_datebg and report_date<=@pay_dateed)) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and artificerID ='''+@fyr+''' And (report_date>='''+@pay_datebg+''' and report_date<='''+@pay_dateed+''') group by report_date,artificerID '
end
else
begin
if len(@fyr)>0
begin
--print '========只查询服务员所有品英==========='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And artificerID=@fyr ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and artificerID ='''+@fyr+''' group by report_date,artificerID '
end
if @pay_datebg != '1900-01-01'
begin
--print '========只查询指定日期所有==========='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And (report_date>=@pay_datebg and report_date<=@pay_dateed) ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and (report_date >='''+@pay_datebg+''' and report_date <='''+@pay_dateed+''') group by report_date,artificerID '
end
end
end
/*
if len(@fyr)>0
begin
print '==================='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' And artificerID=@fyr ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and artificerID ='+@fyr+' group by report_date,artificerID '
end
else
begin
print '-------------------'
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' group by report_date,artificerID '
end
*/
--print @sql
set @sql = @sql + ' order by report_date,artificerID '
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc Pro_quantity_serviceId
@pay_datebg varchar(28),
@pay_dateed varchar(28),
@fyr varchar(28)
as
declare @sql nvarchar(1000)
--先添加头部！
set @sql='select report_date 营业日, serviceId 服务员编号'
if @fyr is null or @fyr = ''
begin
--print '服务员项为空'
set @fyr = ''
end
if @pay_datebg is null or @pay_datebg = ''
begin
--print '交班日期为空'
set @pay_datebg = '1900-01-01'
end
--动态构造，仿造红色部分
if @pay_datebg = '1900-01-01' and @fyr = ''
begin
--print '=======查询全部数据============'
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' and Is_move ='N' ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' group by report_date,serviceId '
end
else
begin
if len(@fyr)>0 and @pay_datebg != '1900-01-01'
begin
--print '==================='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And serviceId=@fyr and (report_date>=@pay_datebg and report_date<=@pay_dateed)) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and serviceId ='''+@fyr+''' And (report_date>='''+@pay_datebg+''' and report_date<='''+@pay_dateed+''') group by report_date,serviceId '
end
else
begin
if len(@fyr)>0
begin
--print '========只查询服务员所有品英==========='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And serviceId=@fyr ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and serviceId ='''+@fyr+''' group by report_date,serviceId '
end
if @pay_datebg != '1900-01-01'
begin
--print '========只查询指定日期所有==========='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y'and Is_move ='N' And (report_date>=@pay_datebg and report_date<=@pay_dateed) ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and Is_move =''N'' and (report_date >='''+@pay_datebg+''' and report_date <='''+@pay_dateed+''') group by report_date,serviceId '
end
end
end
/*
if len(@fyr)>0
begin
print '==================='
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' And serviceId=@fyr ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' and serviceId ='+@fyr+' group by report_date,serviceId '
end
else
begin
print '-------------------'
select @sql = @sql+',sum(case base_list_name when '''+base_list_name+''' then number else 0 end) '+base_list_name+''
--只要能完成distinct子查询，就能查询餐券日售出数量
from (select distinct base_list_name from dbo.business_list where pay_up ='Y' ) a
--最后添加尾部
set @sql= @sql+',sum(number) 合计 from business_list where pay_up=''Y'' group by report_date,serviceId '
end
*/
--print @sql
set @sql = @sql + ' order by report_date,serviceId '
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--add lvxz 2007-06-09
--会员卡最后消费的营业日
alter table crm_base
add business_date datetime
--套票明细
create View view_BASE_COPY_LIST
AS
SELECT
BASE_COPY_LIST.Mainkey,
BASE_COPY_LIST.belncompany,
BASE_COPY_LIST.Parent,
BASE_COPY_LIST.Is_master,
BASE_COPY_LIST.Base_listMK,
BASE_COPY_LIST.name,
BASE_COPY_LIST.unit,
BASE_COPY_LIST.Number,
BASE_COPY_LIST.unprice,
BASE_COPY_LIST.discount_prc,
Number * unprice*(case discount_prc
when 0 then 1
--when NULLIF(discount_prc,0) then 1
else discount_prc
end )as real_price,
BASE_COPY_LIST.serv_price,
BASE_COPY_LIST.artificer_price,
BASE_COPY_LIST.Is_freeItem,
BASE_COPY_LIST.Is_valid,
BASE_COPY_LIST.Create_user,
BASE_COPY_LIST.Create_time
FROM BASE_COPY_LIST
where is_valid = 'Y'
//2007-06-15 对招待进行权限控制
alter table Security_button
add Is_Hospitality_fee char(1) default 'N'
update Security_button
set Is_Hospitality_fee = 'N'
//餐厅桌号
Create table Base_Restaurant_number(
Mainkey int identity primary key,
belncompany int,
code varchar(28),
name varchar(28),
state char(1) default '1',
is_valid char(1) default 'Y',
memo varchar(100)
)
//餐桌状态
Create table Restaurant_number_state(
Mainkey int identity primary key,
rblno varchar(28),
team_blno varchar(28),
is_team char(1),
desk_id int,
desk_code varchar(28),
blno varchar(28),
sign_mainkey int,
sign_number varchar(28),
state char(1) default 'N',
open_pos varchar(28),
open_pos_ip varchar(28),
day_blno varchar(28),
Report_date datetime,
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime
)
CREATE
INDEX [IX_Restaurant_number_state] ON [dbo].[Restaurant_number_state] ([blno], [state])
WITH
DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [IX_Restaurant_number_state_1] ON [dbo].[Restaurant_number_state] ([desk_id], [desk_code])
WITH
DROP_EXISTING
ON [PRIMARY]
//当前数据来自餐厅还是洗浴
ALTER TABLE BASE_area ADD general varchar(28)
alter table business_list
add general varchar(28)
//小时，分钟标示
alter table Hotel_room_discount
add unit varchar(2)
//房间管理
Create table rooms(
Mainkey int identity primary key,
belncompany int,
code varchar(28),
name varchar(28),
bed_number int default 1,
is_valid char(1) default 'Y',
unprice decimal(12,2),
memo varchar(28),
create_user varchar(28),
create_name varchar(28),
Create_time datetime)
go
Create table rooms_dt(
mainkey int identity,
rooms_bk int,
number int,
state char(1) default('N')
create_user varchar(28),
create_name varchar(28),
Create_time datetime)
Create table rooms_open(
Mainkey int identity primary key,
rooms_blno varchar(28),
team_blno varchar(28),
is_team char(1),
rooms_bk int,
rooms_code,
rooms_bed int,
blno varchar(28),
sign_mainkey int,
sign_number varchar(28),
state char(1) default 'N',
open_pos varchar(28),
open_pos_ip varchar(28),
day_blno varchar(28),
Report_date datetime,
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime
)
//单价
alter table rooms_open
add unprice decimal(12,2)
//员工特长
// add lvxz 06-20
Create Table CORP_EMPLOYEES_specialty(
Mainkey int identity,
em_code varchar(20)not null,
item_bk int not null,
item_name varchar(28),
is_valid char(1) default 'Y',
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime
)
ALTER TABLE [dbo].[CORP_EMPLOYEES_specialty] WITH NOCHECK ADD
CONSTRAINT [PK_CORP_EMPLOYEES_specialty] PRIMARY KEY CLUSTERED
(
[em_code],
[item_bk]
) ON [PRIMARY]
GO
//员工表的数据
alter table CORP_EMPLOYEES
add Create_user varchar(28),
Create_name varchar(28),
Create_time datetime;
//客人预约
Create table Guests_booking(Mainkey int identity Primary Key,
blno varchar(28),
guests_name varchar(28),
guests_tel varchar(28),
booking_time datetime,
booking_room varchar(28),
booking_item varchar(28),
emp_code varchar(28),
is_valid char(1) default 'Y',
is_state char(1) default 'N',
create_user varchar(28),
Create_name varchar(28),
Create_time datetime
);
//2007-06-21
//本地库存表
Create TABLE [dbo].[Store_out_local] (
[Mainkey] [int] IDENTITY (1, 1) NOT NULL ,
[blno] [varchar] (28) COLLATE Chinese_PRC_CI_AS ,
[Base_list_mainkey] [int] ,
[Base_list_name] [varchar] (28)Not NULL ,
user_name varchar(25),
dept_name varchar(25),
[number] [int] default 1 ,
[unit] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_user] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_name] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_Time] [datetime] NULL ,
local_ip varchar(28),
local_host varchar(28),
[Memo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL,
primary key (blno,Base_list_name)
) ON [PRIMARY]
GO
//add lvxz 2007-06-22
//本地库只引用一个表，用此标示显示是入库还是出库
alter table dbo.Store_out_local
add Action varchar(8)
CREATE
INDEX [IX_Store_out_local] ON [dbo].[Store_out_local] ([Action])
//本地库视图
Create View Store_local_I
as
SELECT
Store_out_local.Base_list_mainkey,
Store_out_local.Base_list_name,
Store_out_local.dept_name,
SUM(Store_out_local.number)number,
Store_out_local.unit
FROM Store_out_local
WHERE Store_out_local.Action ='I'
GROUP BY Store_out_local.Base_list_mainkey,Store_out_local.dept_name,
Store_out_local.Base_list_name, unit
Create View Store_local_E
as
SELECT
Store_out_local.Base_list_mainkey,
Store_out_local.Base_list_name,
Store_out_local.dept_name,
SUM(Store_out_local.number)number,
Store_out_local.unit
FROM Store_out_local
WHERE Store_out_local.Action ='E'
GROUP BY Store_out_local.Base_list_mainkey,Store_out_local.dept_name,
Store_out_local.Base_list_name, unit
//库存标示
alter table business_list
add store_flag char(1) default 'N';
//员工基本工资
alter table dbo.CORP_EMPLOYEES
add base_pay float
//员工考勤记录表
Create Table CORP_EMPT_SIGN(
Mainkey int identity primary key,
emp_code varchar(28),
emp_name varchar(28),
report_date Datetime,
plan_work_time Datetime,
plan_off_time Datetime,
work_time Datetime,
off_time Datetime,
Memo varchar(100)
)
//员工请假、销假
Create Table CORP_EMPT_ASK_LEAVE
(Mainkey int identity Primary Key,
Report_date DateTime,
emp_code varchar(28),
emp_name varchar(28),
ask_time datetime,
return_time datetime,
Memo varchar(28),
Ask_reg_user varchar(28),
Return_reg_user varchar(28),
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime
)
//add lvxz 20067-06-23
//请假类型
alter table CORP_EMPT_ASK_LEAVE
add holiday_type varchar(28)
--工种设置
Create Table Base_type_work(
Mainkey int Identity Primary Key,
Code varchar(28),
Name Varchar(28),
Is_valid char(1) Default 'Y',
is_turn char(1) default 'N',
Memo Varchar(100),
Create_User Varchar(28),
Create_Name Varchar(28),
Create_Time DateTime
)
--技师排钟表
Create Table Business_pz_seq(
Mainkey int identity Primary Key,
day_blno varchar(28),
report_date datetime,
emp_code varchar(28),
number float,
seq int,
Create_user Varchar(28),
Create_name Varchar(28),
Create_time datetime
)
//让所有员工状态为岗状态
UPDATE CORP_EMPLOYEES
SET Status = 1
//排钟序号,是否上钟
CREATE
INDEX [IX_CORP_EMPT_SIGN] ON [dbo].[CORP_EMPT_SIGN] ([is_work], [off_time])
alter table CORP_EMPT_SIGN
add is_work char(1) default 'N',seq_ref int default 0
--2007-06-24
--是否点钟
Alter table dbo.business_list
Add Is_point_tech char(1) default 'N'
--是否内卖
Alter table dbo.business_list
Add Is_inner char(1) default 'N'
--是否点钟
--1表示加钟
--2表示点钟
Alter table dbo.business_list
Add item_state char(1) default '0'
--是否内卖
Alter table dbo.business_list
Add Is_inner char(1) default 'N'
//交班库存
Create table Store_daily_book(
Mainkey int identity Primary key,
day_blno varchar(28),
business_day DateTime,
belncompany int,
[Base_list_mainkey] [int] NOT NULL ,
[Base_list_name] [varchar] (28) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[local_ip] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[local_host] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[dept_name] [varchar] (25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[number] [int] NOT NULL ,
[unit] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_user] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_name] [varchar] (28) COLLATE Chinese_PRC_CI_AS NULL ,
[Create_Time] [datetime] NULL
)
--ADD LVXZ 2007-06-25
--对于点钟的处理
Alter Table CORP_EMPT_SIGN
ADD FRONT_POS INTEGER,FRONT_STRING VARCHAR(28);
--加钟提成
Alter Table Base_list
Add Add_H_price float
--ADD LVXZ 2007-06-25
alter table Store_out_local
add report_date DateTime
//上一个技师位置
//下一技师位置
alter table corp_empt_sign
add pre_seq integer,next_seq integer;
--是否加钟标示
alter table business_list
add Plus_bell char(1) default 'N' ;
-- add lvxz 2007-06-26
--加钟提成
alter table business_list
add Add_H_price float default 0;
update business_list
set plus_bell ='N';
--实际钟长
alter table business_list
add reality_count_length float;
CREATE
INDEX [IX_business_list_1]
ON [dbo].[business_list] ([belncompany], [Report_date], [pay_up],Is_move, [artificerID], [terminal_time])
--更改数据类型0非加钟,1为加钟
update
set plus_bell=0
alter table business_list
alter column plus_bell integer
--2007-7-01
--销售提成
alter table Base_list
add sale_price float default 0;
alter table business_list
add sale_price float default 0
--add lvxz 2007-07-02
--统计商品收入，及服务收入
alter table business_head
add Commodity_earnings float default 0,
Service_income float default 0;
--add lvxz 2007-07-03
--增加对翻译的提成设置
alter table base_list
add translation_income float default 0;
alter table business_list
add translation_income float default 0;
--是否在班上
alter table CORP_EMPT_SIGN
add is_off_time char(1) default 'N';
--add lvxz 2007-07-05
--增加对翻译的提成设置
--翻译提成放到头表里比较好，不可能每个项目都有翻译提成
alter table business_head
add translation_income float default 0;
--add lvxz 2007-06-20 对会员卡信息的描述与备注不同
Alter Table crm_base
Add CardInfo varchar(200);
--add lvxz 2007-07-23 增加索引
CREATE
INDEX [IX_business_list_2] ON
[dbo].[business_list] ([base_listID], [blno], [pay_up], [Is_del], [Is_move])
WITH
DROP_EXISTING
ON [PRIMARY]
//客人简称
Alter table CRM_BASE
add Short_name varchar(28);
//手牌对照码
ALTER TABLE BASE_SIGN_NUMBER
ADD Control_code VARCHAR(28) DEFAULT '';
CREATE
INDEX [IX_Base_Sign_number_1] ON [dbo].[Base_Sign_number] ([Control_code])
WITH
DROP_EXISTING
ON [PRIMARY]CREATE
INDEX [IX_Base_Sign_number_1] ON [dbo].[Base_Sign_number] ([Control_code])
WITH
DROP_EXISTING
ON [PRIMARY]
--add lvxz 2007-08-02
--四舍五入值
Alter table business_head
add Rounding_Dec dec(12,2) default 0;
--add lvxz 2007-08-03仓库设置
Create Table Store_Set(
Code varchar(28) Not Null Primary Key,
Name varchar(68),
Create_user varchar(28),
Create_name varchar(28),
Create_time datetime default getdate() )
--开牌时间
Alter Table Base_Sign_number
Add Stay datetime;
--add lvxz 08-06 供货商表
Create Table Store_consignor_info(
Mainkey int identity primary key,
code varchar(28),
name varchar(68),
Short_name varchar(28),
link_name varchar(28),
Tel varchar(38),
address varchar(68),
is_default varchar(1) default 'N',
memo varchar(100))
Create Table Store_in_H(
blno varchar(28) not Null Primary Key,
consignor_code varchar(28),
store_code varchar(28),
blno_date DateTime,
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime default getdate(),
yf_money dec(12,2) default 0,
sf_money dec(12,2) default 0,
Memo Varchar(100)
)
--重新设置库存
--2008-08-10
Create Table Store_in_DT(
blno varchar(28) Not Null,
item_bk int Not Null,
item_name varchar(28),
unit varchar(8),
un_price dec(12,2) default 0,
number dec(12,2) default 0,
Create_user varchar(28),
Create_name varchar(28),
Create_time DateTime default getdate(),
)
ALTER TABLE [dbo].[Store_in_DT] ADD
CONSTRAINT [DF__Store_in___un_pr__5BCD9859] DEFAULT (0) FOR [un_price],
CONSTRAINT [DF__Store_in___numbe__5CC1BC92] DEFAULT (0) FOR [number],
CONSTRAINT [DF__Store_in___Creat__5DB5E0CB] DEFAULT (getdate()) FOR [Create_time],
CONSTRAINT [PK_Store_in_DT] PRIMARY KEY CLUSTERED
(
[blno],
[item_bk]
) ON [PRIMARY]
GO
--2007-08-20
CREATE INDEX [IX_business_head_3] ON [dbo].[business_head]([blno], [pay_up])
CREATE
INDEX [IX_business_list_4] ON [dbo].[business_list] ([blno], [Is_move], [pay_up], [Is_del])
CREATE
INDEX [IX_business_cash_pledge] ON [dbo].[business_cash_pledge] ([blno], [Is_valid])
--add lvxz 2007-09-03 增加初级提成中级提成高级提成
Alter Table Base_list
add first_grade dec(12,2) default 0,
middle_grade dec(12,2) default 0,
high_grade dec(12,2) default 0
Go
--add lvxz 2007-09-06
Create Table Business_total_day(
Mainkey int identity primary key,
report_date datetime,
day_blno varchar(28),
P_M integer ,
P_W integer,
O_M integer,
O_W integer,
flag char(1),
Create_user varchar(28),
Create_time datetime default getdate()
)
GO
--翻译员工
ALTER TABLE business_list ADD translation_user varchar(28);
GO
--卡型记次信息
Create Table BASE_CARD_number_set
(
Mainkey int identity primary key,
belncompany int,
typeid int,
item_bk int,
item_bkname varchar(28),
num dec(12,2) ,
Is_valid char(1) default 'Y',
memo varchar(100),
Create_user varchar(28),
Create_name varchar(28),
Create_time datetime default getdate(),
pos_ip varchar(28),
pos_host varchar(28)
)
Go
--记次记录表
Create Table CRM_CARD_number
(
Mainkey int identity primary key,
belncompany int,
Card_no varchar(28),
item_bk int,
item_bkname varchar(28),
num dec(12,2)default 0 ,
num_del dec(12,2)default 0,
Create_user varchar(28),
Create_name varchar(28),
Create_time datetime default getdate()
)
GO
--2007-09-07开牌，结帐时，加上操作员
alter table base_sign_number
add client_user varchar(28)
--是否是主库
Alter Table Store_Set
Add is_master char(1) default 'N'
--库存申请模块,手工为第二个表创建主键
CREATE TABLE [Store_in_App_H] (
[blno] [varchar] (28) NOT NULL Primary Key,
[store_code] [varchar] (28) NULL ,
[blno_date] [datetime] NULL ,
flag char(1) default 'N',
[Create_user] [varchar] (28) NULL ,
[Create_name] [varchar] (28) NULL ,
[Create_time] [datetime] DEFAULT (getdate()),
[Memo] [varchar] (100) NULL
)
GO
Create TABLE [Store_in_App_H_DT] (
[blno] [varchar] (28) NOT NULL ,
[item_bk] [int] NOT NULL ,
[item_name] [varchar] (28) NULL ,
[unit] [varchar] (8) NULL ,
[un_price] [decimal](12, 2) NULL DEFAULT (0),
[number] [decimal](12, 2) NULL DEFAULT (0),
flag char(1) default 'Y',
[Create_user] [varchar] (28) NULL ,
[Create_name] [varchar] (28) NULL ,
[Create_time] [datetime] NULL DEFAULT (getdate())
)
GO
ALTER TABLE Store_in_App_H_DT ADD
PRIMARY KEY (blno, item_bk));
CREATE UNIQUE CLUSTERED
INDEX [PK_Store_in_App_H_DT] ON [dbo].[Store_in_App_H_DT] ([blno], [item_bk])
--出入库标示0入库，１出库
ALTER TABLE Store_in_H ADD IO char(1)
DEFAULT '0'
--库存视图
Create View view_store_in_new
as
SELECT item_bk,item_name,unit,sum(number)number
FROM Store_in_H a,Store_in_dt b
where a.io ='0' and a.blno = b.blno
group by item_bk,item_name,unit
Create View view_store_out_new
as
SELECT item_bk,item_name,unit,sum(number)number
FROM Store_in_H a,Store_in_dt b
where a.io ='1' and a.blno = b.blno
group by item_bk,item_name,unit
alter table Store_in
add store_code varchar(28)
alter table Store_out
add store_code varchar(28)
alter table Store_out
add report_date datetime
alter table Store_out
add local_ip varchar(28),local_host varchar(28)
--创建索引2007-09-09
CREATE
INDEX [IX_business_head_4] ON [dbo].[business_head] ([Team_blno], [pay_up])
--2007-09-11 营业日
alter table dbo.Store_in
Add report_date datetime
Go
--2007-09-12 增加备注，记录哪些流水号
alter table business_total_day
add memo varchar(100);
Go
--2007-09-13 增加开牌女宾人数
Alter Table business_head
add womannumber integer default 0;
Go
--吧台库存
Create View View_store_bar
As
select A.store_code,A.base_list_mainkey,A.base_list_name,A.unit,A.number_in,B.number_out from(
select store_code,base_list_mainkey,base_list_name,unit,sum(number)number_in from store_in
group by base_list_mainkey,base_list_name,unit,store_code)A,(
select store_code,base_list_mainkey,base_list_name,unit,sum(number)number_out from store_out
group by store_code,base_list_mainkey,base_list_name,unit)B
WHERE A.base_list_mainkey *= B.base_list_mainkey And A.store_code = B.store_code
Go
--点钟时初中级提成
Alter Table Base_list
add sp_first_grade dec(12,2) default 0,
sp_middle_grade dec(12,2) default 0,
sp_high_grade dec(12,2) default 0
Go
--次卡时显示子卡
alter table CRM_integral_date
add card_no_child varchar(28)
阅读(2655) | 评论(0) | 转发(0) |
