TXT

p_cyj_pos_bill_gen_detail

By Anne Gray,2014-04-13 08:32
6 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

     select @total2 = @total2 + @min_charge

     end

     end

     else

     begin

     -- --打印以前的账单, 取自 pos_hdish, 不过滤套菜明细

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

     insert #dish select *, '' from pos_hdish where menu = @menu order by inumber

     delete #dish where menu = @menu and (charindex(sta, '03579M') = 0 or charindex(rtrim(code),'YZ') > 0)

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

     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_hdish where menu = @menu and rtrim(code) = 'X'

     end

     end

    --要不要打印零头

    delete #dish where sta = 'A' and special = 'T'

    select @dsc_rate = dsc_rate, @srv_rate = serve_rate, @tax_rate = tax_rate from #menu update #dish set mode_flag = mode_flag+'*' where exists(select 1 from pos_mode_def a,#menu b

     where a.code = b.mode and a.type = '1' and (a.mode = 'C' or a.mode = 'G') and a.rate=0

     and a.plucode = (select max(plucode) from pos_mode_def where type='1' and code = b.mode

     and #dish.sort+#dish.code like rtrim(plucode) + '%'))

    update #dish set mode_flag = rtrim(mode_flag)+'#' where exists(select 1 from pos_mode_def a,#menu b

     where a.code = b.mode and a.type = '2' and a.mode = 'A' and a.rate=0

     and a.plucode = (select max(plucode) from pos_mode_def where type='2' and code = b.mode

     and #dish.sort+#dish.code like rtrim(plucode) + '%'))

    -- 套菜明细明细不打印

    if @stdprint <> 'T'

     delete #dish where sta= 'M'

    -- begin

    if substring(@code, 1, 2) = '61' -- --明细打单

     begin

     insert #bill(menu, inumber, code, name1, name2, number, unit, price,amount, status, log_date, sta, sort, id_master,srv,dsc,mode_flag)

     select distinct menu, min(inumber), code, name1, name2, sum(number), unit, price,sum(amount), 0, getdate(), sta, min(inumber),

    id_master,sum(srv),sum(dsc),mode_flag

     from #dish

     where special <> 'X'

     group by menu, code, name1, name2, unit, sta,price, id_master,mode_flag

     order by menu, code, name1, name2, unit, sta,price, id_master,mode_flag

     -- 折扣

     if datalength(@menus) < 15

     begin

     -- 接打;如果没有点新菜;不要打印折扣, 但是折扣有修改还是要打印

     select @tmpdsc2 = sum(dsc) from #menu

     if @multi <> 'T' or @inumber <> @dish_inumber or @tmpdsc1 <> @tmpdsc2

     insert #bill(menu, inumber, code, empno, name1,name2, number, unit, amount, status, log_date, sort, id_master)

    -- select a.menu, 0, '', '', '折扣['+ convert(char(2), convert(int,

    @dsc_rate * 100)) +'%]', 'Dsc', 1, '',@tmpdsc1 - @tmpdsc2,40, getdate(), 4000, 0

     select a.menu, 0, '', '', '折扣', 'Dsc', 1, '',@tmpdsc1 - @tmpdsc2,40, getdate(), 4000, 0

     from #menu a, pos_mode_name b where a.mode = b.code and @tmpdsc1 <> @tmpdsc2

     end

     else

     begin

     -- 联单结账

     select @tmpdsc2 = sum(dsc) from #menu

     if @multi <> 'T' or @inumber <> @dish_inumber or @tmpdsc1 <> @tmpdsc2

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

    -- select @pcrec, 0, '', '', a.tableno +'折扣['+ convert(char(2),

    convert(int, @dsc_rate * 100)) +'%]', 'Dsc', 1, '', @tmpdsc1 - @tmpdsc2 , 40, a.date0, 4000, 0

     select @pcrec, 0, '', '', a.tableno +'折扣', 'Dsc', 1, '', @tmpdsc1

    - @tmpdsc2 , 40, a.date0, 4000, 0

     from #menu a where a.menu = @pcrec and @tmpdsc1 <> @tmpdsc2

     end

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '',

    '-------------------------------','--------------------------', 0, '', 0, 15, getdate(), 1450, 0

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '赠送小计','赠送小计', 0, '', isnull(sum(amount), 0),

    15, getdate(), 1460, 0

     from #bill a where a.status = 0 and a.sta = '3' having sum(amount) <> 0 --for baiyun 要计算茶位费

     -- --赠送特殊处理

     update #bill set name1 = substring(rtrim('[]' + name1), 1, 60), amount = 0 where sta = '3'

     -- --全免特殊处理

     update #bill set name1 = substring(rtrim('[]' + name1),1, 60), amount = 0 where sta = '5'

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '应收小计','Sum', 0, '', isnull(sum(amount), 0), 15, getdate(), 1500, 0

     from #bill a where a.status = 0 and not code like '[YZ]%' and a.sta <>'M' --for baiyun 要计算茶位费

     -- 将服务费, 附加费合并后打印在小计后面

     -- 服务费

     select @tmpsrv2 = sum(srv) from #menu

     if exists(select 1 from #dish where charindex(rtrim(code),'YZ') = 0)

     insert #bill(menu, inumber, code, empno, name1,name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '服务费[' + convert(char(2), convert(int,

    @srv_rate * 100)) +'%]', 'Serve', 0, '', round(isnull(sum(srv), 0),2), 20, getdate(), 2000, 0

     from #dish a where charindex(rtrim(code), 'YZ') = 0 having sum(a.srv) <> 0

     else if @tmpsrv1 <> @tmpsrv2 -- 如果没有点菜;服务费有变化

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '服务费[' + convert(char(2), convert(int,

    @srv_rate* 100)) +'%]', 'Serve', 0, '', round(@tmpsrv2 - @tmpsrv1, 2), 20, getdate(), 2000, 0

     if exists(select 1 from #dish where special = 'U')

     begin

     select @noserve = 0

     declare c_dish_no_srv cursor for

     select a.deptno, a.pccode, a.mode, b.sort+b.code,b.amount,b.amount - b.dsc,a.serve_rate

     from #menu a, #dish b where a.menu = b.menu and b.special = 'U'

     open c_dish_no_srv

     fetch c_dish_no_srv into

    @deptno,@pccode,@mode,@code1,@amount0,@amount,@serve_rate

     while @@sqlstatus = 0

     begin

     exec p_gl_pos_create_serve

     @deptno,@pccode,@mode,@code1,@amount0,@amount,@serve_rate,@result0 = @serve_charge0 output,@result =@serve_charge output

     select @noserve = @noserve + @serve_charge

     fetch c_dish_no_srv into

    @deptno,@pccode,@mode,@code1,@amount0,@amount,@serve_rate

     end

     close c_dish_no_srv

     deallocate cursor c_dish_no_srv

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '免服务费', 'Serve Dsc', 1, '', round(@noserve, 2), 20, getdate(), 2010, 0

     end

     -- 附加费

     select @tmptax2 = sum(tax) from #menu

     if exists(select 1 from #dish where charindex(rtrim(code), 'YZ') = 0)

     insert #bill(menu, inumber, code, empno,name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '附加费[' + convert(char(2), convert(int,

    @tax_rate * 100)) +'%]', 'Tax', 1, '',round(isnull(sum(tax), 0),2), 30, getdate(), 3000, 0

     from #dish a where charindex(rtrim(code), 'YZ') = 0 having sum(a.tax) <> 0

     else if @tmptax1 <> @tmptax2 -- 如果没有点菜;税有变化

     insert #bill(menu, inumber, code, empno, name1, name2, number, unit, amount, status, log_date, sort, id_master)

     select '', 0, '', '', '附加费[' + convert(char(2), convert(int,

    @tax_rate * 100)) +'%]', 'Tax', 1, '',round(@tmptax2 - @tmptax1,2), 30, getdate(), 3000, 0

     end

    else if substring(@code, 1, 2) = '62' -- -- 汇总账单

     begin

     --汇总打印 #dish 再插入 将服务费等插入

    -- delete #dish

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

     if @hline= 0 or @multi <> 'T' -- 新单

     begin

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount, status, log_date, sort, id_master)

     select substring(@menus, 1, 10), 0, b.code, '01', b.descript, 1, '01', sum(amount), 0, getdate(), 0, 0

     from #dish a, pos_deptcls b

     where a.sort like rtrim(b.deptpat) + '%' and b.code in ('0','1','2','3','4','5','6','7','8','9')

     group by b.code, b.descript

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount, status,log_date, sort, id_master)

     select substring(@menus, 1, 10),0, 'Y', '01','服务费-SERVICE',

    0, '01', sum(srv + tax), 0, getdate(), 10, 0

     from #dish a

     where charindex(rtrim(ltrim(a.code)), 'YZ')=0

     having sum( srv + tax )<>0

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount, status, log_date, sort, id_master)

     select substring(@menus, 1, 10), 0, 'Z', '01','折扣-DISCOUNT',

    0, '01', - sum(dsc), 0, getdate(), 15, 0

     from #dish a

     where charindex(rtrim(ltrim(a.code)), 'YZ')=0

     having sum( dsc )<>0

     end

     else -- 老单

     begin

     if exists(select 1 from #dish)

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount, status, log_date, sort, id_master)

     select substring(@menus, 1, 10), 0, b.code, '01', b.descript, 1, '01', sum(amount), 0, getdate(), 0, 0

     from #dish a, pos_deptcls b

     where a.sort like rtrim(b.deptpat) + '%' and b.code in ('0','1','2','3','4','5','6','7','8','9')

     group by b.code, b.descript

     -- srv,dsc累计数比较

     select @tmpdsc2 = sum(dsc) from pos_dish where charindex(menu, @menus)>0 and charindex(sta, '03579MA') > 0

     select @tmpsrv2 = sum(srv) from pos_dish where charindex(menu, @menus)>0 and charindex(sta, '03579MA') > 0

     if @tmpsrv1 <> @tmpsrv2 -- 服务费差额

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount, status, log_date, sort, id_master)

     select substring(@menus, 1, 10), 0, 'Y', '01','服务费

    -SERVICE', 1, '01', @tmpsrv2 - @tmpsrv1, 0, getdate(), 15, 0

     if @tmpdsc1 <> @tmpdsc2 -- 折扣差额

     insert #bill(menu, inumber, code, empno, name1, number, unit, amount,

Report this document

For any questions or suggestions please email
cust-service@docsford.com