捷信达温泉管理软件员工卡SQL查询

1
select * from snkey  where v_name2 like '%员工%'

捷信达酒店管理系统其他消费明细SQL查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select SUM(Amount)
from gsfoliodetail a  ,
            (
                select a.pcode,a.cname from  gsitem a
                where a.code like '0%' and len(a.pcode) = 6
            ) b
        where ( substring(a.itempcode,1,6) = b.pcode)and  pcode like  '001010%'  
 



select *
from gsfoliodetail a  ,
            (
                select a.pcode,a.cname from  gsitem a
                where a.code like '0%' and len(a.pcode) = 6
            ) b
        where ( substring(a.itempcode,1,6) = b.pcode)and  pcode like  '001010%'

捷信达温泉管理软件消费区域序号只能是一位数不完美解决方案SQL

捷信达温泉管理软件消费区域序号只能是一位数不完美解决方案SQL，反编译不了软件，只能修改数据层。

snWsPara –工作站参数维护
snArea –消费区
snAreaItemType –消费区、消费项目类型
snItemType –消费类别设置

以上数据表areaNO varchar(2) 修改成两位

捷信达资料维护模块存在PB代码进行数据校验，所以后续添加消费区域，需要在数据库进行维护。功能较小就不编写维护程序了，直接操作数据库即可。

温泉手牌录入后执行的数据库命令，如不执行，捷信达官配闸机不识别手牌。
update snkey set v_hexcardno=right(v_hexcardno,6)
update snkey set v_hexcardno=’0000’+v_hexcardno

捷信达应收合约金额修改SQL

1
2
3
4
5
6
use gshis
select * from v_gscorpprofile --应收合约单位列表
select * from gsProfileCA where PaidAmt ='115439'
select * from gsProfileCA where ProfileID ='159' and transid ='1363159'
--UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update  gsProfileCA SET Amount = '115332.5' where ProfileID ='159' and transid ='1363159'

捷信达调单显示未买金额，打单记录里面也没有帐单

1
2
3
4
select v_link,*from sntabmast where v_billNo='7070200581' --第一条输入客帐号查找到批次号v_link
select i_print,i_tagcheckout,*from sntabdetail where v_billNo in(select v_billno from sntabmast where v_link='707020171') and i_tagcheckout=0

update sntabdetail set i_print='0' where v_billNo in(select v_billno from sntabmast where v_link='707020148') and i_tagcheckout=0 --第三条，批次号填写到这这里来

捷信达租赁系统，修改租赁状态

1
2
3
4
5
6
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *  FROM [gsticket].[dbo].[gstkConsume] where consumeno ='SC190106113625000024' and rentstatus ='0'
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update [gsticket].[dbo].[gstkConsume] SET rentstatus='1' where consumeno ='SC190106113625000024' and rentstatus ='0'

--修改租赁状态，是否归还，归还为1，未还为0

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301

--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

捷信达温泉员工卡查询SQL语句

1
select * from snkey  where v_name2 like '%员工%'

捷信达会员卡删除历史往来明细

1
2
3
4
5
6
7
8
9
10
select * from gsprofile where RFCardNo ='0451870484'

update gsprofile SET Balance ='0',defaultcharge ='0',BaseAmtBalance ='0',IncAmount ='0'  where RFCardNo ='0451870484'


select * from gsmbrcard where CardNo ='000016'

select * from gsProfileCA where MbrCardNo ='000016' and PaymentDesc = '26' -- PaymentDesc ='18'

delete from gsProfileCA where MbrCardNo ='000016' and PaymentDesc = '18'