· 8 years ago · Mar 09, 2018, 07:28 PM
1USE [PadpcPostTest]
2GO
3
4/****** Object: Table [dbo].[ETraining_Congres] Script Date: 11/04/2011 14:24:04 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11SET ANSI_PADDING ON
12GO
13
14CREATE TABLE [dbo].[ETraining_Congres](
15 [ETrainingCongresId] [int] IDENTITY(1,1) NOT NULL,
16 [Label] [varchar](250) NOT NULL,
17 CONSTRAINT [PK_ETraining_Congres] PRIMARY KEY CLUSTERED
18(
19 [ETrainingCongresId] ASC
20)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21) ON [PRIMARY]
22
23GO
24
25SET ANSI_PADDING OFF
26GO
27
28ALTER TABLE [dbo].[ETraining_Congres] ADD CONSTRAINT [DF_ETraining_Congres_Label] DEFAULT ('') FOR [Label]
29GO
30
31
32
33
34USE [PadpcPostTest]
35GO
36
37IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Discount_DiscountRate]') AND type = 'D')
38BEGIN
39ALTER TABLE [dbo].[ETraining_Discount] DROP CONSTRAINT [DF__Discount_DiscountRate]
40END
41
42GO
43
44IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__Discount_DiscountText]') AND type = 'D')
45BEGIN
46ALTER TABLE [dbo].[ETraining_Discount] DROP CONSTRAINT [DF__Discount_DiscountText]
47END
48
49GO
50
51USE [PadpcPostTest]
52GO
53
54/****** Object: Table [dbo].[ETraining_Discount] Script Date: 11/04/2011 14:23:42 ******/
55IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Discount]') AND type in (N'U'))
56DROP TABLE [dbo].[ETraining_Discount]
57GO
58
59
60USE [PadpcPostTest]
61GO
62
63IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__DiscountPeriod_DiscountText]') AND type = 'D')
64BEGIN
65ALTER TABLE [dbo].[ETraining_DiscountPeriod] DROP CONSTRAINT [DF__DiscountPeriod_DiscountText]
66END
67
68GO
69
70USE [PadpcPostTest]
71GO
72
73/****** Object: Table [dbo].[ETraining_DiscountPeriod] Script Date: 11/04/2011 14:23:48 ******/
74IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_DiscountPeriod]') AND type in (N'U'))
75DROP TABLE [dbo].[ETraining_DiscountPeriod]
76GO
77
78
79
80USE [PadpcPostTest]
81GO
82
83IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_EFormation_TVQ]') AND type = 'D')
84BEGIN
85ALTER TABLE [dbo].[ETraining_ETraining] DROP CONSTRAINT [DF_EForm_EFormation_TVQ]
86END
87
88GO
89
90
91USE [PadpcPostTest]
92GO
93
94IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_EFormation_TPS]') AND type = 'D')
95BEGIN
96ALTER TABLE [dbo].[ETraining_ETraining] DROP CONSTRAINT [DF_EForm_EFormation_TPS]
97END
98
99GO
100
101
102USE [PadpcPostTest]
103GO
104
105IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_EFormation_Total]') AND type = 'D')
106BEGIN
107ALTER TABLE [dbo].[ETraining_ETraining] DROP CONSTRAINT [DF_EForm_EFormation_Total]
108END
109
110GO
111
112
113USE [PadpcPostTest]
114GO
115
116IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_EFormation_Prix]') AND type = 'D')
117BEGIN
118ALTER TABLE [dbo].[ETraining_ETraining] DROP CONSTRAINT [DF_EForm_EFormation_Prix]
119END
120
121GO
122
123
124USE [PadpcPostTest]
125GO
126
127IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_EFormation_NombreCredits]') AND type = 'D')
128BEGIN
129ALTER TABLE [dbo].[ETraining_ETraining] DROP CONSTRAINT [DF_EForm_EFormation_NombreCredits]
130END
131
132GO
133
134
135
136
137USE [PadpcPostTest]
138GO
139
140ALTER TABLE ETraining_ETraining
141DROP COLUMN Price, TPS, TVQ, Total, PriceToken
142GO
143
144
145ALTER TABLE ETraining_Etraining
146ALTER COLUMN NumberCredits INT NOT NULL
147GO
148
149USE [PadpcPostTest]
150GO
151
152ALTER TABLE [dbo].[ETraining_ETraining] ADD CONSTRAINT [DF_EForm_EFormation_NombreCredits] DEFAULT ((0)) FOR [NumberCredits]
153GO
154
155
156USE [PadpcPostTest]
157GO
158
159/****** Object: Table [dbo].[ETraining_ETrainingCongres] Script Date: 11/04/2011 14:27:16 ******/
160SET ANSI_NULLS ON
161GO
162
163SET QUOTED_IDENTIFIER ON
164GO
165
166CREATE TABLE [dbo].[ETraining_ETrainingCongres](
167 [ETrainingId] [int] NOT NULL,
168 [CongresId] [int] NOT NULL
169) ON [PRIMARY]
170
171GO
172
173USE [PadpcPostTest]
174GO
175
176/****** Object: Table [dbo].[ETraining_ETrainingTag] Script Date: 11/04/2011 14:27:40 ******/
177SET ANSI_NULLS ON
178GO
179
180SET QUOTED_IDENTIFIER ON
181GO
182
183CREATE TABLE [dbo].[ETraining_ETrainingTag](
184 [TagId] [int] NOT NULL,
185 [ETrainingId] [int] NOT NULL
186) ON [PRIMARY]
187
188GO
189
190USE [PadpcPostTest]
191GO
192
193IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_Conferencier_Nom]') AND type = 'D')
194BEGIN
195ALTER TABLE [dbo].[ETraining_Lecturer] DROP CONSTRAINT [DF_EForm_Conferencier_Nom]
196END
197
198GO
199
200IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_Conferencier_Prenom]') AND type = 'D')
201BEGIN
202ALTER TABLE [dbo].[ETraining_Lecturer] DROP CONSTRAINT [DF_EForm_Conferencier_Prenom]
203END
204
205GO
206
207IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EForm_Conferencier_Description]') AND type = 'D')
208BEGIN
209ALTER TABLE [dbo].[ETraining_Lecturer] DROP CONSTRAINT [DF_EForm_Conferencier_Description]
210END
211
212GO
213
214USE [PadpcPostTest]
215GO
216
217/****** Object: Table [dbo].[ETraining_Lecturer] Script Date: 11/04/2011 14:27:57 ******/
218IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Lecturer]') AND type in (N'U'))
219DROP TABLE [dbo].[ETraining_Lecturer]
220GO
221
222
223USE [PadpcPostTest]
224GO
225
226/****** Object: Table [dbo].[ETraining_LecturerProduct] Script Date: 11/04/2011 14:28:20 ******/
227IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_LecturerProduct]') AND type in (N'U'))
228DROP TABLE [dbo].[ETraining_LecturerProduct]
229GO
230
231
232USE [PadpcPostTest]
233GO
234
235/****** Object: Table [dbo].[ETraining_PackageRate] Script Date: 11/04/2011 14:29:23 ******/
236SET ANSI_NULLS ON
237GO
238
239SET QUOTED_IDENTIFIER ON
240GO
241
242CREATE TABLE [dbo].[ETraining_PackageRate](
243 [PackageRateId] [int] IDENTITY(1,1) NOT NULL,
244 [PersonneTypeId] [int] NOT NULL,
245 [NumberCredits] [int] NOT NULL,
246 [Price] [decimal](10, 2) NOT NULL,
247 [TPS] [decimal](10, 2) NOT NULL,
248 [TVQ] [decimal](10, 2) NOT NULL,
249 [Total] [decimal](10, 2) NOT NULL,
250 CONSTRAINT [PK_ETraining_PackageRate] PRIMARY KEY CLUSTERED
251(
252 [PackageRateId] ASC
253)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
254) ON [PRIMARY]
255
256GO
257
258
259USE [PadpcPostTest]
260GO
261
262/****** Object: Table [dbo].[ETraining_PersonneCredits] Script Date: 11/04/2011 14:29:43 ******/
263SET ANSI_NULLS ON
264GO
265
266SET QUOTED_IDENTIFIER ON
267GO
268
269CREATE TABLE [dbo].[ETraining_PersonneCredits](
270 [PersonneCreditId] [int] IDENTITY(1,1) NOT NULL,
271 [PersonneId] [int] NOT NULL,
272 [PurchaseDate] [datetime] NOT NULL,
273 [PaidFor] [bit] NOT NULL,
274 [Royalties] [bit] NOT NULL,
275 [UseDate] [datetime] NULL,
276 [UsedForETrainingId] [int] NULL,
277 CONSTRAINT [PK_ETraining_PersonneCredits] PRIMARY KEY CLUSTERED
278(
279 [PersonneCreditId] ASC
280)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
281) ON [PRIMARY]
282
283GO
284
285
286USE [PadpcPostTest]
287GO
288
289/****** Object: Table [dbo].[ETraining_PersonneETraining] Script Date: 11/04/2011 14:29:58 ******/
290SET ANSI_NULLS ON
291GO
292
293SET QUOTED_IDENTIFIER ON
294GO
295
296CREATE TABLE [dbo].[ETraining_PersonneETraining](
297 [PersonneId] [int] NOT NULL,
298 [ETrainingId] [int] NOT NULL,
299 [CreditsGranted] [bit] NOT NULL,
300 [PurchaseDate] [datetime] NOT NULL,
301 CONSTRAINT [PK_ETraining_PersonneETraining] PRIMARY KEY CLUSTERED
302(
303 [PersonneId] ASC,
304 [ETrainingId] ASC
305)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
306) ON [PRIMARY]
307
308GO
309
310
311USE [PadpcPostTest]
312GO
313
314/****** Object: Table [dbo].[ETraining_Rate] Script Date: 11/04/2011 14:30:57 ******/
315IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Rate]') AND type in (N'U'))
316DROP TABLE [dbo].[ETraining_Rate]
317GO
318
319USE [PadpcPostTest]
320GO
321
322/****** Object: Table [dbo].[ETraining_Rate] Script Date: 11/04/2011 14:30:57 ******/
323SET ANSI_NULLS ON
324GO
325
326SET QUOTED_IDENTIFIER ON
327GO
328
329CREATE TABLE [dbo].[ETraining_Rate](
330 [ETrainingRateId] [int] IDENTITY(1,1) NOT NULL,
331 [PersonneTypeID] [int] NOT NULL,
332 [Price] [decimal](10, 2) NOT NULL,
333 [TPS] [decimal](10, 2) NOT NULL,
334 [TVQ] [decimal](10, 2) NOT NULL,
335 [Total] [decimal](10, 2) NOT NULL,
336 CONSTRAINT [PK_ETraining_Rate] PRIMARY KEY CLUSTERED
337(
338 [ETrainingRateId] ASC
339)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
340) ON [PRIMARY]
341
342GO
343
344
345USE [PadpcPostTest]
346GO
347
348/****** Object: Table [dbo].[ETraining_Tag] Script Date: 11/04/2011 14:31:12 ******/
349SET ANSI_NULLS ON
350GO
351
352SET QUOTED_IDENTIFIER ON
353GO
354
355SET ANSI_PADDING ON
356GO
357
358CREATE TABLE [dbo].[ETraining_Tag](
359 [TagId] [int] IDENTITY(1,1) NOT NULL,
360 [Label] [varchar](100) NOT NULL,
361 CONSTRAINT [PK_ETraining_Tag] PRIMARY KEY CLUSTERED
362(
363 [TagId] ASC
364)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
365) ON [PRIMARY]
366
367GO
368
369SET ANSI_PADDING OFF
370GO
371
372ALTER TABLE [dbo].[ETraining_Tag] ADD CONSTRAINT [DF_ETraining_Tag_Label] DEFAULT ('') FOR [Label]
373GO
374
375
376USE [PadpcPostTest]
377GO
378
379ALTER TABLE ETraining_Transaction
380
381DROP COLUMN TokenID
382GO
383
384ALTER TABLE ETraining_Transaction
385ADD PersonneID int NULL,
386 TPS decimal(10,2) NOT NULL DEFAULT(0),
387 TVQ decimal(10,2) NOT NULL DEFAULT(0),
388 Total decimal(10,2) NOT NULL DEFAULT(0)
389
390
391GO
392
393
394
395USE [PadpcPostTest]
396GO
397
398/****** Object: StoredProcedure [dbo].[ETraining_Congres_Delete] Script Date: 11/04/2011 14:43:36 ******/
399IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Congres_Delete]') AND type in (N'P', N'PC'))
400DROP PROCEDURE [dbo].[ETraining_Congres_Delete]
401GO
402
403USE [PadpcPostTest]
404GO
405
406/****** Object: StoredProcedure [dbo].[ETraining_Congres_Delete] Script Date: 11/04/2011 14:43:36 ******/
407SET ANSI_NULLS ON
408GO
409
410SET QUOTED_IDENTIFIER ON
411GO
412
413
414CREATE PROCEDURE [dbo].[ETraining_Congres_Delete]
415 @ETrainingCongresId int
416AS
417BEGIN
418
419 SET NOCOUNT ON;
420
421 DELETE FROM ETraining_Congres
422 WHERE ETrainingCongresId = @ETrainingCongresId
423END
424
425GO
426
427
428USE [PadpcPostTest]
429GO
430
431/****** Object: StoredProcedure [dbo].[ETraining_Congres_Insert] Script Date: 11/04/2011 14:43:48 ******/
432IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Congres_Insert]') AND type in (N'P', N'PC'))
433DROP PROCEDURE [dbo].[ETraining_Congres_Insert]
434GO
435
436USE [PadpcPostTest]
437GO
438
439/****** Object: StoredProcedure [dbo].[ETraining_Congres_Insert] Script Date: 11/04/2011 14:43:48 ******/
440SET ANSI_NULLS ON
441GO
442
443SET QUOTED_IDENTIFIER ON
444GO
445
446
447
448CREATE PROCEDURE [dbo].[ETraining_Congres_Insert]
449 @Label varchar(250)
450AS
451BEGIN
452
453 SET NOCOUNT ON;
454
455 INSERT INTO ETraining_Congres (Label)
456 VALUES (@Label)
457 RETURN SCOPE_IDENTITY()
458END
459
460
461GO
462
463
464USE [PadpcPostTest]
465GO
466
467/****** Object: StoredProcedure [dbo].[ETraining_ETraining_Upsert] Script Date: 11/04/2011 14:44:18 ******/
468IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_ETraining_Upsert]') AND type in (N'P', N'PC'))
469DROP PROCEDURE [dbo].[ETraining_ETraining_Upsert]
470GO
471
472USE [PadpcPostTest]
473GO
474
475/****** Object: StoredProcedure [dbo].[ETraining_ETraining_Upsert] Script Date: 11/04/2011 14:44:18 ******/
476SET ANSI_NULLS ON
477GO
478
479SET QUOTED_IDENTIFIER ON
480GO
481
482CREATE PROCEDURE [dbo].[ETraining_ETraining_Upsert]
483 @ETrainingId int,
484 @CourseNumber varchar(7),
485 @Title varchar(250),
486 @Summary varchar(max),
487 @Description varchar(max),
488 @PublicationDate datetime,
489 @ExpiryDate datetime,
490 @PurchaseDeadline datetime,
491 @Image varchar(250),
492 @NumberCredits int,
493 @IsDiscountable bit,
494 @IsOnline bit,
495 @AptimedCourseId varchar(10),
496 @AptimedGroupdId varchar(10),
497 @ProductId int output,
498 @ActivityId int output
499AS
500BEGIN
501 SET NOCOUNT ON;
502
503 IF ((@ETrainingId is null) OR (@ETrainingId = 0))
504 BEGIN
505 INSERT INTO ETraining_ETraining (
506 CourseNumber,
507 Title,
508 Summary,
509 [Description],
510 PublicationDate,
511 ExpiryDate,
512 PurchaseDeadline,
513 [Image],
514 NumberCredits,
515 IsDiscountable,
516 IsOnline,
517 AptimedCourseId,
518 AptimedGroupId)
519 VALUES (
520 @CourseNumber,
521 @Title,
522 @Summary,
523 @Description,
524 @PublicationDate,
525 @ExpiryDate,
526 @PurchaseDeadline,
527 @Image,
528 @NumberCredits,
529 @IsDiscountable,
530 @IsOnline,
531 @AptimedCourseId,
532 @AptimedGroupdId)
533
534 set @ActivityId = @@IDENTITY
535
536 INSERT INTO ETraining_Product (ProductTypeID, ActivityID)
537 VALUES ('1', @ActivityId)
538
539 set @ProductId = @@IDENTITY
540
541 END
542 ELSE
543 BEGIN
544 set @ActivityId = @ETrainingId
545
546 UPDATE ETraining_ETraining
547 SET CourseNumber = @CourseNumber,
548 Title = @Title,
549 Summary = @Summary,
550 [Description] = @Description,
551 PublicationDate = @PublicationDate,
552 ExpiryDate = @ExpiryDate,
553 PurchaseDeadline = @PurchaseDeadline,
554 Image = @Image,
555 NumberCredits = @NumberCredits,
556 IsDiscountable = @IsDiscountable,
557 IsOnline = @IsOnline,
558 AptimedCourseId = @AptimedCourseId,
559 AptimedGroupId = @AptimedGroupdId
560 WHERE
561 ETrainingID = @ETrainingId
562
563 SELECT @ProductId = ProductId
564 FROM ETraining_Product
565 WHERE ProductTypeID = '1' and ActivityID = @ETrainingId
566 END
567
568 RETURN SCOPE_IDENTITY()
569END
570
571GO
572
573
574USE [PadpcPostTest]
575GO
576
577/****** Object: StoredProcedure [dbo].[ETraining_ETrainingCongres_Delete] Script Date: 11/04/2011 14:44:32 ******/
578IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_ETrainingCongres_Delete]') AND type in (N'P', N'PC'))
579DROP PROCEDURE [dbo].[ETraining_ETrainingCongres_Delete]
580GO
581
582USE [PadpcPostTest]
583GO
584
585/****** Object: StoredProcedure [dbo].[ETraining_ETrainingCongres_Delete] Script Date: 11/04/2011 14:44:32 ******/
586SET ANSI_NULLS ON
587GO
588
589SET QUOTED_IDENTIFIER ON
590GO
591
592
593CREATE PROCEDURE [dbo].[ETraining_ETrainingCongres_Delete]
594 @ETrainingId int,
595 @CongresId int
596AS
597BEGIN
598
599 SET NOCOUNT ON;
600
601 DELETE FROM ETraining_ETrainingCongres
602 WHERE ETrainingId = @ETrainingId
603 AND CongresId = @CongresId
604END
605
606GO
607
608
609USE [PadpcPostTest]
610GO
611
612/****** Object: StoredProcedure [dbo].[ETraining_ETrainingCongres_Insert] Script Date: 11/04/2011 14:44:40 ******/
613IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_ETrainingCongres_Insert]') AND type in (N'P', N'PC'))
614DROP PROCEDURE [dbo].[ETraining_ETrainingCongres_Insert]
615GO
616
617USE [PadpcPostTest]
618GO
619
620/****** Object: StoredProcedure [dbo].[ETraining_ETrainingCongres_Insert] Script Date: 11/04/2011 14:44:40 ******/
621SET ANSI_NULLS ON
622GO
623
624SET QUOTED_IDENTIFIER ON
625GO
626
627
628CREATE PROCEDURE [dbo].[ETraining_ETrainingCongres_Insert]
629 @ETrainingId int,
630 @CongresId int
631AS
632BEGIN
633
634 SET NOCOUNT ON;
635
636 INSERT INTO ETraining_ETrainingCongres (ETrainingId,
637 CongresId)
638 VALUES (@ETrainingId,
639 @CongresId)
640END
641
642GO
643
644
645USE [PadpcPostTest]
646GO
647
648/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Delete] Script Date: 11/04/2011 14:45:02 ******/
649IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PackageRate_Delete]') AND type in (N'P', N'PC'))
650DROP PROCEDURE [dbo].[ETraining_PackageRate_Delete]
651GO
652
653USE [PadpcPostTest]
654GO
655
656/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Delete] Script Date: 11/04/2011 14:45:02 ******/
657SET ANSI_NULLS ON
658GO
659
660SET QUOTED_IDENTIFIER ON
661GO
662
663
664CREATE PROCEDURE [dbo].[ETraining_PackageRate_Delete]
665 @PackageRateId int
666AS
667BEGIN
668
669 SET NOCOUNT ON;
670
671 DELETE FROM ETraining_PackageRate
672 WHERE PackageRateId = @PackageRateId
673END
674
675GO
676
677
678USE [PadpcPostTest]
679GO
680
681/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Insert] Script Date: 11/04/2011 14:45:12 ******/
682IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PackageRate_Insert]') AND type in (N'P', N'PC'))
683DROP PROCEDURE [dbo].[ETraining_PackageRate_Insert]
684GO
685
686USE [PadpcPostTest]
687GO
688
689/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Insert] Script Date: 11/04/2011 14:45:12 ******/
690SET ANSI_NULLS ON
691GO
692
693SET QUOTED_IDENTIFIER ON
694GO
695
696
697CREATE PROCEDURE [dbo].[ETraining_PackageRate_Insert]
698 @PersonneTypeId int,
699 @NumberCredits int,
700 @Price decimal(10,2),
701 @TPS decimal(10,2),
702 @TVQ decimal(10,2),
703 @Total decimal(10,2)
704AS
705BEGIN
706
707 SET NOCOUNT ON;
708
709 INSERT INTO ETraining_PackageRate(PersonneTypeId,
710 NumberCredits,
711 Price,
712 TPS,
713 TVQ,
714 Total)
715 VALUES (@PersonneTypeId,
716 @NumberCredits,
717 @Price,
718 @TPS,
719 @TVQ,
720 @Total)
721
722 RETURN SCOPE_IDENTITY()
723END
724
725GO
726
727
728USE [PadpcPostTest]
729GO
730
731/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Update] Script Date: 11/04/2011 14:45:20 ******/
732IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PackageRate_Update]') AND type in (N'P', N'PC'))
733DROP PROCEDURE [dbo].[ETraining_PackageRate_Update]
734GO
735
736USE [PadpcPostTest]
737GO
738
739/****** Object: StoredProcedure [dbo].[ETraining_PackageRate_Update] Script Date: 11/04/2011 14:45:20 ******/
740SET ANSI_NULLS ON
741GO
742
743SET QUOTED_IDENTIFIER ON
744GO
745
746
747CREATE PROCEDURE [dbo].[ETraining_PackageRate_Update]
748 @PackageRateId int,
749 @PersonneTypeId int,
750 @NumberCredits int,
751 @Price decimal(10,2),
752 @TPS decimal(10,2),
753 @TVQ decimal(10,2),
754 @Total decimal(10,2)
755AS
756BEGIN
757
758 SET NOCOUNT ON;
759
760 UPDATE ETraining_PackageRate
761 SET PersonneTypeId = @PersonneTypeId,
762 NumberCredits = @NumberCredits,
763 Price = @Price,
764 TPS = @TPS,
765 TVQ = @TVQ,
766 Total = @Total
767 WHERE PackageRateId = @PackageRateId
768END
769
770GO
771
772
773USE [PadpcPostTest]
774GO
775
776/****** Object: StoredProcedure [dbo].[ETraining_PersonneCredit_Insert] Script Date: 11/04/2011 14:45:35 ******/
777IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PersonneCredit_Insert]') AND type in (N'P', N'PC'))
778DROP PROCEDURE [dbo].[ETraining_PersonneCredit_Insert]
779GO
780
781USE [PadpcPostTest]
782GO
783
784/****** Object: StoredProcedure [dbo].[ETraining_PersonneCredit_Insert] Script Date: 11/04/2011 14:45:35 ******/
785SET ANSI_NULLS ON
786GO
787
788SET QUOTED_IDENTIFIER ON
789GO
790
791
792CREATE PROCEDURE [dbo].[ETraining_PersonneCredit_Insert]
793 @PersonneId int,
794 @PurchaseDate datetime,
795 @PaidFor bit,
796 @Royalties bit,
797 @UseDate datetime,
798 @UsedForETrainingId int
799AS
800BEGIN
801
802 SET NOCOUNT ON;
803
804 INSERT INTO ETraining_PersonneCredits(PersonneId,
805 PurchaseDate,
806 PaidFor,
807 Royalties,
808 UseDate,
809 UsedForETrainingId)
810 VALUES (@PersonneId,
811 @PurchaseDate,
812 @PaidFor,
813 @Royalties,
814 @UseDate,
815 @UsedForETrainingId)
816
817 RETURN SCOPE_IDENTITY()
818END
819
820GO
821
822
823USE [PadpcPostTest]
824GO
825
826/****** Object: StoredProcedure [dbo].[ETraining_PersonneCredit_UseForActivity] Script Date: 11/04/2011 14:45:43 ******/
827IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PersonneCredit_UseForActivity]') AND type in (N'P', N'PC'))
828DROP PROCEDURE [dbo].[ETraining_PersonneCredit_UseForActivity]
829GO
830
831USE [PadpcPostTest]
832GO
833
834/****** Object: StoredProcedure [dbo].[ETraining_PersonneCredit_UseForActivity] Script Date: 11/04/2011 14:45:43 ******/
835SET ANSI_NULLS ON
836GO
837
838SET QUOTED_IDENTIFIER ON
839GO
840
841
842
843CREATE PROCEDURE [dbo].[ETraining_PersonneCredit_UseForActivity]
844 @PersonneCreditId int,
845 @UseDate datetime,
846 @UsedForETrainingId int
847AS
848BEGIN
849 SET NOCOUNT ON;
850
851 UPDATE ETraining_PersonneCredits
852 SET UseDate = @UseDate,
853 UsedForETrainingId = @UsedForETrainingId
854 WHERE PersonneCreditId = @PersonneCreditId
855END
856
857
858GO
859
860
861USE [PadpcPostTest]
862GO
863
864/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_Delete] Script Date: 11/04/2011 14:45:55 ******/
865IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PersonneETraining_Delete]') AND type in (N'P', N'PC'))
866DROP PROCEDURE [dbo].[ETraining_PersonneETraining_Delete]
867GO
868
869USE [PadpcPostTest]
870GO
871
872/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_Delete] Script Date: 11/04/2011 14:45:55 ******/
873SET ANSI_NULLS ON
874GO
875
876SET QUOTED_IDENTIFIER ON
877GO
878
879
880CREATE PROCEDURE [dbo].[ETraining_PersonneETraining_Delete]
881 @PersonneId int,
882 @ETrainingId int
883AS
884BEGIN
885
886 SET NOCOUNT ON;
887
888 DELETE FROM ETraining_PersonneETraining
889 WHERE PersonneId = @PersonneId
890 AND ETrainingId = @ETrainingId
891END
892
893GO
894
895
896USE [PadpcPostTest]
897GO
898
899/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_GrantCredits] Script Date: 11/04/2011 14:46:03 ******/
900IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PersonneETraining_GrantCredits]') AND type in (N'P', N'PC'))
901DROP PROCEDURE [dbo].[ETraining_PersonneETraining_GrantCredits]
902GO
903
904USE [PadpcPostTest]
905GO
906
907/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_GrantCredits] Script Date: 11/04/2011 14:46:03 ******/
908SET ANSI_NULLS ON
909GO
910
911SET QUOTED_IDENTIFIER ON
912GO
913
914
915CREATE PROCEDURE [dbo].[ETraining_PersonneETraining_GrantCredits]
916 @PersonneId int,
917 @ETrainingId int,
918 @CreditsGranted bit
919AS
920BEGIN
921
922 SET NOCOUNT ON;
923
924 UPDATE ETraining_PersonneETraining
925 SET CreditsGranted = @CreditsGranted
926 WHERE PersonneId = @PersonneId
927 AND ETrainingId = @ETrainingId
928END
929
930GO
931
932
933USE [PadpcPostTest]
934GO
935
936/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_Insert] Script Date: 11/04/2011 14:46:19 ******/
937IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_PersonneETraining_Insert]') AND type in (N'P', N'PC'))
938DROP PROCEDURE [dbo].[ETraining_PersonneETraining_Insert]
939GO
940
941USE [PadpcPostTest]
942GO
943
944/****** Object: StoredProcedure [dbo].[ETraining_PersonneETraining_Insert] Script Date: 11/04/2011 14:46:19 ******/
945SET ANSI_NULLS ON
946GO
947
948SET QUOTED_IDENTIFIER ON
949GO
950
951
952CREATE PROCEDURE [dbo].[ETraining_PersonneETraining_Insert]
953 @PersonneId int,
954 @ETrainingId int,
955 @CreditsGranted bit,
956 @PurchaseDate datetime
957AS
958BEGIN
959
960 SET NOCOUNT ON;
961
962 INSERT INTO ETraining_PersonneETraining (PersonneId,
963 ETrainingId,
964 CreditsGranted,
965 PurchaseDate)
966 VALUES (@PersonneId,
967 @ETrainingId,
968 @CreditsGranted,
969 @PurchaseDate)
970END
971
972GO
973
974
975USE [PadpcPostTest]
976GO
977
978/****** Object: StoredProcedure [dbo].[ETraining_Rate_Delete] Script Date: 11/04/2011 14:46:26 ******/
979IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Rate_Delete]') AND type in (N'P', N'PC'))
980DROP PROCEDURE [dbo].[ETraining_Rate_Delete]
981GO
982
983USE [PadpcPostTest]
984GO
985
986/****** Object: StoredProcedure [dbo].[ETraining_Rate_Delete] Script Date: 11/04/2011 14:46:26 ******/
987SET ANSI_NULLS ON
988GO
989
990SET QUOTED_IDENTIFIER ON
991GO
992
993
994
995CREATE PROCEDURE [dbo].[ETraining_Rate_Delete]
996 @ETrainingRateId int
997AS
998BEGIN
999
1000 SET NOCOUNT ON;
1001
1002 DELETE FROM ETraining_Rate
1003 WHERE ETrainingRateID = @ETrainingRateId
1004END
1005
1006
1007GO
1008
1009
1010USE [PadpcPostTest]
1011GO
1012
1013/****** Object: StoredProcedure [dbo].[ETraining_Rate_Insert] Script Date: 11/04/2011 14:46:33 ******/
1014IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Rate_Insert]') AND type in (N'P', N'PC'))
1015DROP PROCEDURE [dbo].[ETraining_Rate_Insert]
1016GO
1017
1018USE [PadpcPostTest]
1019GO
1020
1021/****** Object: StoredProcedure [dbo].[ETraining_Rate_Insert] Script Date: 11/04/2011 14:46:33 ******/
1022SET ANSI_NULLS ON
1023GO
1024
1025SET QUOTED_IDENTIFIER ON
1026GO
1027
1028
1029
1030CREATE PROCEDURE [dbo].[ETraining_Rate_Insert]
1031 @PersonneTypeID int,
1032 @Price decimal(10,2),
1033 @TPS decimal(10,2),
1034 @TVQ decimal(10,2),
1035 @Total decimal(10,2)
1036AS
1037BEGIN
1038
1039 SET NOCOUNT ON;
1040
1041 INSERT INTO ETraining_Rate (PersonneTypeID,
1042 Price,
1043 TPS,
1044 TVQ,
1045 Total)
1046 VALUES (@PersonneTypeID,
1047 @Price,
1048 @TPS,
1049 @TVQ,
1050 @Total)
1051
1052 RETURN SCOPE_IDENTITY()
1053END
1054
1055
1056GO
1057
1058
1059USE [PadpcPostTest]
1060GO
1061
1062/****** Object: StoredProcedure [dbo].[ETraining_Rate_Update] Script Date: 11/04/2011 14:46:40 ******/
1063IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Rate_Update]') AND type in (N'P', N'PC'))
1064DROP PROCEDURE [dbo].[ETraining_Rate_Update]
1065GO
1066
1067USE [PadpcPostTest]
1068GO
1069
1070/****** Object: StoredProcedure [dbo].[ETraining_Rate_Update] Script Date: 11/04/2011 14:46:40 ******/
1071SET ANSI_NULLS ON
1072GO
1073
1074SET QUOTED_IDENTIFIER ON
1075GO
1076
1077
1078
1079CREATE PROCEDURE [dbo].[ETraining_Rate_Update]
1080 @ETrainingRateID int,
1081 @PersonneTypeID int,
1082 @Price decimal(10,2),
1083 @TPS decimal(10,2),
1084 @TVQ decimal(10,2),
1085 @Total decimal(10,2)
1086AS
1087BEGIN
1088
1089 SET NOCOUNT ON;
1090
1091 UPDATE ETraining_Rate
1092 SET PersonneTypeID = @PersonneTypeID,
1093 Price = @Price,
1094 TPS = @TPS,
1095 TVQ = @TVQ,
1096 Total = @Total
1097 WHERE
1098 ETrainingRateId = @EtrainingRateId
1099END
1100
1101
1102GO
1103
1104
1105USE [PadpcPostTest]
1106GO
1107
1108/****** Object: StoredProcedure [dbo].[ETraining_Transaction_Insert] Script Date: 11/04/2011 14:46:58 ******/
1109IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ETraining_Transaction_Insert]') AND type in (N'P', N'PC'))
1110DROP PROCEDURE [dbo].[ETraining_Transaction_Insert]
1111GO
1112
1113USE [PadpcPostTest]
1114GO
1115
1116/****** Object: StoredProcedure [dbo].[ETraining_Transaction_Insert] Script Date: 11/04/2011 14:46:58 ******/
1117SET ANSI_NULLS ON
1118GO
1119
1120SET QUOTED_IDENTIFIER ON
1121GO
1122
1123CREATE PROCEDURE [dbo].[ETraining_Transaction_Insert]
1124 @PersonneId int,
1125 @TransactionDate datetime,
1126 @Amount decimal(10,2),
1127 @TPS decimal(10,2),
1128 @TVQ decimal(10,2),
1129 @Total decimal(10,2),
1130 @OrderId varchar(75),
1131 @MonerisReference varchar(75),
1132 @Comment varchar(1000),
1133 @MonerisTxn varchar(20),
1134 @ProcessingDate datetime,
1135 @CardType varchar(20)
1136AS
1137BEGIN
1138 INSERT INTO ETraining_Transaction(PersonneID,
1139 TransactionDate,
1140 Amount,
1141 TPS,
1142 TVQ,
1143 Total,
1144 OrderId,
1145 MonerisReference,
1146 Comment,
1147 MonerisTxn,
1148 ProcessingDate,
1149 CardType)
1150 VALUES(@PersonneId,
1151 @TransactionDate,
1152 @Amount,
1153 @TPS,
1154 @TVQ,
1155 @Total,
1156 @OrderId,
1157 @MonerisReference,
1158 @Comment,
1159 @MonerisTxn,
1160 @ProcessingDate,
1161 @CardType)
1162END
1163
1164GO