· 8 years ago · Jan 08, 2018, 08:00 PM
1
2GO
3SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
4
5SET NUMERIC_ROUNDABORT OFF;
6
7
8
9GO
10/*
11The column [dbo].[tb_voucher].[vchri_text] is being dropped, data loss could occur.
12*/
13
14
15GO
16PRINT N'Starting rebuilding table [dbo].[tb_klient]...';
17
18
19GO
20BEGIN TRANSACTION;
21
22SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
23
24SET XACT_ABORT ON;
25
26CREATE TABLE [dbo].[tmp_ms_xx_tb_klient] (
27 [kd_id] INT IDENTITY (1, 1) NOT NULL,
28 [kd_name] NVARCHAR (200) NULL,
29 [kd_surname] NVARCHAR (50) NULL,
30 [kd_namefull] AS (CASE WHEN [kd_typid] = (1) THEN ([kd_name] + ' ') + [kd_surname] ELSE [kd_name] END) PERSISTED,
31 [kd_town] NVARCHAR (64) NULL,
32 [kd_ul] NVARCHAR (200) NULL,
33 [kd_typid] INT NULL,
34 [kd_personal_id_typ] INT NULL,
35 [kd_personal_id_no] VARCHAR (25) NULL,
36 [kd_nip] VARCHAR (20) NULL,
37 [kd_tel] VARCHAR (20) NULL,
38 [kd_fax] VARCHAR (20) NULL,
39 [kd_w] INT NULL,
40 [kd_country] INT NULL,
41 [kd_www] VARCHAR (80) NULL,
42 [kd_mail] VARCHAR (64) NULL,
43 [kd_info] VARCHAR (700) NULL,
44 [kd_pkod] VARCHAR (10) NULL,
45 [kd_stat] INT CONSTRAINT [DF_tb_klient_kd_stat] DEFAULT ((0)) NULL,
46 [kd_empid] INT NULL,
47 [kd_extid] INT NULL,
48 [kd_birthdate] DATE NULL,
49 [kd_age] AS (datediff(day, [kd_birthdate], getdate()) / (365)),
50 [kd_pass] VARCHAR (25) NULL,
51 [kd_carreg] VARCHAR (25) NULL,
52 [kd_sourceid] INT CONSTRAINT [DF_tb_klient_ok] DEFAULT ((0)) NULL,
53 [kd_lastextupdate] BIGINT NULL,
54 [kd_compid] INT NULL,
55 [kd_langid] INT NULL,
56 [kd_st] INT NULL,
57 CONSTRAINT [tmp_ms_xx_constraint_PK_tb_klient1] PRIMARY KEY CLUSTERED ([kd_id] ASC)
58);
59
60IF EXISTS (SELECT TOP 1 1
61 FROM [dbo].[tb_klient])
62 BEGIN
63 SET IDENTITY_INSERT [dbo].[tmp_ms_xx_tb_klient] ON;
64 INSERT INTO [dbo].[tmp_ms_xx_tb_klient] ([kd_id], [kd_name], [kd_surname], [kd_town], [kd_ul], [kd_typid], [kd_nip], [kd_tel], [kd_fax], [kd_w], [kd_country], [kd_www], [kd_mail], [kd_info], [kd_pkod], [kd_stat], [kd_empid], [kd_extid], [kd_birthdate], [kd_pass], [kd_carreg], [kd_sourceid], [kd_lastextupdate], [kd_compid], [kd_st], [kd_langid])
65 SELECT [kd_id],
66 [kd_name],
67 [kd_surname],
68 [kd_town],
69 [kd_ul],
70 [kd_typid],
71 [kd_nip],
72 [kd_tel],
73 [kd_fax],
74 [kd_w],
75 [kd_country],
76 [kd_www],
77 [kd_mail],
78 [kd_info],
79 [kd_pkod],
80 [kd_stat],
81 [kd_empid],
82 [kd_extid],
83 [kd_birthdate],
84 [kd_pass],
85 [kd_carreg],
86 [kd_sourceid],
87 [kd_lastextupdate],
88 [kd_compid],
89 [kd_st],
90 [kd_langid]
91 FROM [dbo].[tb_klient]
92 ORDER BY [kd_id] ASC;
93 SET IDENTITY_INSERT [dbo].[tmp_ms_xx_tb_klient] OFF;
94 END
95
96DROP TABLE [dbo].[tb_klient];
97
98EXECUTE sp_rename N'[dbo].[tmp_ms_xx_tb_klient]', N'tb_klient';
99
100EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_tb_klient1]', N'PK_tb_klient', N'OBJECT';
101
102COMMIT TRANSACTION;
103
104SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
105
106
107GO
108PRINT N'Creating [dbo].[tb_klient].[IX_town]...';
109
110
111GO
112CREATE NONCLUSTERED INDEX [IX_town]
113 ON [dbo].[tb_klient]([kd_town] ASC);
114
115
116GO
117PRINT N'Creating [dbo].[tb_klient].[IX_nazwa]...';
118
119
120GO
121CREATE NONCLUSTERED INDEX [IX_nazwa]
122 ON [dbo].[tb_klient]([kd_name] ASC);
123
124
125GO
126PRINT N'Creating [dbo].[tb_klient].[IX_NameFull]...';
127
128
129GO
130CREATE NONCLUSTERED INDEX [IX_NameFull]
131 ON [dbo].[tb_klient]([kd_namefull] ASC);
132
133
134GO
135PRINT N'Creating [dbo].[tb_klient].[IX_source]...';
136
137
138GO
139CREATE NONCLUSTERED INDEX [IX_source]
140 ON [dbo].[tb_klient]([kd_sourceid] ASC) WHERE ([kd_sourceid]<>(2));
141
142
143GO
144PRINT N'Altering [dbo].[tb_voucher]...';
145
146
147
148
149GO
150PRINT N'Altering [dict].[tb_holidays]...';
151
152
153GO
154ALTER TABLE [dict].[tb_holidays] ALTER COLUMN [hd_dayserial] INT NOT NULL;
155
156
157GO
158PRINT N'Altering [hr].[tb_urlopwn]...';
159
160
161GO
162ALTER TABLE [hr].[tb_urlopwn]
163 ADD [uw_st] INT CONSTRAINT [DF_tb_urlopwn_uw_st] DEFAULT ((-1)) NULL;
164
165
166GO
167PRINT N'Altering [inv].[tb_payment]...';
168
169
170GO
171ALTER TABLE [inv].[tb_payment] ALTER COLUMN [pym_subject] NVARCHAR (80) NULL;
172
173
174GO
175PRINT N'Altering [rep].[tb_repitemdef]...';
176
177
178GO
179ALTER TABLE [rep].[tb_repitemdef]
180 ADD [rid_sqlformula] NVARCHAR (500) NULL;
181
182
183GO
184PRINT N'Altering [rsv].[resource]...';
185
186
187GO
188ALTER TABLE [rsv].[resource]
189 ADD [res_room_basetype] INT SPARSE NULL;
190
191
192GO
193PRINT N'Altering [rsv].[tb_reserv_person]...';
194
195
196GO
197ALTER TABLE [rsv].[tb_reserv_person] DROP COLUMN [rp_age];
198
199
200GO
201PRINT N'Starting rebuilding table [rsv].[tb_reservation]...';
202
203
204GO
205BEGIN TRANSACTION;
206
207SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
208
209SET XACT_ABORT ON;
210
211CREATE TABLE [rsv].[tmp_ms_xx_tb_reservation] (
212 [rsv_id] INT IDENTITY (1, 1) NOT NULL,
213 [rsv_head] INT NULL,
214 [rsv_reservator] INT NULL,
215 [rsv_respersonname] VARCHAR (120) NULL,
216 [rsv_rescontact] VARCHAR (150) NULL,
217 [rsv_payer] INT NULL,
218 [rsv_source] INT NULL,
219 [rsv_source_details] NVARCHAR (50) NULL,
220 [rsv_start] INT NULL,
221 [rsv_end] INT NULL,
222 [rsv_pricelevel] INT NULL,
223 [rsv_entrydate] INT NULL,
224 [rsv_singularorgroup] INT NULL,
225 [rsv_st] INT CONSTRAINT [DF_tb_reservation_single_rsvs_st] DEFAULT ((-1)) NULL,
226 [rsv_comm] VARCHAR (250) NULL,
227 [rsv_statusid] INT NULL,
228 [rsv_carreg] VARCHAR (250) NULL,
229 CONSTRAINT [tmp_ms_xx_constraint_PK_tb_reservation_head1] PRIMARY KEY CLUSTERED ([rsv_id] ASC)
230);
231
232IF EXISTS (SELECT TOP 1 1
233 FROM [rsv].[tb_reservation])
234 BEGIN
235 SET IDENTITY_INSERT [rsv].[tmp_ms_xx_tb_reservation] ON;
236 INSERT INTO [rsv].[tmp_ms_xx_tb_reservation] ([rsv_id], [rsv_head], [rsv_reservator], [rsv_source], [rsv_source_details], [rsv_start], [rsv_end], [rsv_pricelevel], [rsv_entrydate], [rsv_singularorgroup], [rsv_st], [rsv_statusid])
237 SELECT [rsv_id],
238 [rsv_head],
239 [rsv_reservator],
240 [rsv_source],
241 [rsv_source_details],
242 [rsv_start],
243 [rsv_end],
244 [rsv_pricelevel],
245 [rsv_entrydate],
246 [rsv_singularorgroup],
247 [rsv_st],
248 [rsv_statusid]
249 FROM [rsv].[tb_reservation]
250 ORDER BY [rsv_id] ASC;
251 SET IDENTITY_INSERT [rsv].[tmp_ms_xx_tb_reservation] OFF;
252 END
253
254DROP TABLE [rsv].[tb_reservation];
255
256EXECUTE sp_rename N'[rsv].[tmp_ms_xx_tb_reservation]', N'tb_reservation';
257
258EXECUTE sp_rename N'[rsv].[tmp_ms_xx_constraint_PK_tb_reservation_head1]', N'PK_tb_reservation_head', N'OBJECT';
259
260COMMIT TRANSACTION;
261
262SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
263
264
265GO
266PRINT N'Starting rebuilding table [rsv].[tb_reservation_status]...';
267
268
269GO
270BEGIN TRANSACTION;
271
272SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
273
274SET XACT_ABORT ON;
275
276CREATE TABLE [rsv].[tmp_ms_xx_tb_reservation_status] (
277 [rsvst_id] INT IDENTITY (1, 1) NOT NULL,
278 [rsvst_name] NVARCHAR (100) NULL,
279 [rsvst_desc] NVARCHAR (150) NULL,
280 [rsvst_succ] INT CONSTRAINT [DF_tb_reservation_status_rsvst_succ] DEFAULT ((0)) NULL,
281 [rsvst_st] INT NULL,
282 CONSTRAINT [tmp_ms_xx_constraint_PK_dic_status1] PRIMARY KEY CLUSTERED ([rsvst_id] ASC)
283);
284
285IF EXISTS (SELECT TOP 1 1
286 FROM [rsv].[tb_reservation_status])
287 BEGIN
288 SET IDENTITY_INSERT [rsv].[tmp_ms_xx_tb_reservation_status] ON;
289 INSERT INTO [rsv].[tmp_ms_xx_tb_reservation_status] ([rsvst_id], [rsvst_name], [rsvst_desc], [rsvst_st])
290 SELECT [rsvst_id],
291 [rsvst_name],
292 [rsvst_desc],
293 [rsvst_st]
294 FROM [rsv].[tb_reservation_status]
295 ORDER BY [rsvst_id] ASC;
296 SET IDENTITY_INSERT [rsv].[tmp_ms_xx_tb_reservation_status] OFF;
297 END
298
299DROP TABLE [rsv].[tb_reservation_status];
300
301EXECUTE sp_rename N'[rsv].[tmp_ms_xx_tb_reservation_status]', N'tb_reservation_status';
302
303EXECUTE sp_rename N'[rsv].[tmp_ms_xx_constraint_PK_dic_status1]', N'PK_dic_status', N'OBJECT';
304
305COMMIT TRANSACTION;
306
307SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
308
309
310GO
311PRINT N'Altering [rsv].[tb_room]...';
312
313
314GO
315ALTER TABLE [rsv].[tb_room]
316 ADD [rm_extrabeds] INT NULL,
317 [rm_max_person_no] INT NULL,
318 [rm_person_no] INT NULL;
319
320
321GO
322PRINT N'Altering [spa].[tb_spa_reservation]...';
323
324
325GO
326ALTER TABLE [spa].[tb_spa_reservation] ALTER COLUMN [srsv_tel] VARCHAR (50) NULL;
327
328
329
330GO
331ALTER TABLE [wkt].[tb_worktime_records] DROP COLUMN [wktr_datefull2];
332
333
334GO
335PRINT N'Starting rebuilding table [wty].[tb_redirect]...';
336
337
338GO
339BEGIN TRANSACTION;
340
341SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
342
343SET XACT_ABORT ON;
344
345CREATE TABLE [wty].[tmp_ms_xx_tb_redirect] (
346 [red_id] INT IDENTITY (1, 1) NOT NULL,
347 [red_security_profile] INT NULL,
348 [red_code] NVARCHAR (80) NULL,
349 [red_validdate] INT NULL,
350 [red_cdid] INT NULL,
351 [red_ownerid] INT NULL,
352 [red_st] INT CONSTRAINT [DF_tb_redirect_red_st] DEFAULT ((-1)) NULL,
353 [red_target] NVARCHAR (150) CONSTRAINT [DF_tb_redirect_red_target] DEFAULT (N'/') NULL,
354 [red_pass] NVARCHAR (50) NULL,
355 [red_dynamicpass] NVARCHAR (50) NULL,
356 [red_passerrcount] INT CONSTRAINT [DF_tb_redirect_red_passerrcount] DEFAULT ((0)) NULL,
357 [red_dynamicerrcount] INT CONSTRAINT [DF_tb_redirect_red_passerrcount1] DEFAULT ((0)) NULL,
358 [red_dynamiccount] INT CONSTRAINT [DF_tb_redirect_red_passerrcount2] DEFAULT ((0)) NULL,
359 [red_contact] NVARCHAR (100) NULL,
360 [red_validtime] INT NULL,
361 [red_dynamicsendcount] INT CONSTRAINT [DF_tb_redirect_red_dynamicsendcount] DEFAULT ((0)) NULL,
362 [red_connectedid] INT NULL,
363 [red_klient] INT CONSTRAINT [DF_tb_redirect_red_klient] DEFAULT ((0)) NULL,
364 [red_type] INT NULL,
365 CONSTRAINT [tmp_ms_xx_constraint_PK_tb_redirect1] PRIMARY KEY CLUSTERED ([red_id] ASC)
366);
367
368IF EXISTS (SELECT TOP 1 1
369 FROM [wty].[tb_redirect])
370 BEGIN
371 SET IDENTITY_INSERT [wty].[tmp_ms_xx_tb_redirect] ON;
372 INSERT INTO [wty].[tmp_ms_xx_tb_redirect] ([red_id], [red_security_profile], [red_code], [red_validdate], [red_cdid], [red_ownerid], [red_st], [red_target], [red_pass], [red_dynamicpass], [red_passerrcount], [red_dynamicerrcount], [red_dynamiccount], [red_contact], [red_validtime], [red_dynamicsendcount], [red_connectedid], [red_type], [red_klient])
373 SELECT [red_id],
374 [red_security_profile],
375 [red_code],
376 [red_validdate],
377 [red_cdid],
378 [red_ownerid],
379 [red_st],
380 [red_target],
381 [red_pass],
382 [red_dynamicpass],
383 [red_passerrcount],
384 [red_dynamicerrcount],
385 [red_dynamiccount],
386 [red_contact],
387 [red_validtime],
388 [red_dynamicsendcount],
389 [red_connectedid],
390 [red_type],
391 [red_klient]
392 FROM [wty].[tb_redirect]
393 ORDER BY [red_id] ASC;
394 SET IDENTITY_INSERT [wty].[tmp_ms_xx_tb_redirect] OFF;
395 END
396
397DROP TABLE [wty].[tb_redirect];
398
399EXECUTE sp_rename N'[wty].[tmp_ms_xx_tb_redirect]', N'tb_redirect';
400
401EXECUTE sp_rename N'[wty].[tmp_ms_xx_constraint_PK_tb_redirect1]', N'PK_tb_redirect', N'OBJECT';
402
403COMMIT TRANSACTION;
404
405SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
406
407
408GO
409PRINT N'Starting rebuilding table [wty].[tb_security_profile]...';
410
411
412GO
413BEGIN TRANSACTION;
414
415SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
416
417SET XACT_ABORT ON;
418
419CREATE TABLE [wty].[tmp_ms_xx_tb_security_profile] (
420 [sp_id] INT IDENTITY (1, 1) NOT NULL,
421 [sp_seclevel] INT NULL,
422 [sp_passlimit] INT NULL,
423 [sp_dynamicerrlimit] INT NULL,
424 [sp_dynamicalllimit] INT NULL,
425 [sp_st] INT CONSTRAINT [DF_tb_security_profile_sp_st] DEFAULT ((-1)) NULL,
426 [sp_name] NVARCHAR (50) NULL,
427 [sp_dynamicmethod] INT NULL,
428 [sp_dynamicsendlimit] INT NULL,
429 [sp_codelength] INT NULL,
430 [sp_validtime] INT NULL,
431 [sp_validdays_OLD] INT NULL,
432 CONSTRAINT [tmp_ms_xx_constraint_PK_tb_security_profile1] PRIMARY KEY CLUSTERED ([sp_id] ASC)
433);
434
435IF EXISTS (SELECT TOP 1 1
436 FROM [wty].[tb_security_profile])
437 BEGIN
438 SET IDENTITY_INSERT [wty].[tmp_ms_xx_tb_security_profile] ON;
439 INSERT INTO [wty].[tmp_ms_xx_tb_security_profile] ([sp_id], [sp_seclevel], [sp_passlimit], [sp_dynamicerrlimit], [sp_dynamicalllimit], [sp_st], [sp_name], [sp_dynamicmethod], [sp_dynamicsendlimit], [sp_validtime], [sp_codelength])
440 SELECT [sp_id],
441 [sp_seclevel],
442 [sp_passlimit],
443 [sp_dynamicerrlimit],
444 [sp_dynamicalllimit],
445 [sp_st],
446 [sp_name],
447 [sp_dynamicmethod],
448 [sp_dynamicsendlimit],
449 [sp_validtime],
450 [sp_codelength]
451 FROM [wty].[tb_security_profile]
452 ORDER BY [sp_id] ASC;
453 SET IDENTITY_INSERT [wty].[tmp_ms_xx_tb_security_profile] OFF;
454 END
455
456DROP TABLE [wty].[tb_security_profile];
457
458EXECUTE sp_rename N'[wty].[tmp_ms_xx_tb_security_profile]', N'tb_security_profile';
459
460EXECUTE sp_rename N'[wty].[tmp_ms_xx_constraint_PK_tb_security_profile1]', N'PK_tb_security_profile', N'OBJECT';
461
462COMMIT TRANSACTION;
463
464SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
465
466
467GO
468PRINT N'Creating [dbo].[tb_klient_idtyp]...';
469
470
471GO
472CREATE TABLE [dbo].[tb_klient_idtyp] (
473 [clid_id] INT NOT NULL,
474 [clid_name] VARCHAR (50) NULL,
475 [clid_ord] SMALLMONEY NULL
476);
477
478
479GO
480PRINT N'Creating [dbo].[tb_voucher_log]...';
481
482
483GO
484CREATE TABLE [dbo].[tb_voucher_log] (
485 [vchh_id] INT IDENTITY (1, 1) NOT NULL,
486 [vchr_itemid] INT NOT NULL,
487 [vchr_operation] VARCHAR (250) NULL,
488 [vchr_opdate] DATETIME NULL,
489 [vchr_eid] INT NULL,
490 [vchr_ename] VARCHAR (80) NULL,
491 [vchh_st] INT NULL,
492 CONSTRAINT [PK_tb_voucher_log] PRIMARY KEY CLUSTERED ([vchh_id] ASC)
493);
494
495
496GO
497PRINT N'Creating [dbo].[tb_voucher_target]...';
498
499
500GO
501CREATE TABLE [dbo].[tb_voucher_target] (
502 [ppid] INT IDENTITY (1, 1) NOT NULL,
503 [pp_vchrid] INT NULL,
504 [pp_purpose_id] INT NULL
505);
506
507
508GO
509PRINT N'Creating [dict].[tb_universal_dict]...';
510
511
512GO
513CREATE TABLE [dict].[tb_universal_dict] (
514 [ud_id] INT IDENTITY (1, 1) NOT NULL,
515 [ud_val] NVARCHAR (100) NULL,
516 [ud_key] NVARCHAR (50) NULL,
517 [ud_st] INT NULL,
518 CONSTRAINT [PK_tb_universal_dict] PRIMARY KEY CLUSTERED ([ud_id] ASC)
519);
520
521
522GO
523PRINT N'Creating [rsv].[tb_base_roomtype]...';
524
525
526GO
527CREATE TABLE [rsv].[tb_base_roomtype] (
528 [brt_id] INT IDENTITY (1, 1) NOT NULL,
529 [brt_name] NVARCHAR (100) NULL,
530 [brt_person_no] INT NULL,
531 [brt_st] INT NULL,
532 CONSTRAINT [PK_tb_base_roomtype] PRIMARY KEY CLUSTERED ([brt_id] ASC)
533);
534
535
536GO
537PRINT N'Creating [temp].[__x]...';
538
539
540
541
542
543
544GO
545ALTER TABLE [dbo].[tb_voucher_log]
546 ADD CONSTRAINT [DF_tb_voucher_log_vchh_st] DEFAULT ((1)) FOR [vchh_st];
547
548
549GO
550
551GO
552ALTER TABLE [dict].[tb_universal_dict]
553 ADD CONSTRAINT [DF_tb_universal_dict_ud_st] DEFAULT ((-1)) FOR [ud_st];
554
555
556GO
557PRINT N'Creating [rsv].[DF_tb_base_roomtype_brt_st]...';
558
559
560GO
561ALTER TABLE [rsv].[tb_base_roomtype]
562 ADD CONSTRAINT [DF_tb_base_roomtype_brt_st] DEFAULT ((-1)) FOR [brt_st];
563
564
565GO
566PRINT N'Altering [_def].[dodana_tab]...';
567
568
569GO
570ALTER TRIGGER [_def].[dodana_tab]
571 ON _def.cust_dims
572 AFTER INSERT
573AS
574BEGIN
575 SET NOCOUNT ON;
576 declare @tb_name varchar(100),@name varchar(100)
577declare @key varchar(25), @cSQL nvarchar(800), @cd int
578declare @typid int
579
580 return
581select @tb_name=nazwa_tabeli,@name=nazwa, @key=rs_relate_tb_key, @cd=cd_id, @typid=typ_id from inserted
582
583if @typid=1
584
585set @cSQL='CREATE TABLE '+@tb_name + '( '+ @key +' [int] IDENTITY(1,1) NOT NULL
586 CONSTRAINT [PK_'+@tb_name +'] PRIMARY KEY CLUSTERED
587( ['+ @key +'] ASC
588)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
589) ON [PRIMARY]'
590 exec sp_executesql @cSQL
591
592 return
593INSERT INTO _def.cust_dims_defs
594 ( [cd_id],[etykieta] ,[nazwa_pola]
595 ,[slownikowe],[wymagane],[systemowe]
596 ,[rozmiar], [ddf_data]
597 ,[widoczne] ,[zalokowany] ,[ord]
598 ,[tooltip] ,[isdata])
599 VALUES
600 (
601 @cd ,'id',@key
602 ,0,0 ,1
603 ,4 ,6
604 ,1 ,1,1
605 ,'record id' ,1)
606
607
608
609END
610GO
611PRINT N'Refreshing [dbo].[lookup_kd_town]...';
612
613
614GO
615
616ALTER VIEW [dbo].[v_reservation_all]
617AS
618SELECT dbo.tb_klient.kd_name, dbo.tb_reservation_source.rsvsr_name, dbo.tb_price_level.plv_name, dbo.tb_reservation.rsv_source_details, dbo.tb_reservation.rsv_start, dbo.tb_reservation.rsv_end,
619 dbo.tb_reservation.rsv_entrydate, dbo.tb_reservation.rsv_id, dbo.tb_reservation.rsv_st
620FROM dbo.tb_reservation INNER JOIN
621 dbo.tb_price_level ON dbo.tb_reservation.rsv_pricelevel = dbo.tb_price_level.plv_id INNER JOIN
622 dbo.tb_reservation_source ON dbo.tb_reservation.rsv_source = dbo.tb_reservation_source.rsvsr_id INNER JOIN
623 dbo.tb_klient ON dbo.tb_reservation.rsv_reservator = dbo.tb_klient.kd_id
624WHERE (dbo.tb_reservation.rsv_singularorgroup = 1)
625GO
626PRINT N'Refreshing [dbo].[v_schowek]...';
627
628
629
630GO
631ALTER VIEW rsv.v_reservation_all
632AS
633SELECT rsv.tb_reservation.rsv_id, rsv.tb_reservation.rsv_head, rsv.tb_reservation.rsv_start, rsv.tb_reservation.rsv_end, rsv.tb_reservation.rsv_st, rsv.tb_reservation_status.rsvst_name, rsv.tb_reservation_source.rsvsr_name,
634 dbo.tb_klient.kd_namefull, rsv.tb_reservation.rsv_source_details, rsv.tb_reservation.rsv_entrydate
635FROM rsv.tb_reservation INNER JOIN
636 rsv.tb_reservation_status ON rsv.tb_reservation.rsv_statusid = rsv.tb_reservation_status.rsvst_id LEFT OUTER JOIN
637 dbo.tb_klient ON rsv.tb_reservation.rsv_reservator = dbo.tb_klient.kd_id LEFT OUTER JOIN
638 rsv.tb_reservation_source ON rsv.tb_reservation.rsv_source = rsv.tb_reservation_source.rsvsr_id
639GO
640PRINT N'Refreshing [rsv].[v_reservation_cust]...';
641
642
643GO
644ALTER VIEW dbo.v_holidays
645AS
646SELECT dbo.tb_locarea.lar_name, dict.days.daydate, dict.days.dayofweek, dict.tb_holidays.hd_st, dict.tb_holidays.hd_freeday, dict.tb_holidays.hd_id
647FROM dict.tb_holidays INNER JOIN
648 dbo.tb_locarea ON dict.tb_holidays.hd_culture = dbo.tb_locarea.lar_id INNER JOIN
649 dict.days ON dict.tb_holidays.hd_dayserial = dict.days.dayserial
650GO
651PRINT N'Altering [dict].[v_holidays]...';
652
653
654GO
655ALTER VIEW dict.v_holidays
656AS
657SELECT dbo.tb_locarea.lar_name, dict.days.daydate, dict.days.dayofweek, dict.tb_holidays.hd_id, dict.tb_holidays.hd_st, dict.tb_holidays.hd_freeday, dict.tb_holidays.hd_dayserial
658FROM dict.tb_holidays INNER JOIN
659 dbo.tb_locarea ON dict.tb_holidays.hd_culture = dbo.tb_locarea.lar_id INNER JOIN
660 dict.days ON dict.tb_holidays.hd_dayserial = dict.days.dayserial
661GO
662
663
664GO
665
666
667ALTER VIEW [inv].[v_allpayments]
668AS
669WITH p AS (SELECT SUM(bs_value) AS paid_amount, bs_dueid
670 FROM inv.tb_bankstatement
671 GROUP BY bs_dueid)
672 SELECT inv.tb_payment.pym_date, inv.tb_payment.pym_id, inv.paymethod.pm_name, 'Inne' AS pym_subject, dict.currency.cr_name, inv.tb_payment.pym_nr_doc, inv.tb_payment.pym_value,
673 inv.tb_payment.pym_klient, ISNULL(p_1.paid_amount, 0) AS paid, ISNULL(inv.tb_payment.pym_value, 0) - ISNULL(p_1.paid_amount, 0) AS amount_left, inv.tb_payment.pym_completeddate,
674 inv.saledoctype.sdt_name, dbo.CalculateOverdueLevel(ISNULL(inv.tb_payment.pym_value, 0) - ISNULL(p_1.paid_amount, 0), inv.tb_payment.pym_date) AS pym_overduelvl, inv.tb_payment.pym_currency,
675 inv.tb_payment.pym_value_currency, inv.tb_payment.pym_value AS Expr1, pym_st
676 FROM inv.tb_payment INNER JOIN
677 inv.paymethod ON inv.tb_payment.pym_paymethod = inv.paymethod.pm_id INNER JOIN
678 dict.currency ON inv.tb_payment.pym_currency = dict.currency.cr_id INNER JOIN
679 inv.saledoctype ON inv.tb_payment.pym_doctyp = inv.saledoctype.sdt_id LEFT OUTER JOIN
680 p AS p_1 ON inv.tb_payment.pym_id = p_1.bs_dueid
681GO
682PRINT N'Refreshing [dbo].[v_confroom]...';
683
684
685GO
686ALTER VIEW spa.v_spa_payment
687AS
688SELECT dbo.tb_settle_method.sm_name, spa.tb_spa_reservation.srsv_klient_name, spa.tb_spa_payment.spap_value, spa.tb_spa_payment.spap_id, spa.tb_spa_payment.spap_spa_id, spa.tb_spa_payment.spap_source,
689 spa.tb_spa_payment.spap_vchri_id, spa.tb_spa_payment.spap_st, dbo.tb_klientofwhat.kof_name
690FROM dbo.tb_settle_method INNER JOIN
691 spa.tb_spa_payment ON dbo.tb_settle_method.sm_id = spa.tb_spa_payment.spap_method INNER JOIN
692 dbo.tb_klientofwhat ON spa.tb_spa_payment.spap_source = dbo.tb_klientofwhat.kof_id LEFT OUTER JOIN
693 spa.tb_spa_reservation ON spa.tb_spa_payment.spap_spa_id = spa.tb_spa_reservation.srsv_id
694GO
695PRINT N'Refreshing [wty].[v_redirect]...';
696
697
698
699GO
700
701ALTER VIEW [inv].[v_productlist]
702AS
703SELECT inv.product_type.pt_type, inv.vattype.vat_showval, inv.product.prd_name, inv.product.prd_id, inv.unit_type.ut_fullname, inv.product.prd_code
704FROM inv.product INNER JOIN
705 inv.vattype ON inv.product.prd_vatid = inv.vattype.vat_id INNER JOIN
706 inv.product_type ON inv.product.prd_ptid = inv.product_type.pt_id INNER JOIN
707 inv.unit_type ON inv.product.prd_utid = inv.unit_type.ut_id
708GO
709PRINT N'Altering [wkt].[v_worktime_data]...';
710
711
712GO
713
714ALTER VIEW [wkt].[v_worktime_data]
715AS
716SELECT hr.tb_workunit.wu_name, hr.tb_prac.p_name_full, wkt.tb_worktime_data.wkd_card_id, wkt.tb_worktime_data.wkd_exitediteddate, wkt.tb_worktime_data.wkd_entryediteddate,
717 sec.tb_sys_emps.e_name_full AS wkd_exiteditedby, tb_sys_emps_1.e_name_full AS wkd_entryeditedby, wkt.tb_worktime_data.wkd_startdate, wkt.tb_worktime_data.wkd_enddate, wkt.tb_worktime_data.wkd_id,
718 wkt.tb_worktime_data.wkd_st, wkt.tb_worktime_data.wkd_timediff, wkt.tb_worktime_data.wkd_starttime, wkt.tb_worktime_data.wkd_endtime
719FROM wkt.tb_worktime_data INNER JOIN
720 wkt.tb_emp_card_relation ON wkt.tb_worktime_data.wkd_card_id = wkt.tb_emp_card_relation.ecr_card_id INNER JOIN
721 hr.tb_prac ON wkt.tb_emp_card_relation.ecr_emp_id = hr.tb_prac.p_id INNER JOIN
722 hr.tb_workunit ON hr.tb_prac.p_wu_id = hr.tb_workunit.wu_id LEFT OUTER JOIN
723 sec.tb_sys_emps AS tb_sys_emps_1 ON wkt.tb_worktime_data.wkd_entryeditedby = tb_sys_emps_1.e_id LEFT OUTER JOIN
724 sec.tb_sys_emps ON wkt.tb_worktime_data.wkd_exiteditedby = sec.tb_sys_emps.e_id
725GO
726PRINT N'Creating [dbo].[v_voucher_target]...';
727
728
729GO
730CREATE VIEW dbo.v_voucher_target
731AS
732SELECT dbo.tb_klientofwhat.kof_name, dbo.tb_voucher_target.ppid, dbo.tb_voucher_target.pp_vchrid
733FROM dbo.tb_klientofwhat INNER JOIN
734 dbo.tb_voucher_target ON dbo.tb_klientofwhat.kof_id = dbo.tb_voucher_target.pp_purpose_id
735GO
736PRINT N'Creating [rsv].[v_room_type]...';
737
738
739GO
740CREATE VIEW rsv.v_room_type
741AS
742SELECT rsv.resource.res_name, rsv.tb_base_roomtype.brt_name, rsv.resource.res_desc, rsv.resource.res_person_no, rsv.resource.res_id, rsv.resource.res_st
743FROM rsv.resource INNER JOIN
744 rsv.tb_base_roomtype ON rsv.resource.res_room_basetype = rsv.tb_base_roomtype.brt_id
745WHERE (rsv.resource.res_type = 1)
746GO
747PRINT N'Altering [rep].[StdReportCalcData]...';
748
749
750GO
751
752-- =============================================
753-- Author: <Author,,Name>
754-- Create date: <Create Date, ,>
755-- Description: <Description, ,>
756-- =============================================
757ALTER FUNCTION [rep].[StdReportCalcData] (@fielKey varchar(50), @daySerial int )
758
759RETURNS money
760AS
761BEGIN
762
763 declare @retval money=0
764
765
766set @retval =case @fielKey
767 when 'turnover' then
768
769 (select sum(rida_value) from rep.tb_repitemdata where rida_rid_id in (13,14,16) and rida_period=@daySerial)
770
771-- (select sum(rida_value) from rep.tb_repitemdata where rida_period =@daySerial and rida_rid_id in (
772--select rid_id from rep.tb_repitemdef where rid_itemtype=1 and rid_parentid=2
773--))
774 when 'restaurantwithTransfer' then
775 60
776 when 'AvgPerPerson' then
77750
778when 'BreakfastVal' then
77952
780when 'BreakfastValExtra' then
78110
782when 'TransferFuncs' then
78310
784when 'FuncNet' then
785
786 0
787 when 'spa_spa' then
788
789( select sum(rida_value) from rep.tb_repitemdata where rida_period=@daySerial and rida_rid_id in (
790select rid_id from rep.tb_repitemdef where rid_datakey
791 in('spa_entry',
792'spa_treat',
793'spa_massage',
794'spa_cosmetics',
795'spa_oth')
796))
797
798when 'spa_total' then
799
800( select sum(rida_value) from rep.tb_repitemdata where rida_period=@daySerial and rida_rid_id in (
801select rid_id from rep.tb_repitemdef where rid_datakey
802 in('spa_entry',
803'spa_treat',
804'spa_massage',
805'spa_cosmetics',
806'spa_oth',
807'spa_carnet'
808)
809))
810 end
811
812 return @retval
813
814
815END
816GO
817
818GO
819
820
821-- Drop stored procedure if it already exists
822--IF OBJECT_ID('<Schema_Name, sysname, dbo>.<Procedure_Name, sysname, Procedure_Name>','P') IS NOT NULL
823-- DROP PROCEDURE <Schema_Name, sysname, dbo>.<Procedure_Name, sysname, Procedure_Name>
824--GO
825
826ALTER PROCEDURE [rsv].[ReservationInfo]
827 @DayStart int , @DayEnd int
828 AS BEGIN
829 SET NOCOUNT ON;
830
831
832 SELECT rsv.tb_reservation.rsv_id, rsv.tb_reservation_room.rsvrm_id, rsv.tb_reservation_room.rsvrm_start, rsv.tb_reservation_room.rsvrm_end, dbo.tb_klient.kd_namefull, rsv.tb_reservation_room.rsvrm_rm_id
833,
834case when @DayStart>rsvrm_start then
835@DayStart
836else
837rsvrm_start
838end
839as StartDay,
840
841case when @DayEnd <rsvrm_end then
842@DayEnd
843else
844rsvrm_end
845end
846as EndDay,
847case when @DayStart>rsvrm_start then
848dbo.DateDiffSerial( @DayStart, dbo.xmin(@DayEnd, rsv.tb_reservation_room.rsvrm_end)) -1
849else
850dbo.DateDiffSerial( rsvrm_start, dbo.xmin(@DayEnd, rsv.tb_reservation_room.rsvrm_end)) -1
851
852end
853as daycount,
854 rsvrm_rm_id
855FROM rsv.tb_reservation INNER JOIN
856 rsv.tb_reservation_room ON rsv.tb_reservation.rsv_id = rsv.tb_reservation_room.rsvrm_rsv_id left outer JOIN
857 dbo.tb_klient ON rsv.tb_reservation.rsv_reservator = dbo.tb_klient.kd_id
858WHERE (rsv.tb_reservation_room.rsvrm_id IN
859 (SELECT rd_rsvrmid
860 FROM rsv.room_days
861 WHERE (rd_dayid BETWEEN @DayStart AND @DayEnd)))
862
863
864
865
866
867
868
869 END
870GO
871PRINT N'Altering [dbo].[GetKlientContact]...';
872
873
874GO
875
876ALTER PROCEDURE [dbo].[GetKlientContact]
877 @id int
878AS
879BEGIN
880 SET NOCOUNT ON;
881 select kd_namefull,kd_mail,isnull(_sub_tels.tl_no,'')as tl_no from tb_klient left outer join _sub_tels on kd_id = _sub_tels.tlval_id where kd_id= @id
882END
883GO
884PRINT N'Altering [rsv].[GetReservationInfo]...';
885
886
887GO
888-- =============================================
889-- Author: Mikołaj Piecuch
890-- Create date: 2016-10-29
891-- Description: Informacje rezerwacji (tooltip)
892-- =============================================
893ALTER PROCEDURE [rsv].[GetReservationInfo]
894@rsvId INT
895AS
896BEGIN
897 -- SET NOCOUNT ON added to prevent extra result sets from
898 -- interfering with SELECT statements.
899 SET NOCOUNT ON;
900
901 SELECT dbo.tb_klient.kd_namefull, rsv.tb_reservation_source.rsvsr_name, rsv.tb_reservation_status.rsvst_name, rsv.tb_reservation.rsv_entrydate, rsv.tb_reservation.rsv_singularorgroup,
902 sourceRsvKlient.kd_namefull AS Nalezy_do
903FROM rsv.tb_reservation INNER JOIN
904 rsv.tb_reservation_source ON rsv.tb_reservation.rsv_source = rsv.tb_reservation_source.rsvsr_id INNER JOIN
905 rsv.tb_reservation_status ON rsv.tb_reservation.rsv_statusid = rsv.tb_reservation_status.rsvst_id right outer JOIN
906 dbo.tb_klient ON rsv.tb_reservation.rsv_reservator = dbo.tb_klient.kd_id LEFT JOIN
907 rsv.tb_reservation AS sourceRsv ON rsv.tb_reservation.rsv_head = sourceRsv.rsv_id LEFT JOIN
908 dbo.tb_klient AS sourceRsvKlient ON sourceRsv.rsv_reservator = sourceRsvKlient.kd_id WHERE rsv.tb_reservation.rsv_id=@rsvId
909END
910GO
911PRINT N'Altering [rsv].[CalculateExpenses]...';
912
913
914GO
915
916
917
918GO
919-- =============================================
920-- Author: <Author,,Name>
921-- Create date: <Create Date,,>
922-- Description: <Description,,>
923-- =============================================
924ALTER PROCEDURE rsv.GetRoomDesc
925 @rmid int, @cRmDesc varchar(200) out
926AS
927BEGIN
928 SET NOCOUNT ON;
929
930
931 select @cRmDesc= rm_name + ' ('+cast(rm_nr as varchar(5))+')' from rsv.tb_room where rm_id=@rmid
932
933
934
935END
936GO
937PRINT N'Altering [wkt].[ProcessWorkTimes]...';
938
939
940GO
941
942
943ALTER Procedure [dbo].[sys_get_data_lookup] @cd INT, @dist_cdf_name varchar(80),@usr INT ,@filter NVarchar(350)=''
944As
945SET DATEFORMAT dmy
946declare @countlimit varchar(5)='20'
947
948declare @SQLP NVarchar(250), @tabname Nvarchar(50), @distFieldName Nvarchar(50), @lookuptable nvarchar(50)
949
950
951--select @tabname=db_schema+'.'+nazwa_tabeli FROM _def.cust_dims Where cd_id =@cd
952 set @distFieldName =@dist_cdf_name
953
954 --def.cust_dims_defs.nazwa_pola,
955 SELECT @tabname= db_schema+'.'+nazwa_tabeli, @lookuptable=isnull(_def.cust_dims_defs.lookuptbl,'')
956FROM _def.cust_dims INNER JOIN _def.cust_dims_defs ON _def.cust_dims.cd_id = _def.cust_dims_defs.cd_id
957where _def.cust_dims.cd_id=@cd and nazwa_pola=@distFieldName
958
959 if len(@lookuptable)>3
960
961 begin
962 if (len(ltrim(@filter))>3 )
963
964 set @SQLP='SELECT top ' +@countlimit +' '+ @distFieldName + ' FROM ' + @lookuptable + ' WHERE ' + @filter
965 else
966 set @SQLP='SELECT top ' +@countlimit +' '+ @distFieldName + ' FROM ' + @lookuptable
967 end
968else
969 begin
970 if @tabname is null
971 begin
972 SELECT @tabname= db_schema+'.'+nazwa_tabeli FROM _def.cust_dims where _def.cust_dims.cd_id=@cd
973 end
974
975
976 if (len(ltrim(@filter))>3 )
977
978 set @SQLP='SELECT distinct top '+@countlimit +' '+ @distFieldName + ' FROM ' + @tabname + ' WHERE ' + @filter
979 else
980 set @SQLP='SELECT distinct top '+@countlimit +' '+ @distFieldName + ' FROM ' + @tabname
981 end
982
983
984
985
986
987
988
989
990
991-- print @SQLP
992 exec sp_executesql @SQLP
993/*
994select @SQLP
995*/
996GO
997PRINT N'Altering [dbo].[sys_get_dic_multidata]...';
998
999
1000GO
1001ALTER PROCEDURE [dbo].[sys_get_dic_multidata] @cd int, @rel1ID int, @eid int
1002AS
1003BEGIN
1004 -- SET NOCOUNT ON added to prevent extra result sets from
1005 -- interfering with SELECT statements.
1006 SET NOCOUNT ON;
1007--if @cd=126
1008
1009--begin
1010-- SELECT dbo.tb_custsupp.cs_id, dbo.tb_custsupp.cs_kd_id, dbo.tb_supp.d_dostawca, dbo.tb_custsupp.cs_sp_id
1011-- FROM dbo.tb_custsupp INNER JOIN
1012-- dbo.tb_supp ON dbo.tb_custsupp.cs_sp_id = dbo.tb_supp.d_id
1013-- WHERE (dbo.tb_supp.d_top = 1) and cs_kd_id =@rel1ID
1014--union
1015
1016--SELECT 0 AS Expr1, 0 AS Expr2, d_dostawca, d_id
1017--FROM dbo.tb_supp
1018--WHERE (d_top = 1) and d_id not in (
1019--SELECT cs_sp_id
1020--FROM dbo.tb_custsupp where cs_kd_id=@rel1ID
1021
1022--)
1023
1024
1025--end
1026
1027
1028 if @cd=94
1029 begin
1030 SELECT dbo.tb_klient_branza.kb_id, dbo.tb_klient_branza.kbkdr_id, dbo.tb_klient_branza.kbbr_id , dbo.tb_branze.br_branza
1031FROM dbo.tb_klient_branza INNER JOIN
1032 dbo.tb_branze ON dbo.tb_klient_branza.kbbr_id = dbo.tb_branze.br_id
1033WHERE (dbo.tb_klient_branza.kbkdr_id = @rel1ID)
1034
1035union
1036SELECT 0 AS Expr1, 0 AS Expr2, br_id, br_branza
1037FROM dbo.tb_branze where br_id not in (
1038SELECT kbbr_id
1039FROM dbo.tb_klient_branza
1040WHERE (kbkdr_id = @rel1ID)
1041)
1042
1043 end
1044
1045
1046 if @cd=152
1047 begin
1048
1049 SELECT dbo.tb_reminderperson.rmp_id, sec.tb_sys_emps.e_id, dbo.tb_reminderperson.rmp_rm_id as relid, sec.tb_sys_emps.e_name_full
1050FROM dbo.tb_reminderperson INNER JOIN
1051 sec.tb_sys_emps ON dbo.tb_reminderperson.rmp_emp_id = sec.tb_sys_emps.e_id
1052WHERE (dbo.tb_reminderperson.rmp_rm_id = @rel1ID)
1053union
1054
1055select 0 as rmp_id, sec.tb_sys_emps.e_id, 0 as relid , sec.tb_sys_emps.e_name_full
1056from sec.tb_sys_emps where sec.tb_sys_emps.e_id not in (
1057SELECT [rmp_emp_id]
1058 FROM [dbo].[tb_reminderperson] where [rmp_emp_id]=@rel1ID
1059
1060)
1061
1062 end
1063
1064
1065
1066
1067 if @cd=706
1068 begin
1069
1070SELECT spa.tb_spastation_service.ss_id,spa.tb_spastation_service.ss_station_id , spa.tb_spastation_service.ss_service_id, spa.tb_spa_services.spsv_name
1071FROM spa.tb_spastation_service INNER JOIN
1072 spa.tb_spa_services ON spa.tb_spastation_service.ss_service_id = spa.tb_spa_services.spsv_id
1073where spa.tb_spastation_service.ss_station_id=@rel1ID
1074
1075 union
1076
1077 SELECT 0 AS ss_id, 0 AS ss_station_id, spsv_id, spsv_name
1078FROM spa.tb_spa_services where spsv_id not in (
1079SELECT ss_service_id
1080FROM spa.tb_spastation_service where ss_station_id=@rel1ID
1081
1082)
1083 end
1084
1085 if @cd=708
1086 begin
1087
1088SELECT spa.tb_spa_reservation_item.sri_id, spa.tb_spa_reservation_item.srisrsv_id, spa.tb_spa_reservation_item.sri_serviceid, spa.tb_spa_services.spsv_name, spsv_parent
1089FROM spa.tb_spa_reservation_item INNER JOIN
1090 spa.tb_spa_services ON spa.tb_spa_reservation_item.sri_serviceid = spa.tb_spa_services.spsv_id
1091WHERE (spa.tb_spa_reservation_item.srisrsv_id = @rel1ID)
1092
1093 union
1094
1095SELECT 0 as sri_id, 0 as srisrsv_id, spsv_id, spsv_name, spsv_parent
1096FROM spa.tb_spa_services
1097WHERE (spsv_active = 1) and spsv_id not in(
1098SELECT sri_serviceid
1099FROM spa.tb_spa_reservation_item
1100WHERE (srisrsv_id = @rel1ID)
1101 )
1102 end
1103
1104 --srisrsv_id
1105 --klientOfWhat
1106if @cd=711
1107 begin
1108
1109SELECT dbo.tb_klient_purpose.kp_id, dbo.tb_klient_purpose.kp_kd_id,kp_purpose_id, dbo.tb_klientofwhat.kof_name
1110FROM dbo.tb_klient_purpose INNER JOIN
1111 dbo.tb_klientofwhat ON dbo.tb_klient_purpose.kp_purpose_id = dbo.tb_klientofwhat.kof_id
1112 where (kp_kd_id=@rel1ID)
1113
1114 union
1115
1116 SELECT 0 as kp_id, 0 as kp_kd_id, kof_id as kp_purpose_id, kof_name
1117FROM dbo.tb_klientofwhat
1118where kof_id not in (
1119SELECT
1120 [kp_purpose_id]
1121 FROM [dbo].[tb_klient_purpose] where [kp_kd_id]=@rel1ID
1122)
1123
1124
1125 end
1126
1127 --product uses
1128if @cd=121
1129 begin
1130
1131SELECT inv.tb_product_uses.p_pu_id, inv.tb_product_uses.p_pu_product, inv.tb_product_uses.p_pu_use, inv.tb_product_use.pu_name
1132FROM inv.tb_product_uses INNER JOIN
1133 inv.tb_product_use ON inv.tb_product_uses.p_pu_use = inv.tb_product_use.pu_id
1134WHERE (inv.tb_product_uses.p_pu_product = 1)
1135
1136 union
1137
1138 SELECT 0 as p_pu_id, 0 as p_pu_product, pu_id as p_pu_use, pu_name
1139FROM inv.tb_product_use
1140where pu_id not in (
1141SELECT
1142 p_pu_use
1143 FROM inv.tb_product_uses where p_pu_product=1
1144)
1145 end
1146
1147
1148--------------reminder - colors
1149 --
1150if @cd=157
1151 begin
1152
1153--SELECT dbo.tb_klient_purpose.kp_id, dbo.tb_klient_purpose.kp_kd_id,kp_purpose_id, dbo.tb_klientofwhat.kof_name
1154--FROM dbo.tb_klient_purpose INNER JOIN
1155-- dbo.tb_klientofwhat ON dbo.tb_klient_purpose.kp_purpose_id = dbo.tb_klientofwhat.kof_id
1156-- where (kp_kd_id=@rel1ID)
1157
1158SELECT dbo._sub_univd_data.uvd_id, dbo._sub_univd_data.uvdval_id as relid, dbo._sub_univd.unvd_id, dbo._sub_univd.unvd_cval, dbo._sub_univd.unvd_idata
1159FROM dbo._sub_univd_data INNER JOIN
1160 dbo._sub_univd ON dbo._sub_univd_data.uvref_id = dbo._sub_univd.unvd_id
1161where dbo._sub_univd_data.uvdval_id=@rel1ID
1162
1163 union
1164
1165 SELECT 0 as uvd_id, 0 as relid, unvd_id, unvd_cval, unvd_idata
1166FROM dbo._sub_univd
1167where unvd_id not in (
1168SELECT uvref_id
1169FROM dbo._sub_univd_data
1170WHERE (uvdval_id = @rel1ID)
1171)
1172
1173
1174 end
1175
1176
1177
1178
1179
1180if @cd=287
1181 begin
1182
1183SELECT dbo.tb_voucher_target.ppid, dbo.tb_voucher_target.pp_vchrid,pp_purpose_id, dbo.tb_klientofwhat.kof_name
1184FROM dbo.tb_voucher_target INNER JOIN
1185 dbo.tb_klientofwhat ON dbo.tb_voucher_target.pp_purpose_id = dbo.tb_klientofwhat.kof_id
1186 where (pp_vchrid=@rel1ID)
1187
1188 union
1189
1190 SELECT 0 as kp_id, 0 as pp_vchrid_id, kof_id as kp_purpose_id, kof_name
1191FROM dbo.tb_klientofwhat
1192where kof_id not in (
1193SELECT
1194 pp_purpose_id
1195 FROM [dbo].tb_voucher_target where pp_vchrid=@rel1ID
1196)
1197
1198
1199 end
1200
1201 END
1202GO
1203PRINT N'Altering [hr].[rep_worktime_stat3]...';
1204
1205
1206GO
1207
1208
1209-- =============================================
1210-- Author: <Author,,Name>
1211-- Create date: <Create Date,,>
1212-- Description: <Description,,>
1213-- =============================================
1214ALTER PROCEDURE [hr].[rep_worktime_stat3]
1215( @unit int,@StartSerial int, @EndSerial int, @eid int, @iHarmOrWkt int=0)
1216AS
1217BEGIN
1218 SET NOCOUNT ON;
1219
1220 -- select * from rep_workday3 ( @StartSerial,@EndSerial ,@unit)
1221
1222
1223 -- (@StartSerial int, @EndSerial int, @unit int)
1224 declare @rep TABLE
1225( prac varchar(80),
1226 prac_hour int DEFAULT( 0 ),
1227 abs_hour int DEFAULT( 0 ),
1228 url_hour int DEFAULT(0),
1229 chor_hour int DEFAULT(0),
1230 night_hour int DEFAULT(0),
1231
1232 prac_day int DEFAULT(0),
1233 fday_day int DEFAULT(0),
1234 sund_day int DEFAULT(0),
1235 wolne_day int DEFAULT(0),
1236 abs_day int DEFAULT(0),
1237 url_day int DEFAULT(0),
1238 chor_day int DEFAULT(0),
1239 wrkTotal int DEFAULT(0) )
1240
1241declare @Ptyp int, @Atyp int,@Utyp int,@Ctyp int
1242declare @Pdcount int, @Ddcount int,@Ndcount int,@Wdcount int,@Adcount int,@Udcount int,@Cdcount int
1243declare @NightWt int
1244
1245declare @workhours int, @workdays int, @saturdays int, @sundays int, @abshours int, @absdays int
1246declare @chorhour int, @nighthour int, @urlhour int, @urldays int
1247
1248
1249declare @BaseWorkDayCount int
1250 --SELECT @BaseWorkDayCount= count( dayserial) FROM dict.days where datemonth = left(cast(@StartSerial as char(8)),6) and (DATEPART ( dw , daydate )between 2 and 7 ) --and daytyp=1
1251--SELECT ISNULL(p_wc,0)as wc,@BaseWorkDayCount* ISNULL(p_wc,0) as emp_basewrkhour from dbo.tb_prac where dbo.tb_prac.p_id=@praciID
1252 SELECT @BaseWorkDayCount= count( dayserial) FROM dict.days where dict.days.dayserial between @StartSerial and @EndSerial and (DATEPART ( dw , daydate )between 2 and 6 )
1253
1254
1255
1256
1257declare @prac varchar(80),@praciID int,@wc money -- , @workmin int, @daytyp char(1)
1258DECLARE db_cursor CURSOR FOR
1259
1260SELECT p_id, p_name_full as prac , ISNULL(p_cvs_id,0)as wc
1261FROM hr.tb_prac
1262inner join [hr].[tb_workunit] as wu on wu.[wu_id] = hr.tb_prac.p_wu_id
1263WHERE ([wu_parent_id] = @unit) and p_id in (
1264SELECT hrd_emp_id
1265FROM hr.tb_HRdocument
1266WHERE (dbo.DaySerial( isnull(hrd_end,cast( getdate() as date)) , 0) >= @StartSerial)
1267)
1268
1269
1270
1271--and
1272--( dbo.tb_prac.p_dat_kon_serial is null or dbo.tb_prac.p_dat_kon_serial >= @StartSerial)
1273
1274
1275OPEN db_cursor
1276FETCH NEXT FROM db_cursor INTO @praciID, @prac, @wc
1277
1278WHILE @@FETCH_STATUS = 0
1279BEGIN
1280
1281--P
1282
1283--select @Ptyp= sum(wt_time) , @Pdcount=COUNT(wthdd_id) from [dbo].tf_harmdata_temp(@StartSerial , @EndSerial
1284--,@praciID ) where wth_daytyp='P'
1285--SELECT @Ptyp= (SUM(dbo.tb_wkt.wt_time))-- + SUM(ISNULL(t.wtr_xtime, 0)))
1286--FROM dbo.tb_worktime AS t INNER JOIN
1287-- dbo.tb_wkt ON t.wtr_tcode = dbo.tb_wkt.wkcode
1288--WHERE (t.wtrdd_id BETWEEN @StartSerial AND @EndSerial) AND (ISNULL(t.wtr_extdocID, 0) = 0) and wtrpr_id =@praciID
1289-- AND ISNULL( t.wtr_abs_id,0)=0
1290
1291
1292
1293if @iHarmOrWkt=0
1294 begin
1295--HARMONOGRAM
1296
1297
1298
1299 SELECT @workhours = SUM(dbo.tb_wkt.wt_time) , @workdays= COUNT(dbo.tb_worktimehrm.wth_id)
1300 FROM dbo.tb_worktimehrm INNER JOIN
1301 dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode
1302 WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id =@praciID) and (ISNULL(dbo.tb_worktimehrm.wth_extdocID, 0) = 0) and tb_wkt.wkcode>0
1303 set @abshours=0
1304 set @absdays=0
1305 set @chorhour=0
1306
1307--declare @workhours int, @workdays int, @saturdays int, @sundays int, @abshours int, @absdays int
1308--declare @chorhour int, @nighthour int, @urlhour int, @urldays int
1309 SELECT @urlhour = SUM(dbo.tb_wkt.wt_time) , @urldays= COUNT(dbo.tb_worktimehrm.wth_id)
1310 FROM dbo.tb_worktimehrm INNER JOIN
1311 dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode
1312 WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id =@praciID) and (ISNULL(dbo.tb_worktimehrm.wth_extdocID, 0) > 0) and tb_wkt.wkcode>0
1313
1314
1315 select @saturdays=COUNT(wthdd_id) from hr.tf_harmdata_temp(@StartSerial , @EndSerial
1316,@praciID ) where wth_daytyp='D'
1317
1318
1319SELECT @saturdays= COUNT(dbo.tb_worktimehrm.wth_id)
1320FROM dbo.tb_worktimehrm INNER JOIN
1321 dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode INNER JOIN
1322 dict.days ON dbo.tb_worktimehrm.wthdd_id = dict.days.dayserial
1323WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id = @praciID) AND (ISNULL(dbo.tb_worktimehrm.wth_extdocID, 0) = 0) AND (dbo.tb_wkt.wkcode > 0) AND
1324 (DATEPART(dw, dict.days.daydate) = 7)
1325
1326
1327SELECT @sundays= COUNT(dbo.tb_worktimehrm.wth_id)
1328FROM dbo.tb_worktimehrm INNER JOIN
1329 dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode INNER JOIN
1330 dict.days ON dbo.tb_worktimehrm.wthdd_id = dict.days.dayserial
1331WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id = @praciID) AND (ISNULL(dbo.tb_worktimehrm.wth_extdocID, 0) = 0) AND (dbo.tb_wkt.wkcode > 0) AND
1332 (DATEPART(dw, dict.days.daydate) = 1)
1333
1334
1335end
1336else
1337begin
1338--WORKTIME
1339 SELECT @workhours = SUM(dbo.tb_wkt.wt_time) , @workdays= COUNT(dbo.tb_worktime.wtr_id)
1340 FROM dbo.tb_worktime INNER JOIN
1341 dbo.tb_wkt ON dbo.tb_worktime.wtr_tcode = dbo.tb_wkt.wkcode
1342 WHERE (dbo.tb_worktime.wtrdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktime.wtrpr_id =@praciID) and (ISNULL(dbo.tb_worktime.wtr_extdocID, 0) = 0) and tb_wkt.wkcode>0
1343
1344
1345end
1346
1347--declare @P1 int, @P2 int
1348--SELECT @P1=SUM(dbo.tb_wkt.wt_time)/60
1349--FROM dbo.tb_worktimehrm INNER JOIN
1350-- dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode
1351--WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id = @praciID) AND (ISNULL(dbo.tb_worktimehrm.wth_extdocID, 0) = 0) AND (dbo.tb_worktimehrm.wth_daytyp = 'P')
1352
1353
1354
1355
1356
1357--set @Ptyp=@P1
1358
1359
1360
1361 --select @Atyp= sum(wt_time) from [dbo].tf_harmdata_temp(@StartSerial , @EndSerial
1362--,@praciID ) where wth_daytyp='A'
1363-- SELECT @Ptyp = SUM(dbo.tb_wkt.wt_time) , @Atyp= COUNT(dbo.tb_worktimehrm.wth_id)
1364
1365--FROM dbo.tb_worktimehrm INNER JOIN
1366-- dbo.tb_wkt ON dbo.tb_worktimehrm.wth_tcode = dbo.tb_wkt.wkcode INNER JOIN
1367-- dbo.tb_urlopwn ON dbo.tb_worktimehrm.wth_abs_id = dbo.tb_urlopwn.uw_id
1368--WHERE (dbo.tb_worktimehrm.wthdd_id BETWEEN @StartSerial AND @EndSerial) AND (dbo.tb_worktimehrm.wthpr_id =@praciID)
1369
1370--NightTime
1371
1372SELECT @NightWt= (SUM( wt_nighttime)) FROM dbo.tb_worktime AS t INNER JOIN
1373 dbo.tb_wkt ON t.wtr_tcode = dbo.tb_wkt.wkcode
1374WHERE (t.wtrdd_id BETWEEN @StartSerial AND @EndSerial) AND (ISNULL(t.wtr_extdocID, 0) = 0) and wtrpr_id =@praciID
1375if @NightWt>0
1376begin
1377print @NightWt
1378end
1379
1380
1381--U
1382-- select @Utyp= sum(wt_time) , @Udcount=COUNT(wthdd_id) from [dbo].tf_harmdata_temp(@StartSerial , @EndSerial ,@praciID ) where wth_daytyp='U'
1383--select @Utyp=0
1384----C
1385-- select @Ctyp= sum(wt_time) , @Cdcount=COUNT(wthdd_id) from hr.tf_harmdata_temp(@StartSerial , @EndSerial
1386--,@praciID ) where wth_daytyp='C'
1387
1388
1389--declare @saturdays int, @sundays int, @abshours int, @absdays int
1390
1391--D
1392
1393--W
1394 --@wc
1395
1396insert into @rep
1397(prac,
1398prac_hour,abs_hour,url_hour,chor_hour,night_hour,
1399prac_day,fday_day,
1400 sund_day , wolne_day,abs_day, url_day, chor_day, wrkTotal )
1401 values(
1402@prac,
1403 isnull( @workhours,0), isnull(@abshours,0) ,isnull(@urlhour,0), isnull(@chorhour,0), isnull(@nighthour,0),
1404
1405isnull(@workdays,0),
1406isnull(@saturdays,0),@sundays,@urldays,@Adcount,@Udcount,@Cdcount, @BaseWorkDayCount*@wc*60 *8 )
1407
1408 FETCH NEXT FROM db_cursor INTO @praciID, @prac, @wc
1409END
1410
1411CLOSE db_cursor
1412DEALLOCATE db_cursor
1413
1414
1415
1416 select * from @rep order by prac
1417
1418END
1419GO
1420PRINT N'Altering [rsv].[DayInfo]...';
1421
1422
1423GO
1424
1425-- Drop stored procedure if it already exists
1426--IF OBJECT_ID('<Schema_Name, sysname, dbo>.<Procedure_Name, sysname, Procedure_Name>','P') IS NOT NULL
1427-- DROP PROCEDURE <Schema_Name, sysname, dbo>.<Procedure_Name, sysname, Procedure_Name>
1428--GO
1429
1430ALTER PROCEDURE [rsv].[DayInfo]
1431 @DayStart int , @DayEnd int
1432 AS BEGIN
1433 SET NOCOUNT ON;
1434 SELECT dict.days.dayserial, dict.days.daydate, dict.days.daytyp, dict.days.datemonth, dict.days.dayno, dict.days.monthno, dict.days.yearno, dict.days.dayofweek, ISNULL(dict.tb_holidays.hd_freeday, 0)
1435 AS daystatus
1436FROM dict.days LEFT OUTER JOIN
1437 dict.tb_holidays ON dict.days.dayserial = dict.tb_holidays.hd_dayserial
1438
1439 where dayserial between @DayStart and @DayEnd
1440 END
1441GO
1442PRINT N'Altering [rsv].[FindModifiedExpenses]...';
1443
1444
1445GO
1446
1447-- =============================================
1448-- Author: Mikolaj Piecuch
1449-- Create date: 2016-05-14
1450-- Description: zapis pokoi rezerwacji
1451-- =============================================
1452ALTER PROCEDURE [rsv].[SaveReservationRooms]
1453 @rsvId INT, @rmId INT, @end INT, @start INT, @info NVARCHAR(250)
1454AS
1455BEGIN
1456 -- SET NOCOUNT ON added to prevent extra result sets from
1457 -- interfering with SELECT statements.
1458 SET NOCOUNT ON;
1459 DECLARE @rsvrmid INT
1460
1461 IF NOT EXISTS (SELECT * FROM rsv.tb_reservation_room WHERE rsvrm_rm_id=@rmId AND ((@start BETWEEN rsvrm_start AND rsvrm_end-1) OR (@END BETWEEN rsvrm_start AND rsvrm_end)))
1462 BEGIN
1463 INSERT INTO rsv.tb_reservation_room ([rsvrm_rsv_id],[rsvrm_rm_id],[rsvrm_start],[rsvrm_end],[rsvrm_info],[rsvrm_st])
1464 VALUES (@rsvId, @rmId, @start, @end, @info, 1)
1465
1466 SELECT @rsvrmid = SCOPE_IDENTITY()
1467 INSERT INTO rsv.room_days ([rd_dayid],[rd_rsvrmid],[rd_starthotelday],[rd_st]) VALUES(@start, @rsvrmid, 2, 1)
1468 INSERT INTO rsv.room_days ([rd_dayid],[rd_rsvrmid],[rd_starthotelday],[rd_endhotelday],[rd_st])
1469 SELECT dayserial, @rsvrmid, 2, 2, 1 FROM dict.days WHERE dayserial BETWEEN @start+1 AND @end-1
1470 INSERT INTO rsv.room_days ([rd_dayid],[rd_rsvrmid],[rd_endhotelday],[rd_st]) VALUES(@end, @rsvrmid, 2, 1)
1471
1472 END
1473END
1474GO
1475PRINT N'Creating [dbo].[voucher_logreg]...';
1476
1477
1478GO
1479-- =============================================
1480-- Author: <gsue>
1481-- Create date: <2017-11-02>
1482-- Description: <rejestruje zapis w logu operacji na voucherach>
1483-- =============================================
1484CREATE PROCEDURE voucher_logreg
1485 @voucherItemId int, @voucherDesc varchar(250), @eid int, @opdate datetime =null
1486 AS
1487BEGIN
1488 SET NOCOUNT ON;
1489
1490 insert into [tb_voucher_log] (vchr_itemid, vchr_operation, vchr_eid, vchr_opdate)
1491 values(
1492 @voucherItemId, @voucherDesc, @eid,isnull( @opdate, getdate())
1493 )
1494
1495
1496END
1497GO
1498PRINT N'Creating [dbo].[voucher_multiply]...';
1499
1500
1501GO
1502
1503-- =============================================
1504-- Author: <gsue>
1505-- Create date: <2017-11-12>
1506-- Description: <duplikuje voucher>
1507-- =============================================
1508CREATE PROCEDURE [dbo].voucher_multiply
1509 @SourceVoucherItemId int, @eid int, @vcode varchar(25)
1510 AS
1511BEGIN
1512 SET NOCOUNT ON;
1513
1514
1515 insert into dbo.tb_voucherItem (
1516 vchri_vcid, vchri_val, vchri_kod, vchri_status, vchri_consdate, vchri_strt, vchri_end, vchri_rest, vchri_hotel, vchri_spa, vchri_st, vchri_text, vchri_eid, vchri_date
1517 )
1518
1519 SELECT vchri_vcid, vchri_val, @vcode, vchri_status, vchri_consdate, vchri_strt, vchri_end, vchri_rest, vchri_hotel, vchri_spa, vchri_st, vchri_text, @eid, getdate()
1520FROM dbo.tb_voucherItem where vchri_id = @SourceVoucherItemId
1521
1522
1523
1524
1525
1526END
1527GO
1528PRINT N'Creating [rep].[rep_rebuild]...';
1529
1530
1531GO
1532-- =============================================
1533-- Author: <Author,,Name>
1534-- Create date: <Create Date,,>
1535-- Description: <Description,,>
1536-- =============================================
1537CREATE PROCEDURE [rep].[rep_rebuild]
1538@groupID int, @DaySerial int
1539AS
1540BEGIN
1541 SET NOCOUNT ON;
1542
1543declare @rid int, @funsum nvarchar(500)
1544
1545DECLARE db_cursor CURSOR FOR
1546
1547select rid_id, rid_sqlformula from rep.tb_repitemdef where rid_itemtype>1 and rid_parentid=@groupID
1548
1549OPEN db_cursor
1550FETCH NEXT FROM db_cursor INTO @rid,@funsum
1551
1552WHILE @@FETCH_STATUS = 0
1553BEGIN
1554
1555declare @sq nvarchar(500), @res money
1556DECLARE @ParmDefinition NVARCHAR(500)
1557
1558--set @funsum ='select sum(rida_value) from rep.tb_repitemdata where rida_rid_id in (13,14,16) and rida_period=var_date'
1559
1560
1561 set @sq=replace( @funsum,'var_date', @DaySerial)
1562
1563
1564 --- @mval - stala nazwa zmienne w param sql
1565
1566
1567 --DECLARE @SQLString NVARCHAR(500)
1568
1569
1570--SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
1571 -- FROM pubs.dbo.employee WHERE job_lvl = @level'
1572
1573 -- SET @SQLString = N'select @mval =sum(rida_value) from rep.tb_repitemdata where rida_rid_id in (13,14,16) and rida_period=@var_date'
1574SET @ParmDefinition = N'@mval money OUTPUT'
1575
1576EXECUTE sp_executesql
1577@sq,
1578@ParmDefinition,
1579@mval=@res OUTPUT
1580
1581 --exec @res=sp_executesql @sq
1582
1583
1584
1585
1586
1587 --select @res
1588 -- select * from rep.tb_repitemdata where rida_rid_id=17 and rida_period=20170501
1589 declare @rida int, @ridaval money
1590 select @rida=rida_id, @ridaval=rida_value from rep.tb_repitemdata where rida_rid_id=@rid and rida_period=@DaySerial
1591
1592 if(@rida>0)
1593 begin
1594 if @res<>@ridaval
1595 begin
1596 update rep.tb_repitemdata set rida_value=@res where rida_id= @rida
1597 end
1598 end
1599 else
1600begin
1601 insert into rep.tb_repitemdata (rida_rid_id, rida_period, rida_value) values( @rid, @DaySerial, @res)
1602 end
1603
1604 FETCH NEXT FROM db_cursor INTO @rid,@funsum
1605END
1606
1607CLOSE db_cursor
1608DEALLOCATE db_cursor
1609
1610END
1611GO
1612PRINT N'Creating [rsv].[GetItemPrice]...';
1613
1614
1615GO
1616CREATE PROCEDURE [rsv].[GetItemPrice]
1617 @pl_id int,
1618 @res_id int,
1619 @start int,
1620 @end int
1621
1622AS
1623BEGIN
1624 -- SET NOCOUNT ON added to prevent extra result sets from
1625 -- interfering with SELECT statements.
1626 SET NOCOUNT ON;
1627
1628 -- Insert statements for procedure here
1629
1630
1631
1632SELECT TOP (100) PERCENT ISNULL(rsv.tb_price_list_roomitem.ptd_nettoprice, ISNULL(rsv.resource.res_price, rsv.tb_room.rm_price)) AS nettoprice,
1633 rsv.tb_price_list_roomitem.ptd_dayserial
1634FROM rsv.tb_room INNER JOIN
1635 rsv.resource ON rsv.tb_room.rm_type = rsv.resource.res_id LEFT OUTER JOIN
1636 rsv.tb_price_list_roomitem ON rsv.resource.res_type = rsv.tb_price_list_roomitem.ptd_root_id
1637WHERE (rsv.tb_price_list_roomitem.ptd_pl_id = @pl_id) AND (rsv.tb_price_list_roomitem.ptd_dayserial BETWEEN @start AND @end) AND
1638(rsv.tb_room.rm_id = @res_id)
1639union
1640SELECT ISNULL(rsv.resource.res_price, rsv.tb_room.rm_price) AS nettoprice, dict.days.dayserial
1641FROM rsv.resource INNER JOIN
1642 rsv.tb_room ON rsv.resource.res_id = rsv.tb_room.rm_type CROSS JOIN
1643 dict.days
1644WHERE (rsv.tb_room.rm_id = @res_id) AND (dict.days.dayserial BETWEEN @start AND @end) AND (dict.days.dayserial NOT IN
1645 (SELECT rsv.tb_price_list_roomitem.ptd_dayserial
1646 FROM rsv.tb_room AS tb_room_1 INNER JOIN
1647 rsv.resource AS resource_1 ON tb_room_1.rm_type = resource_1.res_id LEFT OUTER JOIN
1648 rsv.tb_price_list_roomitem ON resource_1.res_type = rsv.tb_price_list_roomitem.ptd_root_id
1649 WHERE (rsv.tb_price_list_roomitem.ptd_pl_id = @pl_id) AND
1650 (rsv.tb_price_list_roomitem.ptd_dayserial BETWEEN @start AND @end) AND (tb_room_1.rm_id = @res_id)))
1651ORDER BY rsv.tb_price_list_roomitem.ptd_dayserial
1652 END
1653GO
1654PRINT N'Creating [rsv].[GetPriceLevels]...';
1655
1656
1657GO
1658
1659
1660-- =============================================
1661-- Author: Grzegorz
1662-- Create date: 2017-03-24
1663-- Description: Lista dostepnych poziomow cenowych w okresie - procedura dla rezerwacji
1664-- =============================================
1665CREATE PROCEDURE [rsv].[GetPriceLevels]
1666@start int , @rmId int
1667 AS
1668BEGIN
1669
1670 SELECT [pl_id]
1671 ,[pl_code]
1672 ,[pl_st]
1673 ,[pl_startday]
1674 ,[pl_endday]
1675 ,[pl_currency]
1676 FROM [rsv].[tb_price_list] where pl_startday <=@start and pl_endday>=@start
1677
1678 END
1679GO
1680PRINT N'Creating [rsv].[GetPricelistRoomPrices]...';
1681
1682
1683GO
1684
1685-- =============================================
1686-- Author: Piotr
1687-- Create date: 2017-02-13
1688-- Description: zczytanie cen typów pokoi dla podanego zakresu czasowego oraz poziomu cenowego
1689-- =============================================
1690CREATE PROCEDURE [rsv].[GetPricelistRoomPrices]
1691@start int,
1692@end int,
1693@pl_id int
1694AS
1695BEGIN
1696declare
1697@root int,
1698@root_name nvarchar(100)
1699declare
1700@res table
1701(
1702ptd_root_id int,
1703res_name nvarchar(100),
1704ptd_nettoprice money,
1705ptd_dayserial int,
1706ptd_id int
1707)
1708 SET NOCOUNT ON;
1709DECLARE db_cursor CURSOR FOR
1710SELECT rsv.resource.res_id
1711FROM rsv.resource
1712where rsv.resource.res_type = 1
1713OPEN db_cursor
1714FETCH NEXT FROM db_cursor INTO @root
1715
1716WHILE @@FETCH_STATUS = 0
1717BEGIN
1718select @root_name = res_name from rsv.resource where res_id = @root
1719 insert into @res (ptd_root_id,res_name, ptd_nettoprice, ptd_dayserial,ptd_id) SELECT @root AS Expr1,@root_name, rsv.tb_price_list_roomitem.ptd_nettoprice, dict.days.dayserial, rsv.tb_price_list_roomitem.ptd_id
1720FROM rsv.tb_price_list_roomitem INNER JOIN
1721 dict.days ON rsv.tb_price_list_roomitem.ptd_dayserial = dict.days.dayserial
1722WHERE (rsv.tb_price_list_roomitem.ptd_pl_id = @pl_id) AND (rsv.tb_price_list_roomitem.ptd_root_id = @root) AND (dict.days.dayserial BETWEEN @start AND @end)
1723union
1724SELECT @root AS Expr1, @root_name AS Expr3, 0 AS Expr4, dict.days.dayserial, 0 AS Expr2
1725FROM dict.days
1726WHERE (dict.days.dayserial BETWEEN @start AND @end) and dict.days.dayserial not in
1727(
1728SELECT dict.days.dayserial
1729FROM rsv.tb_price_list_roomitem INNER JOIN
1730 rsv.resource ON rsv.tb_price_list_roomitem.ptd_root_id = rsv.resource.res_id INNER JOIN
1731 dict.days ON rsv.tb_price_list_roomitem.ptd_dayserial = dict.days.dayserial
1732WHERE (rsv.tb_price_list_roomitem.ptd_pl_id = @pl_id) AND (rsv.tb_price_list_roomitem.ptd_root_id = @root) AND (dict.days.dayserial BETWEEN @start AND @end)
1733)
1734
1735
1736FETCH NEXT FROM db_cursor INTO @root
1737
1738END
1739
1740CLOSE db_cursor
1741DEALLOCATE db_cursor
1742select * from @res order by ptd_root_id, ptd_dayserial asc
1743END
1744GO
1745PRINT N'Creating [rsv].[GetReservationInfoRaw]...';
1746
1747
1748GO
1749-- =============================================
1750-- Author: Mikołaj Piecuch
1751-- Create date: 2016-10-29
1752-- Description: Informacje rezerwacji (tooltip)
1753-- =============================================
1754
1755create PROCEDURE [rsv].[GetReservationInfoRaw]
1756@rsvId INT
1757AS
1758BEGIN
1759 -- SET NOCOUNT ON added to prevent extra result sets from
1760 -- interfering with SELECT statements.
1761 SET NOCOUNT ON;
1762
1763 SELECT *
1764FROM rsv.tb_reservation WHERE rsv.tb_reservation.rsv_id=@rsvId
1765END
1766GO
1767PRINT N'Creating [rsv].[GetRoomPriceTypDays]...';
1768
1769
1770GO
1771
1772-- =============================================
1773CREATE PROCEDURE [rsv].[GetRoomPriceTypDays]
1774 @startdate INT,
1775 @enddate INT,
1776 @cennikId INT,
1777 @pTypId int
1778AS
1779BEGIN
1780 -- SET NOCOUNT ON added to prevent extra result sets from
1781 -- interfering with SELECT statements.
1782 SET NOCOUNT ON;
1783
1784
1785 if @cennikId=0
1786 begin
1787select dayserial as ptd_dayserial, 0.0 as ptd_nettoprice from dict.days where (dayserial >= @startdate) AND (dayserial <= @enddate)
1788
1789 end
1790 else
1791 begin
1792 SELECT ptd_dayserial, ptd_nettoprice
1793FROM rsv.tb_price_list_roomitem
1794WHERE (ptd_dayserial >= @startdate) AND (ptd_dayserial <= @enddate) and ptd_pl_id=@cennikId and ptd_root_id=@pTypId
1795 end
1796
1797 END
1798GO
1799PRINT N'Creating [rsv].[ReservationMealsGenerate]...';
1800
1801
1802GO
1803-- =============================================
1804-- Author: <gsue>
1805-- Create date: <2017-09-22>
1806-- Description: <generuje posilki >
1807-- =============================================
1808CREATE PROCEDURE rsv.ReservationMealsGenerate
1809@rsvId int, @mealId int
1810AS
1811BEGIN
1812
1813 SET NOCOUNT ON;
1814
1815 declare @perscount int
1816 declare @dayCount int
1817 select @perscount=count(0) from rsv.tb_reserv_person where rp_rsvid=@rsvId
1818
1819
1820
1821declare @dSt int, @dNd int
1822select @dSt=rsv_start, @dNd= rsv_end from rsv.tb_reservation
1823 where rsv_id=@rsvId
1824select @dayCount= count(0) from dict.days where dict.days.dayserial between
1825@dSt and @dNd
1826
1827if @dayCount>0
1828begin
1829 set @dayCount=@dayCount-1
1830end
1831
1832
1833
1834
1835
1836END
1837GO
1838PRINT N'Creating [rsv].[rsv_confirmation]...';
1839
1840
1841GO
1842
1843
1844
1845-- =============================================
1846-- Author: <Author,,Name>
1847-- Create date: <Create Date,,>
1848-- Description: <Description,,>
1849-- =============================================
1850CREATE PROCEDURE [rsv].[rsv_confirmation]
1851@rsvId int
1852AS
1853BEGIN
1854
1855 SET NOCOUNT ON;
1856
1857declare @iShareID int=0
1858
1859-- SELECT @iShareID= isnull(extf_shares,0)
1860--FROM ext.tbext_fidelio_reserv where extf_rsid=@rsvId
1861
1862
1863
1864if @iShareID=0
1865begin
1866--SELECT dbo.tb_klient.kd_name, dbo.tb_klient.kd_surname, ext.tbext_fidelio_reserv.extf_anreise, ext.tbext_fidelio_reserv.extf_abreise, ext.tbext_fidelio_reserv.extf_rsid, dbo.tb_klient.kd_town, dbo.tb_klient.kd_ul,
1867-- dbo.tb_klient.kd_tel, dbo.tb_klient.kd_mail, ext.tbext_fidelio_reserv.extf_roomnumber, dbo.tb_klient.kd_pass, dbo.tb_klient.kd_carreg
1868--FROM ext.tbext_fidelio_reserv INNER JOIN
1869-- dbo.tb_klient ON ext.tbext_fidelio_reserv.extf_kdid = dbo.tb_klient.kd_id
1870-- WHERE (ext.tbext_fidelio_reserv.extf_rsid = @rsvId)
1871SELECT rsv.tb_reservation.rsv_start, dbo.tb_klient.kd_name, dbo.tb_klient.kd_surname, dbo.tb_klient.kd_town, dbo.tb_klient.kd_ul, rsv.tb_reservation.rsv_carreg, rsv.tb_reservation.rsv_end,
1872
1873ISNULL((select rm_nr from rsv.tb_reservation_room
1874left join rsv.tb_room on rsvrm_rm_id=rm_id
1875 where rsvrm_rsv_id=@rsvId ), '') as room
1876 , dbo.tb_klient.kd_tel, dbo.tb_klient.kd_mail
1877
1878
1879
1880FROM dbo.tb_klient INNER JOIN
1881 rsv.tb_reservation ON dbo.tb_klient.kd_id = rsv.tb_reservation.rsv_reservator
1882WHERE (rsv.tb_reservation.rsv_id = @rsvId)
1883
1884 end
1885
1886else
1887begin
1888SELECT rsv.tb_reservation.rsv_start, dbo.tb_klient.kd_name, dbo.tb_klient.kd_surname, dbo.tb_klient.kd_town, dbo.tb_klient.kd_ul, rsv.tb_reservation.rsv_carreg, rsv.tb_reservation.rsv_end,
1889
1890ISNULL((select rm_nr from rsv.tb_reservation_room
1891left join rsv.tb_room on rsvrm_rm_id=rm_id
1892 where rsvrm_rsv_id=@rsvId ), '') as room, dbo.tb_klient.kd_tel,
1893 dbo.tb_klient.kd_mail
1894
1895FROM dbo.tb_klient INNER JOIN
1896 rsv.tb_reservation ON dbo.tb_klient.kd_id = rsv.tb_reservation.rsv_reservator
1897WHERE (rsv.tb_reservation.rsv_id = @rsvId)
1898--SELECT dbo.tb_klient.kd_name, dbo.tb_klient.kd_surname, ext.tbext_fidelio_reserv.extf_anreise, ext.tbext_fidelio_reserv.extf_abreise, ext.tbext_fidelio_reserv.extf_rsid, dbo.tb_klient.kd_town, dbo.tb_klient.kd_ul,
1899-- dbo.tb_klient.kd_tel, dbo.tb_klient.kd_mail, ext.tbext_fidelio_reserv.extf_roomnumber,dbo.tb_klient.kd_pass, dbo.tb_klient.kd_carreg
1900--FROM ext.tbext_fidelio_reserv INNER JOIN
1901-- dbo.tb_klient ON ext.tbext_fidelio_reserv.extf_kdid = dbo.tb_klient.kd_id
1902-- WHERE (ext.tbext_fidelio_reserv.extf_shares IN
1903-- (SELECT extf_shares
1904-- FROM ext.tbext_fidelio_reserv AS tbext_fidelio_reserv_1
1905-- WHERE (extf_rsid = @rsvId)))
1906
1907end
1908
1909
1910END
1911GO
1912PRINT N'Creating [rsv].[save_room_prices_for_period]...';
1913
1914
1915GO
1916--defsedit
1917--rsvrController
1918--DataManRsv
1919--reservation_single.js
1920--rels.cs
1921 --area htres
1922--DataManRsv
1923--simplectrlhelper
1924
1925
1926
1927--area htres
1928--DataManRsv
1929--simplectrlhelper
1930
1931-- =============================================
1932-- Author: Piotr
1933-- Create date: 2017-02-10
1934-- Description: generuje ceny typów pokoi na dany okres
1935-- =============================================
1936CREATE PROCEDURE [rsv].[save_room_prices_for_period]
1937 @start int,
1938 @end int,
1939 @pl_id int,
1940 @room_type int,
1941 @holiday int,
1942 @price money,
1943 @daytypes dbo.IntList readonly
1944AS
1945BEGIN
1946 SET NOCOUNT ON;
1947 if @holiday =1
1948 begin
1949 delete from rsv.tb_price_list_roomitem where rsv.tb_price_list_roomitem.ptd_pl_id=@pl_id and rsv.tb_price_list_roomitem.ptd_root_id =@room_type and rsv.tb_price_list_roomitem.ptd_dayserial in (select dayserial from dict.days where dayserial between @start and @end and ( (dict.days.dayofweek in (select item from @daytypes)) or dict.days.dayserial in (select hd_dayserial from dict.tb_holidays)))
1950 insert into rsv.tb_price_list_roomitem (ptd_pl_id, ptd_dayserial, ptd_root_id, ptd_nettoprice,ptd_st) select @pl_id,dayserial,@room_type,@price,1 from dict.days where dayserial between @start and @end and ( (dict.days.dayofweek in (select item from @daytypes)) or dict.days.dayserial in (select hd_dayserial from dict.tb_holidays))
1951 end
1952 else
1953 begin
1954 delete from rsv.tb_price_list_roomitem where rsv.tb_price_list_roomitem.ptd_pl_id=@pl_id and rsv.tb_price_list_roomitem.ptd_root_id =@room_type and rsv.tb_price_list_roomitem.ptd_dayserial in (select dayserial from dict.days where dayserial between @start and @end and (dict.days.dayofweek in (select item from @daytypes) and dict.days.dayserial not in (select hd_dayserial from dict.tb_holidays)))
1955 insert into rsv.tb_price_list_roomitem (ptd_pl_id, ptd_dayserial, ptd_root_id, ptd_nettoprice,ptd_st) select @pl_id,dayserial,@room_type,@price,1 from dict.days where dayserial between @start and @end and (dict.days.dayofweek in (select item from @daytypes) and dict.days.dayserial not in (select hd_dayserial from dict.tb_holidays))
1956 end
1957END
1958GO
1959PRINT N'Creating [rsv].[UpdateExpenses]...';
1960
1961
1962GO
1963
1964
1965
1966-- =============================================
1967-- Author: <gsue44>
1968-- Create date: <2017-10-03>
1969-- Description: <validacja rezerwacji spa - zmiana>
1970-- =============================================
1971CREATE PROCEDURE [spa].[reservation_change_save] @sparesID int, @DayID int, @iTime int, @iStationID int, @eid int , @cComment varchar(250)
1972AS
1973BEGIN
1974 SET NOCOUNT ON;
1975 declare @iwrktID int
1976
1977select @iwrktID=spa.tb_worktable.wrkt_id from spa.tb_worktable where wrkt_dayid = @DayID and wrkt_cabinetid =@iStationID
1978
1979
1980update spa.tb_spa_reservation set srsv_worktable_id=@iwrktID,
1981srsv_start=@iTime,
1982srsv_comm= @cComment where srsv_id=@sparesID
1983
1984
1985DECLARE @cd int=710
1986DECLARE @defkey varchar(80) ='spa_reservation_change'
1987DECLARE @usr int=@eid
1988DECLARE @otyp int=2
1989DECLARE @val int=@sparesID
1990
1991
1992EXECUTE [dbo].[sys_operc]
1993 @cd
1994 ,@defkey
1995 ,@usr
1996 ,@otyp
1997 ,@val
1998
1999
2000
2001END
2002GO
2003PRINT N'Creating [spa].[reservation_change_validate]...';
2004
2005
2006GO
2007
2008-- =============================================
2009-- Author: <gsue44>
2010-- Create date: <2017-10-03>
2011-- Description: <validacja rezerwacji spa - zmiana>
2012-- =============================================
2013CREATE PROCEDURE [spa].[reservation_change_validate] @sparesID int, @DayID int, @iTime int, @iStationID int, @eid int , @iTimeOverlap int out, @cErrInfo varchar(250) out
2014AS
2015BEGIN
2016 SET NOCOUNT ON;
2017 -- @xday int,
2018 declare @xAtomCount int, @xcabinetID int
2019 declare @iwrktID int
2020
2021 declare @cAtomTime varchar(10)='15' , @iAtomT int
2022
2023 select @cAtomTime= pr_defaultvalue from _def.tb_params where pr_name= 'spa_atom'
2024
2025 set @iAtomT= cast( Isnull(@cAtomTime,'15') as int)
2026
2027
2028 SELECT @xAtomCount= spa.tb_spa_reservation.srsv_atom_count
2029 FROM spa.tb_spa_reservation
2030WHERE (spa.tb_spa_reservation.srsv_id = @sparesID)
2031
2032
2033
2034 declare @st int , @nd int, @T int
2035
2036 set @st=@iTime
2037 set @nd=@st+(@xAtomCount*@iAtomT)
2038
2039
2040 select @iwrktID=spa.tb_worktable.wrkt_id from spa.tb_worktable where wrkt_dayid = @DayID and wrkt_cabinetid =@iStationID
2041
2042
2043declare @xst int, @xnd int
2044declare @iRefTime int
2045 declare @iErr int=0
2046
2047
2048DECLARE db_cursor CURSOR FOR
2049
2050 SELECT srsv_start, srsv_start + srsv_atom_count * @iAtomT AS srsv_end
2051 FROM spa.tb_spa_reservation
2052 WHERE (srsv_worktable_id = @iwrktID) and srsv_id <>@sparesID
2053
2054OPEN db_cursor
2055FETCH NEXT FROM db_cursor INTO @xst, @xnd
2056
2057WHILE @@FETCH_STATUS = 0 and @iErr<4
2058BEGIN
2059
2060
2061 select @iErr=count(0) from [temp].[__x]
2062where __x.a between @xst and @xnd
2063and __x.a in (
2064select xtmp.a from [temp].[__x] as xtmp
2065where xtmp.a between @st and @nd
2066)
2067
2068
2069 FETCH NEXT FROM db_cursor INTO @xst, @xnd
2070END
2071
2072CLOSE db_cursor
2073DEALLOCATE db_cursor
2074
2075
2076 set @iTimeOverlap=@iErr
2077 --print @iErr
2078
2079 set @cErrInfo='Znaleziono innÄ… rezerwacjÄ™ w podanym terminie i gabinecie!'
2080
2081
2082
2083
2084END
2085GO
2086PRINT N'Creating [spa].[reservation_info]...';
2087
2088
2089GO
2090-- =============================================
2091-- Author: <gsue44>
2092-- Create date: <2017-09-29>
2093-- Description: <podglÄ…d rezerwacji spa - zmiana>
2094-- =============================================
2095CREATE PROCEDURE [spa].[reservation_info] @paresID int, @eid int
2096
2097AS
2098BEGIN
2099 -- SET NOCOUNT ON added to prevent extra result sets from
2100 -- interfering with SELECT statements.
2101 SET NOCOUNT ON;
2102 SELECT spa.tb_spa_reservation.srsv_id, spa.tb_spa_reservation.srsv_klient_name + ' '+ dbo.tb_klient.kd_namefull as srsv_klient_info, spa.tb_worktable.wrkt_cabinetid, spa.tb_worktable.wrkt_dayid,
2103 spa.tb_spa_reservation.srsv_st, spa.tb_spa_reservation.srsv_atom_count, spa.tb_spa_reservation.srsv_start, spa.tb_spa_reservation.srsv_comm
2104FROM spa.tb_spa_reservation INNER JOIN
2105 spa.tb_worktable ON spa.tb_worktable.wrkt_id = spa.tb_spa_reservation.srsv_worktable_id LEFT OUTER JOIN
2106 dbo.tb_klient ON spa.tb_spa_reservation.srsv_klient_id = dbo.tb_klient.kd_id
2107WHERE (spa.tb_spa_reservation.srsv_id = @paresID)
2108
2109
2110END
2111GO
2112PRINT N'Creating [valid].[findCustomerCorp]...';
2113
2114
2115GO
2116
2117-- =============================================
2118-- Author: <gsue>
2119-- Create date: <12-09-2017>
2120-- Description: <zwraca id znalezionego klienta>
2121-- =============================================
2122CREATE PROCEDURE [valid].[findCustomerCorp]
2123@custId int,
2124@countryID int, @nip varchar(20) ,
2125 @eid int, @kdid int output
2126AS
2127BEGIN
2128 SET NOCOUNT ON;
2129/*
2130 SELECT @icount= COUNT(kd_id)
2131FROM dbo.tb_klient
2132WHERE (kd_tel = @tel)AND (kd_id <> @id) OR
2133 (kd_tel2 = @tel) AND (kd_id <> @id)
2134 */
2135 --@tel varchar(15),
2136 --declare @icount int, @icount2 int
2137 --SELECT @icount= count( tlid )
2138 -- FROM [dbo].[_sub_tels] where tl_no=@tel
2139
2140
2141select @kdid=kd_id from tb_klient where (kd_id <>@custId) and (kd_country in (0, @countryID)) and kd_nip like '%'+@nip+'%'
2142
2143
2144
2145
2146set @kdid=isnull(@kdid,0);
2147
2148-- if @kdid=0
2149
2150
2151
2152
2153END
2154GO
2155PRINT N'Creating [valid].[findCustomerInd]...';
2156
2157
2158GO
2159
2160
2161-- =============================================
2162-- Author: <gsue>
2163-- Create date: <12-09-2017>
2164-- Description: <zwraca id znalezionego klienta>
2165-- =============================================
2166CREATE PROCEDURE [valid].[findCustomerInd]
2167@custId int,
2168@countryID int, @nip varchar(20) ,
2169 @eid int, @kdid int output
2170AS
2171BEGIN
2172 SET NOCOUNT ON;
2173/*
2174 SELECT @icount= COUNT(kd_id)
2175FROM dbo.tb_klient
2176WHERE (kd_tel = @tel)AND (kd_id <> @id) OR
2177 (kd_tel2 = @tel) AND (kd_id <> @id)
2178 */
2179 --@tel varchar(15),
2180 --declare @icount int, @icount2 int
2181 --SELECT @icount= count( tlid )
2182 -- FROM [dbo].[_sub_tels] where tl_no=@tel
2183
2184
2185select @kdid=kd_id from tb_klient where (kd_id <>@custId) and (kd_country in (0, @countryID)) and kd_nip like '%'+@nip+'%'
2186
2187
2188
2189
2190set @kdid=isnull(@kdid,0);
2191
2192-- if @kdid=0
2193
2194
2195
2196
2197END
2198GO
2199
2200
2201GO
2202PRINT N'Update complete.';
2203
2204
2205GO