USE [OblakLive-02-12]
GO

/****** Object:  StoredProcedure [vm].[VirtualMachineGetAllByAccountID]    Script Date: 27-Jan-20 11:01:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [vm].[VirtualMachineGetAllByAccountID]
( @AccountID int, @StatusID tinyint = null)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Now datetime2(3) = GETUTCDATE()

	SELECT distinct
			VM.[InstanceID]
			,VM.[VMMInstanceID]
			,VM.[VMMServerID]
			,VM.[AccountID]
			,VM.[CloudID]
			,VM.[Label]
			,VM.[Description]
			,VM.[ComputerName]
			,VM.[SourceTemplateID]
			,VM.[SourceVMID]
			,VM.[ModifiedDate]
			,VM.[CreatedDate]
			,VM.[Status]
			,st.StatusName
			,VM.[TimeZoneID]
			,VM.VMQueueMetadata
			,VM.FailedCreateAttempts
			,tp.[VMMTemplateID]
			,case when tp.TemplateStatusID = 4 then 1 else 0 end as IsDemo -- TemplateStatusID = 4 -> Demo
			,vmsource.VMMInstanceID as SourceVMVMMID
			,tz.[TimeZoneDisplay]
			,ipassign.VMMPrivateIP as PrivateIP
			,ip.HostIPv4 as PublicIP
			,cs.ConsoleShareID
			,cs.Email as ReceiverEmail
			,acc.Email as SharerEmail
			,cs.[Password]
			,cs.IsShareUsed
			,cs.ExpirationDate
			,cs.CreatedDate as SharedDate
	FROM [vm].[VirtualMachine] VM
		inner join lookup.VirtualMachineStatus st on st.StatusID = VM.Status
		left outer join [vm].[Template] tp on tp.TemplateID = VM.SourceTemplateID
		left outer join [vm].VirtualMachine vmsource on vmsource.InstanceID = vm.SourceVMID
		left outer join [lookup].[TimeZones] tz on tz.TimeZoneID = VM.[TimeZoneID]
		left outer join network.PublicIPVMAssignment ipassign on vm.InstanceID = ipassign.VMInstanceID and ipassign.Active = 1
		left outer join network.PublicIP ip on ipassign.PublicIPID = ip.PublicIPID
		left outer join vm.ConsoleShare cs ON cs.InstanceID = VM.InstanceID AND ExpirationDate > @Now
		left outer join t.Account acc ON acc.AccountID = cs.AccountID
	WHERE VM.[AccountID] = @AccountID and
		(@StatusID is null or VM.[Status] = @StatusID)
END

GO