Frédéric FOURGEOT fourgeotf

## 1 - Situation stock SHOPPING.sql
-- A lancer en premier afin de créer la table CIMSHOPPING2021.dbo.SituationStock2021
-- ATTENTION : La jointure avec Repository et Site se fait en LEFT pour la situation de stock sur la base SHOPPING
--             alors que l'on utilisera INNER pour les autres bases afin de contraindre le résultat. Sur SHOPPING
--             nous voulons récupérer l'ensemble des lignes du fichier F_ARTSTOCK alors que pour les autres bases
--             nous ne voulons que les lignes qui correspondent aux différents magasins.
USE SHOPPING
GO

WITH dl
AS

## App.razor
<Router AppAssembly="@typeof(App).Assembly">
    <Found Context="routeData">
        <AuthorizeRouteView RouteData="@routeData" DefaultLayout="@typeof(MainLayout)" />
        <FocusOnNavigate RouteData="@routeData" Selector="h1" />
    </Found>
    <NotFound>
        <CascadingAuthenticationState>
            <PageTitle>Not found</PageTitle>
            <LayoutView Layout="@typeof(MainLayout)">
                <h1>404 Error</h1>

## CIMPRODUCT - TG_OF_Insert.sql
USE [Cimprod_Mithieux]
GO

/****** Object:  Trigger [dbo].[TG_OF_Insert]    Script Date: 09/02/2022 11:59:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

## CIMPRODUCT - F_VerifClotureMetaOF.sql
USE [Cimprod_Mithieux]
GO

/****** Object:  UserDefinedFunction [dbo].[F_VerifClotureOF]    Script Date: 09/02/2022 11:46:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

## Autorisations sur bases SAGE.sql
EXEC sp_change_users_login 'update_one','user_cbase','APPL_CBASE'

ALTER AUTHORIZATION on database::[NOMBASE] to sa


## Mise en sommeil des clients créés depuis plus de 5 ans sans documents.sql
--SELECT
--         ct.CT_Num
--        ,ct.cbCreation
--        ,ct.CT_NumPayeur
--        ,do.Max_DO_Date
--        ,ct.CT_Sommeil
UPDATE ct SET ct.CT_Sommeil = 1
FROM F_COMPTET ct
LEFT JOIN (SELECT do.DO_Tiers, MAX(do.DO_Date) AS Max_DO_Date FROM F_DOCENTETE do WHERE do.DO_Domaine = 0 GROUP BY do.DO_Tiers) AS do ON do.DO_Tiers = ct.CT_NumPayeur
WHERE YEAR(ct.cbCreation) <= 2017

## Mise en sommeil des clients inactifs depuis 5 ans.sql
USE LOISIRALP
GO


WITH query AS (SELECT
  ct.CT_Num
 ,ct.CT_Intitule
 ,(SELECT TOP 1 do.DO_Date FROM F_DOCENTETE do WHERE do.DO_Tiers = ct.CT_Num AND DO_Domaine = 0 ORDER BY do.DO_Date DESC) AS DO_Date
 ,(SELECT TOP 1 do.DO_Type FROM F_DOCENTETE do WHERE do.DO_Tiers = ct.CT_Num AND DO_Domaine = 0 ORDER BY do.DO_Date DESC) AS DO_Type
FROM F_COMPTET ct
	-- A lancer en premier afin de créer la table CIMSHOPPING2021.dbo.SituationStock2021
	-- ATTENTION : La jointure avec Repository et Site se fait en LEFT pour la situation de stock sur la base SHOPPING
	-- alors que l'on utilisera INNER pour les autres bases afin de contraindre le résultat. Sur SHOPPING
	-- nous voulons récupérer l'ensemble des lignes du fichier F_ARTSTOCK alors que pour les autres bases
	-- nous ne voulons que les lignes qui correspondent aux différents magasins.
	USE SHOPPING
	GO

	WITH dl
	AS
	<Router AppAssembly="@typeof(App).Assembly">
	<Found Context="routeData">
	<AuthorizeRouteView RouteData="@routeData" DefaultLayout="@typeof(MainLayout)" />
	<FocusOnNavigate RouteData="@routeData" Selector="h1" />
	</Found>
	<NotFound>
	<CascadingAuthenticationState>
	<PageTitle>Not found</PageTitle>
	<LayoutView Layout="@typeof(MainLayout)">
	<h1>404 Error</h1>
	USE [Cimprod_Mithieux]
	GO

	/**** Object: Trigger [dbo].[TG_OF_Insert] Script Date: 09/02/2022 11:59:39 ****/
	SET ANSI_NULLS ON
	GO

	SET QUOTED_IDENTIFIER ON
	GO
	USE [Cimprod_Mithieux]
	GO

	/**** Object: UserDefinedFunction [dbo].[F_VerifClotureOF] Script Date: 09/02/2022 11:46:41 ****/
	SET ANSI_NULLS ON
	GO

	SET QUOTED_IDENTIFIER ON
	GO
	EXEC sp_change_users_login 'update_one','user_cbase','APPL_CBASE'

	ALTER AUTHORIZATION on database::[NOMBASE] to sa
	--SELECT
	-- ct.CT_Num
	-- ,ct.cbCreation
	-- ,ct.CT_NumPayeur
	-- ,do.Max_DO_Date
	-- ,ct.CT_Sommeil
	UPDATE ct SET ct.CT_Sommeil = 1
	FROM F_COMPTET ct
	LEFT JOIN (SELECT do.DO_Tiers, MAX(do.DO_Date) AS Max_DO_Date FROM F_DOCENTETE do WHERE do.DO_Domaine = 0 GROUP BY do.DO_Tiers) AS do ON do.DO_Tiers = ct.CT_NumPayeur
	WHERE YEAR(ct.cbCreation) <= 2017
	USE LOISIRALP
	GO


	WITH query AS (SELECT
	ct.CT_Num
	,ct.CT_Intitule
	,(SELECT TOP 1 do.DO_Date FROM F_DOCENTETE do WHERE do.DO_Tiers = ct.CT_Num AND DO_Domaine = 0 ORDER BY do.DO_Date DESC) AS DO_Date
	,(SELECT TOP 1 do.DO_Type FROM F_DOCENTETE do WHERE do.DO_Tiers = ct.CT_Num AND DO_Domaine = 0 ORDER BY do.DO_Date DESC) AS DO_Type
	FROM F_COMPTET ct