
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_login]
	@user_code ais_code_medium,
	@pwd nvarchar(64),
	@cache bit
as

	declare @t_login table
	(
		usr_code ais_code_medium not null primary key,
		profil_code ais_code_short not null,
		culture_code ais_code_short not null
	)


	insert into @t_login(usr_code, profil_code, culture_code)
	select	usr_user_code, usr_profil_code, usr_culture_code
	from	usr_users
	where	usr_user_code = @user_code
	and		usr_pwd = @pwd

	if not exists (	select	1 from	@t_login )
	begin
		-- login incorrect, message d'erreur
		select	* from usr_users where 1 = 2
		return
	end

	-- login correct, envoyer les autres informations 


	select	u.*
	from	usr_users u
			inner join @t_login t on t.usr_code = u.usr_user_code

	select	p.*
	from	usr_profils p
			inner join @t_login t on t.profil_code = p.pr_profil_code

	if not exists(select 1 from usr_users_options where usro_user_code = @user_code)
	begin
		insert into usr_users_options(usro_user_code) select @user_code
	end

	select	uuo.*
	from	usr_users_options uuo
	where usro_user_code = @user_code

	select	c.*
	from	md_cultures c
			inner join @t_login t on t.culture_code = c.clt_culture_code

	-- recupération des informations concernant l'apporteur associé à l'utilisateur (s'il existe)
	select	i.int_actor_id, i.int_short_code, i.act_internal_reference
	from	vw_act_introducers i
			inner join usr_users u on u.usr_introducer_id = i.int_actor_id
			inner join @t_login t on t.usr_code = u.usr_user_code

	-- envoyer les options pour tester la version de la base de données
	select * from ais_options

	-- envoyer la liste des entités (distinct) associées à l'utilisateur connecté
	select	b.*
	from	vw_act_branches b
			inner join	(
						select	distinct usrbdep_branch_id
						from	usr_users_branches_departments ubd 
						where	usrbdep_user_code = @user_code
						) as t on t.usrbdep_branch_id = b.br_actor_id

	-- envoyer la liste des départements associés à l'utilisateur connecté
	select	bd.*
	from	usr_users_branches_departments ubd
			inner join act_branches_departments bd	on	bd.brdep_branch_id = ubd.usrbdep_branch_id
													and	bd.brdep_department_code = ubd.usrbdep_department_code
	where	usrbdep_user_code = @user_code

	
	if @cache != 0
	begin
		-- renvoi des données pour la mise en cache
		select * from md_languages
		select * from md_types
		select * from md_grids_summaries
		select * from nm_types_mvts_clm
		select * from nm_types_mvts_pol
		select * from nm_status_clients
		select * from md_masks

		select * from nm_pol_operations
		select * from nm_risks_movements
		


		/*
		DGE
			Déterminer la culture de l'utilisateur et/ou le dictionnaire
		*/
		DECLARE	@dictionary_code	ais_code_short,
				@language_code	ais_code_short

		select	@dictionary_code = u.usr_dictionary_code,
				@language_code = c.clt_language_code
		from	usr_users u
				inner join md_cultures c on c.clt_culture_code = u.usr_culture_code
				inner join @t_login t on t.usr_code = u.usr_user_code


		-- strings
		select	s.str_string_name, 
				str_l1 =	coalesce(t.dicts_string, 
							case 
							when @language_code = 'l2' then isnull(s.str_l2, s.str_l1)
							when @language_code = 'l3' then isnull(s.str_l3, s.str_l1)
							else str_l1
							end),
				str_l2 = null,
				str_l3 = null
		from	md_strings s
				left join	(
							select	ds.dicts_string_name, ds.dicts_string
							from	md_dictionaries_strings ds
							where	ds.dicts_dictionary_code = @dictionary_code
							) as t	on t.dicts_string_name = s.str_string_name

		-- tables
		select	tbl_table_name,
				tbl_rowver, 
				tbl_l1 =	coalesce(tmp.dictt_string, 
							case
							when @language_code = 'l2' then t.tbl_l2
							when @language_code = 'l3' then t.tbl_l3
							else t.tbl_l1
							end, 
							t.tbl_l1),
				tbl_l2 = null,
				tbl_l3 = null,
				tbl_prefix,
				tbl_virtual
		from	md_tables t
				left join	(
							select	dt.dictt_table_name, dt.dictt_string
							from	md_dictionaries_tables dt
							where	dt.dictt_dictionary_code = @dictionary_code
							) as tmp	on tmp.dictt_table_name = t.tbl_table_name

		-- fields
		select	fld_table_name,
				fld_field_name,
				fld_rowver,
				fld_l1 =	coalesce( t.dictf_string,
							case
							when @language_code = 'l2' then f.fld_l2
							when @language_code = 'l3' then f.fld_l3
							else f.fld_l1
							end, 
							f.fld_l1),
				fld_l2 = null,
				fld_l3 = null,
				fld_dsc_l1,
				fld_dsc_l2,
				fld_dsc_l3,
				fld_type,
				fld_size,
				fld_nullable,
				fld_read_only,
				fld_virtual,
				fld_mask_code,
				fld_default,
				fld_link_table_name,
				fld_pk,
				fld_ordinal_position,
				fld_grid_name,
				fld_grid_hidden
		from	md_fields f
				left join	(
							select	df.dictf_table_name, df.dictf_field_name, df.dictf_string
							from	md_dictionaries_fields df
							where	df.dictf_dictionary_code = @dictionary_code
							) as t	on	t.dictf_table_name = f.fld_table_name
									and	t.dictf_field_name = f.fld_field_name
		order by f.fld_table_name, fld_ordinal_position -- l'ordre de tri est important pour l'affichage dans les grilles



		-- fields cpl
		select	fld_table_name,
				fld_field_name,
				fld_rowver,
				fld_l1 =	coalesce( t.dictfc_string,
							case
							when @language_code = 'l2' then f.fld_l2
							when @language_code = 'l3' then f.fld_l3
							else f.fld_l1
							end,
							f.fld_l1),
				fld_l2 = null,
				fld_l3 = null,
				fld_dsc_l1,
				fld_dsc_l2,
				fld_dsc_l3,
				fld_type,
				fld_size,
				fld_nullable,
				fld_read_only,
				fld_virtual,
				fld_mask_code,
				fld_default,
				fld_link_table_name,
				fld_pk,
				fld_ordinal_position
		from	md_fields_cpl f
				left join	(
							select	df.dictfc_table_name, df.dictfc_field_name, df.dictfc_string
							from	md_dictionaries_fields_cpl df
							where	df.dictfc_dictionary_code = @dictionary_code
							) as t	on	t.dictfc_table_name = f.fld_table_name
									and	t.dictfc_field_name = f.fld_field_name
							

		select	grd_grid_name,
				grd_rowver,
				grd_l1 =	coalesce( t.dictg_string,
							case
							when @language_code = 'l2' then g.grd_l2
							when @language_code = 'l3' then g.grd_l3
							else g.grd_l1
							end, 
							g.grd_l1),
				grd_l2 = null,
				grd_l3 = null,
				grd_value_table_name,
				grd_value_field_name,
				grd_display_table_name,
				grd_display_field_name,
				grd_sql,
				grd_columns_fill,
				grd_show_required_icon,
				g.grd_display_combo_expression
		from	md_grids g
				left join	(
							select	dg.dictg_grid_name, dg.dictg_string
							from	md_dictionaries_grids dg
							where	dg.dictg_dictionary_code = @dictionary_code
							) as t	on t.dictg_grid_name = g.grd_grid_name

		-- l'ordre de tri est important pour l'affichage dans les grilles
		select * from md_grids_fields order by grdf_grid_name, grdf_order


		select * from md_strings -- on a besoin aussi de la table complète

	end







GO
