--:SetVar Procedure "ЛК:ИнформацияЛС" -- account info
if exists( select * from [sysobjects] (nolock) where [type]='P' and [name]=N'ЛК:ИнформацияЛС' )
   drop procedure [ЛК:ИнформацияЛС]
GO
/* Translated by $SQLTRANS utility (SQL-file technology). Method author: Sergei Y.Kitáev.
** Script translation date-time: 2022-11-27 00:37:48.28 (stamp of SQL-file generation). */
create procedure [ЛК:ИнформацияЛС] @ИДЛС uniqueidentifier
AS
/*==================================================================================================
# Flat owner personal cabinet | MyFlat sample project | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# ACCOUNT INFO:
#
# === Usage: ===
# exec [ЛК:ИнформацияЛС] <ИД ЛС> -- account ID
#
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# === Result-set: ===
# [ФИО]                   $(dt_String_RUH)  not null -- flat owner initials
# [Адрес]                 $(dt_String_RUH)  not null -- flat owner address
# [Кол-во проживающих]    int               not null -- really live (person count)
# [Общая площадь]         $(dt_Square_2)    not null -- total flat square
# [Жилая площадь]         $(dt_Square_2)    not null -- living flat territory square
# [Название района]       $(dt_String_RUH)  not null -- district name
# [Название жил.орг.]     $(dt_String_RUH)  not null -- name of technical subdivision
==================================================================================================*/
begin begin try set xact_abort off; set ansi_nulls,ansi_null_dflt_on,quoted_identifier,arithabort,nocount on; set implicit_transactions off; declare @initial_tran_count_aux int, @is_local_tran_open_aux bit; select @initial_tran_count_aux=@@trancount, @is_local_tran_open_aux=0; end try begin catch throw; end catch; begin try
----------------------------------------------------------------------------------------------------
if @ИДЛС is null
   exec [AUX:RaiseIncorrectProcedureCall] N'ЛК:ИнформацияЛС'
----------------------------------------------------------------------------------------------------
set NOCOUNT off

select
   RTRIM( лс.[ФИО] ) 'ФИО', -- flat owner initials
   RTRIM( ул.[Название] )+', д.'+RTRIM( д.[Номер дома] )+', кв.'+RTRIM( кв.[Номер квартиры] ) 'Адрес', -- address
   лс.[Кол-во проживающих], -- really live (person count)
   лс.[Общая площадь], -- total flat square
   лс.[Жилая площадь], -- living flat territory square
   RTRIM( р.[Название] ) 'Название района', -- district name
   RTRIM( жо.[Название] ) 'Название жил.орг.'-- tech.subdivision name
from [Лицевые счета] лс (nolock) -- accounts
join [Квартиры] кв (nolock) -- flats
     on кв.[ИД] = лс.[ИД квартиры]
join [Дома] д (nolock) -- buildings
     on д.[ИД] = кв.[ИД дома]
join [Улицы] ул (nolock) -- streets
     on ул.[ИД] = д.[ИД улицы]
join [Районы] р (nolock) -- city districts
     on р.[ИД] = д.[ИД района]
join [Жил.организации] жо (nolock) -- technical subdivisions
     on жо.[ИД] = д.[ИД ЖО]
where лс.[ИД] = @ИДЛС
if @@ROWCOUNT = 0 RETURN 1
if @@ROWCOUNT > 1
   exec [AUX:RaiseUnexpectedInternalError] N'ЛК:ИнформацияЛС', 'D1BDDEE4-7A8D-452A-8774-2DF1D58C80DD'

----------------------------------------------------------------------------------------------------
if @is_local_tran_open_aux=1 raiserror ('Not closed local transaction was detected, previously opened by BEGIN_TRANSACTION_AUX macro.',11,1); end try begin catch if @is_local_tran_open_aux=1 and (@@trancount>@initial_tran_count_aux or xact_state()=-1) rollback transaction; set @is_local_tran_open_aux=0; throw; end catch; end
/*================================================================================================*/
GO
/*
exec [ЛК:ИнформацияЛС] '09C057D7-772A-43CF-9F8F-DE58A3A283E1'
exec [ЛК:ИнформацияЛС] 'B8707DFD-E4A0-41A5-8D33-F28FBC630898'
*/
