TXT

p_cyj_pos_bill_gen_detail

By Anne Gray,2014-04-13 08:32
7 views 0
p_cyj_pos_bill_gen_detail

    drop proc p_cyj_pos_bill_gen_detail; create proc p_cyj_pos_bill_gen_detail

     @menus varchar(255),

     @today char(1), -- T:打印当天的账单F:打印以前的账单

     @paid char(1), -- T:结账打单 F:非结账打

     @multi char(1), -- T:在原帐单上打印

     @code char(10), -- 2位为打印类型;第3位为是否打印套菜明细;

     -- 4位为是否打印套菜明细单价;后2位为中英文

     @pc_id char(4),

     @empno char(10) = '' -- 打印人

    as

    --------------------------------------------------------------------------------

    ------------------

    -- 餐饮账单 --- X5

    -- 说明?1、赠送(dish.sta= '3')、全免(dish.sta= '5'), 方法要根据饭店实际要求而定

    -- 一般处理方法?金额不为零, 金额记入折扣?金额为零, 金额不记入折扣 -- 2、折扣处理方法?A?每次都打印折扣总额?B?每次只打印新点的菜的折扣 -- 3、支持套菜特殊处理

    -- 5、支持中英文

    --------------------------------------------------------------------------------

    ------------------

    insert gdsmsg select "/"+ @menus+"/"+ @today+"/"+ @paid +"/"+

     @multi+"/"+ @code +"/"+ @pc_id +"/"

    --/0508270005/T/F/F/61FF_c /8.04/ --/0508290021/T/T/F/61FF_c /8.04/

declare

     @ls_menus varchar(255),

     @menu char(10),

     @min_charge money,

     @total0 money,

     @total1 money,

     @total2 money,

     @paymth varchar(255),

     @transfer varchar(255),

     @paycode char(5),

     @distribute char(4),

     @ld_amount money,

     @accnt varchar(10),

     @class char(1),

     @dec_length integer,

     @dec_mode char(1),

     @inumber int, -- 在一张账单上打印明细帐要记已

    打的pos_dish.inumber

     @dish_inumber int, -- dish 的最大inumber

     @hline int,

     @ii int,

     @amount decimal(10,2),

     @samount varchar(40),

     @deptno2 char(3),

     @dsc_rate money,

     @srv_rate money,

     @tax_rate money,

     @remark char(20),

     @stdprint char(1), -- 套菜明细是否要打印

     @stdprice char(1), -- 套菜单价是否要打印

     @sbal char(255),

     @tmpdsc1 money, -- 已经打印的折扣

     @tmpdsc2 money, -- 总折扣

     @tmpsrv1 money, -- 已经打印的服务费

     @tmpsrv2 money, -- 总服务费

     @tmptax1 money, -- 已经打印的税

     @tmptax2 money, -- 总税

     @tmptea1 money, -- 已经打印的茶位费

     @tmptea2 money, -- 总茶位费

     @add char(1), --是否加减菜

     @pcrec char(10),

     @reason char(3),

     @modedes char(60),

     @deptno char(2),

     @pccode char(3),

     @mode char(10),

     @code1 char(10),

     @serve_charge0 money,

     @serve_charge money,

     @serve_rate money,

     @noserve money,

     @amount0 money,

     @printtype char(10),

     @roomno char(6),

     @ref char(20),

     @consume_item varchar(60),

     @consume_code char(10),

     @paytype char(5),

     @descript char(20),

     @empno3 char(10),

     @foliono varchar(20)

select @printtype = printtype from bill_mode where code = substring(@code,1,2)

    if charindex(upper(@paid),'FT')=0

     select @paid = 'F'

    if charindex(upper(@today),'FT')=0

     select @today = 'T'

    if charindex(upper(@multi),'FT')=0

     select @multi = 'F'

select @stdprint = substring(@code, 3, 1), @stdprice = substring(@code, 4, 1)

    if @stdprint is null

     select @stdprint = 'T'

    if @stdprice is null

     select @stdprice = 'F'

    delete bill_data where pc_id = @pc_id select *,mode_flag=space(5) into #dish from pos_dish where 1=2

    select * into #menu from pos_menu where 1=2 create index index1 on #dish(code) create table #bill

    (

     menu char(10) not null, -- 主单号码

     inumber integer not null, -- ID

     code char(15) default ''not null, -- 代码

     empno char(3) default '' not null, -- 工号

     name1 char(60) default '' not null, -- 中文名称

     name2 char(60) null, -- 英文名称

     number money not null, -- 份量

     unit char(4) null, -- 单位

     price money default 0 not null, -- 单价

     amount money not null, -- 金额

     log_date datetime not null, -- 写盘时间

     status integer not null, -- 0.. 5.英文. 10. --. 15.小计. 20.服务费. 30.附加费.

     --

     40.折扣. 50.累计. 60.合计.

     -- 70.

    其中付款. 80.转账帐号余额

     sta char(1) default '0' null,

     sort integer not null, -- 用于排序

     id_master integer not null, -- 用于排序

     srv money default 0 not null,

     dsc money default 0 not null,

     mode_flag char(10) default '' null

    )

create table #checkout

    (

     paycode char(5) null, --付款方式

     amount money not null, --付款金额

     accnt char(10) null,

     roomno char(10) null,

     remark char(30) null, --零头去向,

    帐号,理由等

     ref char(255) null, --转账账户名称余额

     reason char(3)

    )

    select @ls_menus = @menus, @total0 = 0, @total1 = 0, @total2 = 0, @paymth = '', @transfer = '',@tmpdsc2 = 0, @tmpsrv2 = 0, @tmptax2 = 0,@tmptea2 = 0

-- --已打的序号

    select @pcrec = '', @menu = substring(@menus, 1, 10)

    if @today = 'T'

     select @pcrec = isnull(pcrec, '') from pos_menu where menu = substring(@menus, 1, 10)

    if @pcrec > ''

     select @menu = @pcrec

    if @multi = 'T'-- 在原帐单上打印

     select @inumber= inumber, @hline = hline,@tmpdsc1 = isnull(dsc, 0),@tmpsrv1 = isnull(srv, 0),@tmptax1 = isnull(tax, 0),@tmptea1 = isnull(tea, 0) from pos_menu_bill where menu = @menu

    else

     select @inumber= 0, @hline = 0,@tmpdsc1 = 0,@tmpsrv1= 0,@tmptax1 = 0,@tmptea1 = 0

if @inumber = null

     select @inumber = 0, @hline= 0

while datalength(@ls_menus) > 1

     begin

     select @menu = substring(@ls_menus, 1, 10), @ls_menus = substring(@ls_menus, 12,

255)

     if @today = 'T'

     -- -- 打印当天的账单, 取自 pos_dish, 不过滤套菜明细

     begin

     select @dish_inumber = max(inumber) from pos_dish where menu = @menu

     select @inumber= inumber from pos_menu_bill where menu = @menu

     if @multi = 'T'

     begin

     -- --被冲账可能已经打印;所以对冲销菜的id_cancel作判断. 被冲菜不打印

     insert #dish select *, '' from pos_dish where menu = @menu and (id_cancel = 0 or id_cancel <= @inumber) and charindex(sta,'1468') =0 and inumber > @inumber and charindex(rtrim(code), 'YZ') = 0 order by inumber

     select @total0 = @total0 + isnull(sum(amount), 0) from pos_dish where menu = @menu and sta ='3'

     select @total1 = @total1 + isnull(sum(amount), 0) from pos_dish where menu = @menu and sta ='5'

     select @tmptea2 = @tmptea2 + isnull(sum(amount), 0) from pos_dish where menu = @menu and rtrim(code) = 'X'

     end

     else

     begin

     -- --一次打单可以过滤所有冲账和被冲账

     insert #dish select *, '' from pos_dish where menu =@menu and charindex(sta, '03579MA') > 0 and charindex(rtrim(code), 'YZ') = 0order by inumber

     select @total0 = @total0 + isnull(sum(amount), 0) from pos_dish where menu = @menu and sta ='3'

     select @total1 = @total1 + isnull(sum(amount), 0) from pos_dish where menu = @menu and sta ='5'

     select @tmptea2 = @tmptea2 + isnull(sum(amount), 0) from pos_dish where menu = @menu and rtrim(code) = 'X'

     end

     insert #menu select * from pos_menu where menu = @menu

     -- --最低消费

     if @paid = 'F'

     begin

     exec p_gl_pos_create_min_charge @menu, @min_charge out, 'R', 0

     if @min_charge != 0