1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Un collèque
-- Create date: 2015-07-31
-- Description: Build customer allocation (SB) from matrix C024
-- SB is updated through the use of a temporary table and an import
-- History:
-- 2015-09-11 Un collèque : Create allocation for Customers only
-- 2015-10-29 Un collèque : Species criteria is now linked to the species of the productions (C022) of the customer
-- 2015-11-27 Un collèque : Do not allocate and delete allocation of inactive users
-- 2016-05-04 Moi : Optimisation
-- 2016-05-19 Moi : Réécriture complète
-- 2016-06-17 Moi : Ajout de SB_StaNo et SB_SerNo pour la suppression
-- 2018-06-19 Moi : Ajout d'une règle pour pouvoir recopier les SB d'un rep vers un autre
-- 2018-10-24 Moi : Ajout d'une règle pour pouvoir affecter une plage de code postaux
-- 2019-01-25 Moi : Ajout de critères d'exclusions d'une règle à l'autre pour éviter qu'une règle plus large ne prenne le pas sur un paramétrage plus fin)
-- 2019-06-06 Moi : Ajout d'un test sur SB.FreiL1 (Disabled) en plus de LosKZ
-- 2019-09-16 Moi : Modification du test pour suppression des SB avec mauvaise clé externe + petites optimisations
-- =============================================
ALTER PROCEDURE [dbo].[CU_Customer_Allocation]
AS
BEGIN
SET NOCOUNT ON;
declare @dte datetime2;
-- Création d'une table temporaire qui servira à générer l'allocation complète
print 'Création de la table temporaire';
create table #tmp (id_fi bigint, id_id bigint, extkey as (concat(cast(id_fi / 4294967296 as bigint), '_', id_fi & cast(4294967295 as bigint), '_', id_id)) persisted, primary key (id_fi, id_id));
create unique index uix_tmp on #tmp (extkey);
set @dte = SYSDATETIME();
-- Suppression des allocations archivées
print 'Suppression des allocations déjà traitées';
DELETE CU_CustomerAllocation WHERE Allocated = 1;
-- Archivage de la précédente allocation
print 'Archivage des allocations en cours';
UPDATE CU_CustomerAllocation SET Allocated = 1 WHERE Allocated = 0;
print 'Génération de la liste des allocations théoriques';
insert into #tmp (id_fi, id_id)
-- Règle 1
-- On affecte à un utilisateur les clients d'un tenant qui font une espèce donnée dans une plage de codes INSEE donnée
SELECT fi.id, id.id
FROM dbo.te_isi_C024 C024
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
WHERE C024.del = 0
and C024.f7000 > 0
and C024.f7004 > 0
and C024.f7006 > 0
and C024.f7007 = 0
and C024.f7008 = 0
and isnull(C024.F7011, '') <> ''
and isnull(C024.F7012, '') <> ''
and C024.F7015 = 0
and isnull(C024.F7019, '') = ''
and isnull(C024.F7020, '') = ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
and exists (
select *
from te_isi_C022 SP
where SP.F7003 = c024.f7006 AND SP.ID_FI = FI_GLO.ID AND SP.DEL = 0
)
union
-- Règle 2
-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes INSEE donnée
SELECT fi.id, id.id
FROM dbo.te_isi_C024 C024
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
WHERE C024.del = 0
and C024.f7000 > 0
and C024.f7004 > 0
and C024.f7006 = 0
and C024.f7007 = 0
and C024.f7008 = 0
and isnull(C024.F7011, '') <> ''
and isnull(C024.F7012, '') <> ''
and C024.F7015 = 0
and isnull(C024.F7019, '') = ''
and isnull(C024.F7020, '') = ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
union
-- Règle 3
-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau donné sans sous-réseau dans une plage de codes INSEE donnée
SELECT fi.id, id.id
FROM dbo.te_isi_C024 C024
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
WHERE C024.del = 0
and C024.f7000 > 0
and C024.f7004 > 0
and C024.f7006 = 0
and C024.f7007 > 0
and C024.f7008 = 0
and isnull(C024.F7011, '') <> ''
and isnull(C024.F7012, '') <> ''
and C024.F7015 = 0
and isnull(C024.F7019, '') = ''
and isnull(C024.F7020, '') = ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
union
-- Règle 4
-- On affecte à un utilisateur les clients d'un tenant qui ont un réseau et un sous-réseau donné dans une plage de codes INSEE donnée
SELECT fi.id, id.id
FROM dbo.te_isi_C024 C024
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and FI.F7020 = C024.F7007 and FI.F7021 = C024.F7008 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.F7004 between c024.f7011 and c024.f7012
WHERE C024.del = 0
and C024.f7000 > 0
and C024.f7004 > 0
and C024.f7006 = 0
and C024.f7007 > 0
and C024.f7008 > 0
and isnull(C024.F7011, '') <> ''
and isnull(C024.F7012, '') <> ''
and C024.F7015 = 0
and isnull(C024.F7019, '') = ''
and isnull(C024.F7020, '') = ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
union
-- Règle 5
-- On affecte à un utilisateur tous les SB d'un autre utilisateur
SELECT SB.ID_FI, C024.F7000
FROM dbo.TE_ISI_C024 C024
INNER JOIN dbo.TE_ISI_SB SB on SB.BearbeiterId = C024.F7015 and SB.LosKZ = 0 and SB.FreiL1 = 0
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
WHERE C024.DEL = 0
and C024.F7000 > 0
--and C024.f7004 = 0
and C024.f7006 = 0
and C024.f7007 = 0
and C024.f7008 = 0
and isnull(C024.F7011, '') = ''
and isnull(C024.F7012, '') = ''
and C024.F7015 > 0
and isnull(C024.F7019, '') = ''
and isnull(C024.F7020, '') = ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014
union
-- Règle 6
-- On affecte à un utilisateur les clients d'un tenant sans une espèce dans une plage de codes postaux donnée
SELECT fi.id, id.id
FROM dbo.te_isi_C024 C024
INNER JOIN dbo.te_isi_id id on id.id = C024.F7000 and id.Inaktiv = 0
inner join dbo.te_isi_fi fi on fi.mandnr = c024.F7004 and 2 in (FI.Fityp, FI.Fityp2, FI.Fityp3, FI.Fityp4, FI.Fityp5) AND FI.LosKZ = 0 AND FI.Plz between c024.f7019 and c024.f7020
INNER JOIN te_isi_FI FI_GLO on FI_GLO.MandNr = 0 AND FI_GLO.ID = FI.ID_FI_250 AND FI_GLO.LosKZ = 0
WHERE C024.del = 0
and C024.f7000 > 0
and C024.f7004 > 0
and C024.f7006 = 0
and C024.f7007 = 0
and C024.f7008 = 0
and isnull(C024.F7011, '') = ''
and isnull(C024.F7012, '') = ''
and C024.F7015 = 0
and isnull(C024.F7019, '') <> ''
and isnull(C024.F7020, '') <> ''
and dbo.CU_Date_To_CRMDate(@dte) between C024.F7013 AND C024.F7014;
-- On enregristre la liste des SB à désallouer
print 'Identification des allocations existantes à supprimer';
insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
select
'DEL',
null,
null,
null,
@dte,
dbo.CU_ID_To_StaNo(sb.ID),
dbo.CU_ID_To_SerNo(sb.ID)
from dbo.te_isi_sb sb
where sb.LosKZ = 0 and sb.FreiL1 = 0 and sb.F7007 = 1
and not exists (
select 1 / 0
from #tmp tmp
where sb.F4018 = tmp.extkey
);
-- On exclu les allocations déjà existantes
print 'Exclusion des allocations déjà correctes';
delete #tmp
where exists
(
select *
from dbo.te_isi_sb sb
where sb.BearbeiterId = #tmp.id_id
and sb.ID_FI = #tmp.id_fi
and sb.LosKZ = 0
and sb.FreiL1 = 0
and sb.F4018 = #tmp.extkey
);
-- On enregistre les allocations à créer
print 'Ajout des allocations à créer';
insert into dbo.CU_CustomerAllocation ([mode], fi_stano, fi_serno, rep, [date], SB_StaNo, SB_SerNo)
select 'NEW',
dbo.CU_ID_To_StaNo(FI.ID) CompanyStaNo,
dbo.CU_ID_To_SerNo(FI.ID) CompanySerNo,
id.id,
@dte,
dbo.CU_ID_To_StaNo(SB.ID) SB_StaNo,
dbo.CU_ID_To_SerNo(SB.ID) SB_SerNo
from #tmp tmp
inner join dbo.te_isi_fi fi on fi.id = tmp.ID_fi
inner join dbo.te_isi_id id on id.id = tmp.id_id
left outer join dbo.te_isi_sb sb on sb.LosKZ = 0 and sb.F7007 = 1 and sb.FreiL1 = 1 and sb.F4018 = tmp.extkey;
-- On détruit la table temporaire
print 'Destruction de la table temporaire';
drop table #tmp;
END; |