Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[捷信达]技师钟数明细表(汇总表) #74

Open
landv opened this issue Sep 6, 2023 · 0 comments
Open

[捷信达]技师钟数明细表(汇总表) #74

landv opened this issue Sep 6, 2023 · 0 comments

Comments

@landv
Copy link
Owner

landv commented Sep 6, 2023

技师钟数明细表(汇总表) 付款方式统计-

up_snRptArtItemDetail

--up_snRptArtItemDetail '2003-01-01','2010-01-22','','','120,138,217',''

--select * from uf_snbills('120,138,217','',',')
--v_payModeNO 付款方式代码
--snPayMode 付款方式表
--pay.pay,--landv
--LEFT JOIN snPayMode pay on a.v_payModeNO=pay.v_payModeNO --landv


ALTER        PROCEDURE [dbo].[up_snRptArtItemDetail] (
                @as_sDate         char(10) = '',    --开始营业日
                @as_eDate         char(10) = '',    --结束营业日
                @as_sDateTime     varchar(20) = '',    --开始起钟时间    
                @as_eDateTime    varchar(20) = '',    --结束起钟时间
                @as_artNo        varchar(40) = '',    --技师号m
                @as_itemNo        varchar(200) = '',    --消费项目号
                @as_isspec        varchar(20) = '',    --起钟类型
                @as_shiftNo        varchar(30) = '',    --班次    m
                @as_OperName    varchar(50) = '',    --收银员m
                @as_type        varchar(10) = '',
                @as_itemtype    varchar(200)='',
                @ai_tagcheckout varchar(1) = '1' --是否已结:1:已结 0:未结 9:全部 luoy 2018-03-08
    )

as


begin
set nocount on
/*******************************************************************************************
名称：    up_snRptArtItemDetail
说明：    报表：技师钟数明细表(汇总表)
作者：    陈提见
日期：    2003-01-14
更新日期：
********************************************************************************************/
declare @errno int
declare @errmsg varchar(255)
declare @sql varchar(5000)

--技师号<匙牌号<房号<项目<起钟时间<落钟时间<钟数<金额<起钟类型

declare @dt_sDate datetime
declare @dt_eDate datetime
declare @dt_sDateTime datetime
declare @dt_eDateTime datetime

set @dt_sDate = dbo.uf_stringDate(@as_sDate)
set @dt_eDate = dbo.uf_stringDate(@as_eDate)
set @dt_sDateTime = dbo.uf_stringDate(@as_sDateTime)
set @dt_eDateTime = dbo.uf_stringDate(@as_eDateTime)



if @as_type = ''
    set @as_type = '全部'
if isnull(ltrim(rtrim(@ai_tagcheckout)),'') = ''
set @ai_tagcheckout = '1'

create table #temp_rpt
    (
     v_artno        varchar(6),
     v_keyno        varchar(7),
     v_positionno   varchar(6),
     v_name1        varchar(30),
     dt_start       datetime,
     dt_record       datetime,
     n_quantity     decimal(12,2),
     n_amount       decimal(18,2),
     v_isspec       varchar(40),
     v_billno       varchar(20),
     i_tagcheckout    int,
     v_link            varchar(20),
     fusername        varchar(20),
     d_inbusiness    datetime,
     d_business        datetime,
     n_dueamount    decimal(18,2),
     n_discount        decimal(18,3),
     v_invNo        varchar(20),
     v_downinvno    varchar(50),
    -- v_artname1     varchar(30),
     v_pay                varchar(20) --landv
)

if @dt_sDate is not null
begin
    if @ai_tagcheckout = '1' or @ai_tagcheckout = '9'
    begin
        select v_paidNo
        into #temp_paidNo
        from uf_snRptGetPaidNo(@as_sDate,    --开始营业日
                   @as_eDate ,    --结束营业日
                   null ,    --开始自然时间    
                   null,    --结束自然时间
                   '',    --收银点
                   @as_shiftNo,    --班次    
                   @as_OperName,    --收银员
                   0)


    insert into #temp_rpt(v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay)
    select A.v_artno,
           v_keyno = case when isnull(rtrim(ltrim(a.v_oribillno)),'')<> '' then (select v_keyno from v_sntabmast where v_billno = a.v_oribillno ) else B.v_keyNo end,
           A.v_positionNO,
           A.v_name1,
           dt_start = A.dt_input,
           c.dt_record,
           A.n_quantity,
           A.n_amount,
           v_isspec = d.v_name1,
           A.v_billNo,
           a.i_tagcheckout,
           b.v_link,
           u.fusername,
           b.d_inbusiness,
          c.d_business,a.n_dueamount,a.n_discount,a.v_invNo,a.v_downinvno,
          --'v_artname1' = art.v_name1,
             pay.v_Name1--landv
    from v_snTabDetail a left join snIsspec d on a.v_isspec = d.v_isspec
               left join snBillMast c on A.v_paidNo = C.v_paidNo
               left join snart art on a.v_artno = art.v_artno 
                 LEFT JOIN snPayMode pay on a.v_payModeNO=pay.v_payModeNO --landv
               left join t_user u on a.v_operno = u.fuserno,            
         v_snTabMast b,#temp_paidNo e
    where
          A.v_billNo =b.v_billNo  and a.v_paidno=e.v_paidno and
          A.i_upRowId >= 0 and a.i_tagcharge<>2 and
          A.i_type = 1 and
          (@as_type = '全部' or
           @as_type = '做钟' and A.i_seq > 0 or
           @as_type = '买钟' and ( A.i_seq is null or A.i_seq <= 0)
          ) and
          (@as_itemNo = '' or  A.v_itemNo in (select v_billNo from uf_snBills(@as_itemno,'',','))) and
          (@as_artNo = '' or A.v_artno in (select v_billNo from uf_snBills(@as_artNo,'',','))) and
          (@as_itemtype = '' or A.v_itemtype in (select v_billNo from uf_snBills(@as_itemtype,'',','))) and
(@as_isspec = '' or A.v_isspec in(select v_billNo from uf_snBills(@as_isspec,'',','))) and
        b.i_tagcheckout <> 9
    end
    
    --取未结算的数据
    if @ai_tagcheckout = '0' or @ai_tagcheckout = '9'
    begin
        insert into #temp_rpt(v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay)
        select
                    A.v_artno,
           v_keyno = case when isnull(rtrim(ltrim(a.v_oribillno)),'')<> '' then (select v_keyno from v_sntabmast where v_billno = a.v_oribillno ) else B.v_keyNo end,
           A.v_positionNO,
           A.v_name1,
           dt_start = A.dt_input,
           c.dt_record,
           A.n_quantity,
           A.n_amount,
           v_isspec = d.v_name1,
           A.v_billNo,
           a.i_tagcheckout,
           b.v_link,
           u.fusername,
           b.d_inbusiness,
           c.d_business,a.n_dueamount,a.n_discount,a.v_invNo,a.v_downinvno,
           --'v_artname1' = art.v_name1,
              pay.v_Name1--landv
    from v_snTabDetail a left join snIsspec d on a.v_isspec = d.v_isspec
               left join snBillMast c on A.v_paidNo = C.v_paidNo
               left join snart art on a.v_artno = art.v_artno
                 LEFT JOIN snPayMode pay on a.v_payModeNO=pay.v_payModeNO --landv
               left join t_user u on a.v_operno = u.fuserno,            
         v_snTabMast b
    where
          A.v_billNo =b.v_billNo and
          A.i_upRowId >= 0 and a.i_tagcharge<>2 and
          A.i_type = 1 and
          (@as_type = '全部' or
           @as_type = '做钟' and A.i_seq > 0 or
           @as_type = '买钟' and ( A.i_seq is null or A.i_seq <= 0)
          ) and
          (@as_itemNo = '' or  A.v_itemNo in (select v_billNo from uf_snBills(@as_itemno,'',','))) and
          (@as_artNo = '' or A.v_artno in (select v_billNo from uf_snBills(@as_artNo,'',','))) and
          (@as_itemtype = '' or A.v_itemtype in (select v_billNo from uf_snBills(@as_itemtype,'',','))) and
(@as_isspec = '' or A.v_isspec in(select v_billNo from uf_snBills(@as_isspec,'',','))) and
         b.i_tagcheckout <> 9
         and isnull(a.i_tagcheckout,0) = 0
    end

    select v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay
    from #temp_rpt
    order by v_artNo
end
else
begin
    if @ai_tagcheckout = '1' or @ai_tagcheckout = '9'     
    begin
        insert into #temp_rpt(v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay)
        select
                A.v_artno,
               v_keyno = case when isnull(rtrim(ltrim(a.v_oribillno)),'')<> '' then (select v_keyno from v_sntabmast where v_billno = a.v_oribillno ) else B.v_keyNo end,
               A.v_positionNO,
               A.v_name1,
               dt_start = isnull(A.dt_start,A.dt_input),
               c.dt_record,
               A.n_quantity,
               A.n_amount,
               v_isspec = d.v_name1,
               A.v_billNo,
               a.i_tagcheckout,
               b.v_link,
               u.fusername,
               b.d_inbusiness,
               c.d_business,
               a.n_dueamount,
               a.n_discount,
               a.v_invNo,a.v_downinvno,
               --'v_artname1' = art.v_name1,
                  pay.v_Name1--landv
        from v_snTabDetail a left join snIsspec d on a.v_isspec = d.v_isspec
                   left join t_user u on a.v_operno = u.fuserno
                   left join snart art on a.v_artno = art.v_artno
                     LEFT JOIN snPayMode pay on a.v_payModeNO=pay.v_payModeNO --landv
                   left join snBillMast c on A.v_paidNo = C.v_paidNo,
             v_snTabMast b
        where A.v_billNo =b.v_billNo  and
              A.i_type = 1 and
              a.i_tagcharge<>2 and
              (@as_type = '全部' or
               @as_type = '做钟' and A.i_seq > 0 or
               @as_type = '买钟' and ( A.i_seq is null or A.i_seq <= 0)
              ) and
              (A.dt_input > @as_sDateTime and A.dt_input <= @as_eDateTime) and
              (@as_itemNo = '' or A.v_itemno in (select v_billNo from uf_snBills(@as_itemno,'',','))) and
              (@as_artNo = '' or A.v_artNo in(select v_billNo from uf_snBills(@as_artNo,'',','))) and
              (@as_isspec = '' or A.v_isspec in(select v_billNo from uf_snBills(@as_isspec,'',','))) and
              (@as_shiftNo = '' or C.v_shiftNo in(select v_billNo from uf_snBills(@as_shiftNo,'',','))) and
              (@as_itemtype = '' or A.v_itemtype in (select v_billNo from uf_snBills(@as_itemtype,'',','))) and
              (@as_OperName = '' or v_operName in(select v_billNo from uf_snBills(@as_operName,'',','))) and
              b.i_tagcheckout <> 9 and
             (a.i_tagcheckout = 1)
    end
    
    if @ai_tagcheckout = '0' or @ai_tagcheckout = '9'     
    begin
        insert into #temp_rpt(v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay)
        select 
                    
                    A.v_artno,
               v_keyno = case when isnull(rtrim(ltrim(a.v_oribillno)),'')<> '' then (select v_keyno from v_sntabmast where v_billno = a.v_oribillno ) else B.v_keyNo end,
               A.v_positionNO,
               A.v_name1,
               dt_start = isnull(A.dt_start,A.dt_input),
               c.dt_record,
               A.n_quantity,
               A.n_amount,
               v_isspec = d.v_name1,
               A.v_billNo,
               a.i_tagcheckout,
               b.v_link,
               u.fusername,
               b.d_inbusiness,
               c.d_business,
               a.n_dueamount,
               a.n_discount,
               a.v_invNo,a.v_downinvno,
               --'v_artname1' = art.v_name1,
                 pay.v_Name1--landv
        from v_snTabDetail a left join snIsspec d on a.v_isspec = d.v_isspec
                   left join t_user u on a.v_operno = u.fuserno
                   left join snart art on a.v_artno = art.v_artno
                     LEFT JOIN snPayMode pay on a.v_payModeNO=pay.v_payModeNO --landv
                   left join snBillMast c on A.v_paidNo = C.v_paidNo,
             v_snTabMast b
        where A.v_billNo =b.v_billNo  and
              A.i_type = 1 and
              a.i_tagcharge<>2 and
              (@as_type = '全部' or
               @as_type = '做钟' and A.i_seq > 0 or
               @as_type = '买钟' and ( A.i_seq is null or A.i_seq <= 0)
              ) and
              (@as_itemNo = '' or A.v_itemno in (select v_billNo from uf_snBills(@as_itemno,'',','))) and
              (@as_artNo = '' or A.v_artNo in(select v_billNo from uf_snBills(@as_artNo,'',','))) and
              (@as_isspec = '' or A.v_isspec in(select v_billNo from uf_snBills(@as_isspec,'',','))) and
              (@as_shiftNo = '' or C.v_shiftNo in(select v_billNo from uf_snBills(@as_shiftNo,'',','))) and
              (@as_itemtype = '' or A.v_itemtype in (select v_billNo from uf_snBills(@as_itemtype,'',','))) and
              (@as_OperName = '' or v_operName in(select v_billNo from uf_snBills(@as_operName,'',','))) and
              b.i_tagcheckout <> 9 and
             (a.i_tagcheckout = 0)
        end        
    select v_artno,v_keyno,v_positionno,v_name1,dt_start,dt_record,n_quantity,n_amount,v_isspec,v_billno,i_tagcheckout,v_link,fusername,d_inbusiness,d_business,n_dueamount,n_discount,v_invNo,v_downinvno,v_pay
    from #temp_rpt
    order by v_artNo
end

--加入小费与合计
set nocount off
return

error:
--r aiserror @errno  @errmsg
raiserror ( @errmsg   ,   16, 1 )
--   rollback  transaction

set nocount off
end

image
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@landv