/****** Object:  StoredProcedure [dbo].[ЛК:ИнформацияЛС]    Script Date: 20.11.2020 20:01:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/* Translated by $SQLTRANS utility according to "SQL-file" technology. Method author: Sergei Y.Kitaev.
** Script translation date-time: 20.11.2020 19:40:03.92 (stamp of applying to SQL-file). */
create procedure [dbo].[ЛК:ИнформацияЛС] @ИДЛС 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 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; begin try if 0=1 print '$(SQL_FILE_TECHNOLOGY)';
----------------------------------------------------------------------------------------------------
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 open 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
