--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
技师钟数明细表(汇总表) 付款方式统计-
up_snRptArtItemDetail