USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetLicenseById]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================  
-- Author:  Magic Software  
-- Create date: 29-Jan-2018  
-- Description: To get a license information on the basis of LicenseId  
-- ====================================================

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