SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_doc_collections_start_mail]
	@table_id					type_table_id READONLY,			-- documents à traiter
	@guid						varchar(64),					-- guid
	@execution_utc_date			datetime,						-- date d'exéction UTC
	@language_code				ais_code_short,					-- code language de l'utilisateur, pour la MAJ des erreurs
	@error_mail_template		ais_name,						-- err template KO
	@error_mail_status_code		ais_name,						-- err statut incorrect
	@error_mail_object_id		ais_name,						-- err fichier manquant
	@error_mail_addr			ais_name,						-- err adresse email KO
	@error_mail_auth			ais_name						-- err le client n'autorise pas la réception des mails
	


AS

	/*
	DGE 02/03/2016

	Cette procédure sert à déclencher l'envoi des mails d'une liste de documents.
	*/

	set nocount on;

	-- maj des documents à traiter (guid)
	update	d
	set		d.dcd_mail_guid = @guid,
			d.dcd_mail_utc_date = null,
			d.dcd_obs = null
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
			left join vw__obj_objects_select o on o.obj_object_id = d.dcd_object_id
	where	d.dcd_status_code != 'L'	-- les documents bloqués ne sont pas traités



	-- exclure les documents avec un template vide
	update	d
	set		d.dcd_mail_utc_date = @execution_utc_date,
			d.dcd_status_code = 'SE',
			d.dcd_obs = (	select	case @language_code
									when 'l3' then coalesce(str_l3, str_l1)
									when 'l2' then coalesce(str_l2, str_l1)
									else str_l1
									end
							from	md_strings 
							where	str_string_name = @error_mail_template)
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null
	and		isnull(d.dcd_mail_code, '') = ''


	-- exclure les documents en statut ME (merge error) et L (locked)
	update	d
	set		d.dcd_mail_utc_date = @execution_utc_date,
			d.dcd_status_code = 'SE',
			d.dcd_obs = (	select	case @language_code
									when 'l3' then coalesce(str_l3, str_l1)
									when 'l2' then coalesce(str_l2, str_l1)
									else str_l1
									end
							from	md_strings 
							where	str_string_name = @error_mail_status_code)
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null
	and		d.dcd_status_code in ('L', 'ME')

	-- exclure les documents sans object_id
	update	d
	set		d.dcd_mail_utc_date = @execution_utc_date,
			d.dcd_status_code = 'SE',
			d.dcd_obs = (	select	case @language_code
									when 'l3' then coalesce(str_l3, str_l1)
									when 'l2' then coalesce(str_l2, str_l1)
									else str_l1
									end
							from	md_strings 
							where	str_string_name = @error_mail_object_id)
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
			left join vw__obj_objects_select o on o.obj_object_id = d.dcd_object_id
	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null
	and		ISNULL(o.obj_object_id, 0) <= 0

	-- exclure les documents pour lesquels l'adresse de mail n'est pas présente
	update	d
	set		d.dcd_mail_utc_date = @execution_utc_date,
			d.dcd_status_code = 'SE',
			d.dcd_obs = (	select	case @language_code
									when 'l3' then coalesce(str_l3, str_l1)
									when 'l2' then coalesce(str_l2, str_l1)
									else str_l1
									end
							from	md_strings 
							where	str_string_name = @error_mail_addr)
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
			inner join vw_act_clients v on v.act_actor_id = d.dcd_actor_id
	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null
	and		isnull(v.adr_mail, '') = ''


	-- exclure les documents pour lesquels le client n'autorise pas l'envoi par mail
	update	d
	set		d.dcd_mail_utc_date = @execution_utc_date,
			d.dcd_status_code = 'SE',
			d.dcd_obs = (	select	case @language_code
									when 'l3' then coalesce(str_l3, str_l1)
									when 'l2' then coalesce(str_l2, str_l1)
									else str_l1
									end
							from	md_strings 
							where	str_string_name = @error_mail_auth)
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id

			inner join	(
						select	d.dcd_document_id
						from	doc_collections_docs d
								inner join @table_id t on t.id = d.dcd_document_id
								inner join doc_collections c on c.dc_collection_id = d.dcd_collection_id
								inner join acc_cts_bordereaux b on b.ctsb_bordereau_id = c.dc_id 
								inner join act_clients cli on cli.cli_actor_id = d.dcd_actor_id
						where	d.dcd_mail_guid = @guid				
						and		d.dcd_mail_utc_date is null
						and		c.dc_type_code = 'CTS'				-- uniquement des documents de trpe CTS	
						and		0 = case b.ctsb_type_code
									when 'PN' then cli.cli_send_mail_for_pn 	-- Premium notice	Avis de prime
									when 'PR' then cli.cli_send_mail_for_pr		-- Premium recall	Appel de prime
									when 'UR' then cli.cli_send_mail_for_ur		-- Unpaid reminder	Rappel d'impayé
									when 'FN' then cli.cli_send_mail_for_fn		-- Formal notice	Mise en demeure
									when 'FNR' then cli.cli_send_mail_for_fnr	-- Formal notice reminder	Rappel mise en demeure
									when 'CS' then cli.cli_send_mail_for_cs		-- Contentious suspension	Suspension contentieux
									when 'CT' then cli.cli_send_mail_for_ct		-- Contentious termination	Résiliation contentieux
									end
						) as tmp on tmp.dcd_document_id = d.dcd_document_id

	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null



	-- sélection des documents
	select	d.*,
			va.act_internal_reference, 
			va.cli_culture_code, l.lng_language_code,
			va.adr_mail, 
			va.adr_complete_name, 
			m.*,
			o.*
	from	doc_collections_docs d
			inner join @table_id t on t.id = d.dcd_document_id
			inner join vw_act_clients va on va.act_actor_id = d.dcd_actor_id
			inner join vw__obj_objects_select o on o.obj_object_id = d.dcd_object_id
			inner join nm_mails m on m.mail_mail_code = d.dcd_mail_code
			inner join md_cultures clt on clt.clt_culture_code = va.cli_culture_code
			inner join md_languages l on l.lng_language_code = clt.clt_language_code
	where	d.dcd_status_code != 'L'
	and		d.dcd_mail_guid = @guid
	and		d.dcd_mail_utc_date is null

GO
