usp_GetLicenseById.sql
3.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetLicenseById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetLicenseById]
GO
-- ====================================================
-- Author: Magic Software
-- Create date: 29-Jan-2018
-- Description: To get a license information on the basis of LicenseId
-- ====================================================
create PROCEDURE [dbo].[usp_GetLicenseById]
-- Add the parameters for the stored procedure here
@Id int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @SubscriptionStartDate datetime, @SubscriptionEndDate datetime, @NoOfImages int, @TotalAmount money, @RenewalDate datetime,
@SiteIp varchar(100), @SiteIpTo varchar(100), @SiteMasterIPTo varchar(100);
declare @UserId int, @Login varchar(100), @Password varchar(100), @SecurityQuestionId int, @Answer varchar(100);
declare @EditionId int, @TotalLogins int;
declare @EditionLoginText varchar(1000);
declare CurEditionLogin cursor for
select EditionId, TotalLogins from LicenseToEdition where LicenseId = @Id;
set @EditionLoginText = '';
open CurEditionLogin
fetch NEXT FROM CurEditionLogin into @EditionId, @TotalLogins
while @@FETCH_STATUS = 0
begin
set @EditionLoginText = @EditionLoginText + convert(varchar(5), @EditionId) + '-' + convert(varchar(5), @TotalLogins) + '|';
fetch NEXT FROM CurEditionLogin into @EditionId, @TotalLogins
end
CLOSE CurEditionLogin
DEALLOCATE CurEditionLogin
set @EditionLoginText = SUBSTRING(@EditionLoginText, 1, len(@EditionLoginText) - 1);
select top 1
@SubscriptionStartDate = LSD.SubscriptionValidFrom,
@SubscriptionEndDate = LSD.SubscriptionValidThrough,
@RenewalDate = LSD.RenewalDate,
@NoOfImages = LSD.NoofImages,
@TotalAmount = LSD.TotalAmount
from LicenseSubscriptionDetail LSD where LSD.LicenseId = @Id order by LSD.Id desc;
select top 1 @SiteIp = S.SiteIP, @SiteIpTo = S.SiteIPTo, @SiteMasterIPTo = S.SiteMasterIPTo from Site S
inner join SiteToLicenseEdition SLE ON S.Id = SLE.SiteId
inner join LicenseToEdition LE on SLE.LicenseEditionId = LE.Id
where LE.LicenseId = @Id and S.IsMaster = 1 AND S.IsActive = 1 order by SLE.SiteId desc;
select top 1 @UserId = US.Id, @Login = US.LoginId, @Password = US.Password, @SecurityQuestionId = US.SecurityQuestionId,
@Answer = US.SecurityAnswer from AIAUserToLicenseEdition UL inner join LicenseToEdition LE on UL.LicenseEditionId = LE.Id
inner join AIAUser US on UL.UserId = US.Id where LE.LicenseId = @Id order by LE.Id desc;
select L.Id, L.LicenseTypeId, L.AccountNumber, L.AccountTypeId, L.Address1, L.Address2, L.LicenseeFirstName, L.LicenseeLastName, L.City,
L.CountryId, L.StateId, L.EmailId, L.InstitutionName, L.Phone, L.ProductId, L.Zip, L.TotalLogins, @EditionLoginText as EditionLogins,
@SubscriptionStartDate as SubscriptionStartDate, @SubscriptionEndDate as SubscriptionEndDate, @NoOfImages as NoOfImages, @TotalAmount as Price,
@SiteIp as SiteUrl, @SiteIpTo as SitToUrl, @SiteMasterIPTo as SiteMasterUrl, @UserId as UserId, @Login as Login, @Password as Password,
@SecurityQuestionId as SecurityQuestionId, @Answer as Answer, L.NoOfRenewals as TotalRenewals, @RenewalDate as RenewalDate, L.IsActive
from License L where L.Id = @Id;
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO