Partage

Equivalent RechercheV Excel ?

PHP/MySQL

8 novembre 2019 à 11:32:11

Bonjour à tous,

Pour un projet de migration d'un outil de tarification excel vers une interface web (php) j'ai besoin de "trouver" l'équivalent de la fonction recherchev excel en Php/MySQL.

Le fonctionnement actuel :

J'entre la date de naissance d'une personne et la date de début souhaitée du contrat.

Cela me calcule donc son age à la date de début du contrat et RECHERCHE cet age en colonne 1 de ma plage cellules définie et me RETOURNE la valeur correspondante dans la colonne 2 (ou toute autre colonne définie dans la formule - puisqu'en colonne 1 j'ai mes ages, et dans les colonnes 2,3,4... ce sont mes différents contrats et leurs tarifs selon les ages).

Ex:

Né en 1950 - Date début : 2020 - Recherche de 70 dans colonne 1, renvoi de la valeur associée en colonne 2 -> 83.60€

Né en 1950 - Date début : 2020 - Recherche de 70 dans colonne 1, renvoi de la valeur associée en colonne 3 -> 97.20€

J'ai déja mis en place mon calcul d'age en PHP :

<!DOCTYPE html>
<html>
<head>
<title>Calcul</title>
<meta charset="utf-8" />
</head>
<body>
<h2>Calcul</h2>
			
	<form  method="post">
		<p> Année de naissance : <input type="int" name="datenais" /></p>
		<p> Année effet : <input type="int" name="dateeff" /></p>
		<p><input type="submit" value="ok"></p>
	</form>


<?php
if (isset ($_POST ['datenais']) AND isset ($_POST ['dateeff']))
{
 $datenais = $_POST ['datenais'];
 $dateeff = $_POST ['dateeff'];
 $age1 = $dateeff - $datenais;
echo $age1;}
 
?>
</body>
</html>

Donc connaissez vous une équivalence de recherchev en sql ? ou y'a t'il une autre solution plus efficace ?

(En sachant qu'en réalité, sur les valeurs retournées il y a ensuite des modifications a apporter selon le cas ex: réduction de 5% si couple, etc..)

Merci !

-
Edité par Kuss 8 novembre 2019 à 11:34:00

Benzouye

8 novembre 2019 à 12:02:30

Bonjour,

Dans une base de données relationnelle, rien de tout cela n'est nécessaire, car il ne faut pas structurer les données ainsi ...

Tu dois organiser ta base autour de deux tables :

contrat ( id_contrat [pk], nom )
tarif ( age [pk], id_contrat [pk][fk], montant )

Si je reprend ton exemple, ces tables vont contenir :

Table contrat
id_contrat	nom
1	Contrat 1
2	Contrat 2
3	Contrat 3

Cette table correspond aux entêtes de tes colonnes 2, 3 et 4 (et plus si un jour tu as plus de contrats).

Table tarif
id_contrat	age	montant
1	60	xxx
2	60	xxx
3	60	xxx
1	70	83.60
2	70	97.20
3	70	xxxx
1	80	xxx
2	80	xxx
3	80	xxxx

Cette table reprend pour un contrat donné pour un age donné le montant du contrat.

Lorsque l'utilisateur saisi sa date de naissance, et la date de début, tu calcules l'age côté PHP comme tu le fais actuellement et tu fais une simple requête :

SELECT
    C.id_contrat,
    C.nom AS nom_contrat
    T.montant
FROM
    tarif T
        INNER JOIN contrat C
            ON T.id_contrat = C.id_contrat
WHERE age = 'age calculé'

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

8 novembre 2019 à 15:47:34

Salut, et merci pour ta réponse !

J'ai une erreur avec age en primary key, erreur doublon dès que j'ajoute le montant de mon contrat 2 pour un même age que mon contrat 1 déja enregistré.

Je dois mal faire un truc surement. Pas facile tout ça, je voyais ça plus simple héhé.

Voila ma requête :

INSERT INTO `tarif` (`id_contrat`, `age`, `montant`) VALUES ('1', '69', '80.60'), ('2', '69', '94.20')

Là j'essaye juste de mettre en place la structure que tu m'as montrer pour faire des tests.

Du coup j'ai juste fait

Table : contrat (ici j'ai indexé aussi id_contrat pour pouvoir le trouver en fk sur mon autre table)

id_contrat(pk) nom
1 contrat 1

2 contrat 2

et Table : tarif (et donc (je fais ça sur phpmyadmin) dans vue relationnelle j'associe ma colonne id_contrat à celle de la table contrat)

id_contrat(fk) age(pk) montant

Et donc quand je veux ajouter des valeurs pour tester j'ai un message duplicata.

-
Edité par Kuss 8 novembre 2019 à 15:49:47

Benzouye

8 novembre 2019 à 15:56:04

Dans la table tarif, il faut que la clé primaire soit composée des deux colonnes age et id_contrat. C'est d'ailleurs pour cela que tu as une erreur car en l'état la clé primaire est seulement sur la colonne age.

Benzouye a écrit:

tarif ( age [pk], id_contrat [pk][fk], montant )

Je ne sais pas comment faire cela dans PHPMyAdmin ... En SQL ce serait :

CREATE TABLE contrat (
    id_contrat INT UNSIGNED NOT NULL,
    nom VARCHAR(60) NOT NULL,
    PRIMARY KEY ( id_contrat ),
    UNIQUE ( nom )
) Engine=InnoDB;

CREATE TABLE tarif (
    age SMALLINT UNSIGNED NOT NULL,
    id_contrat INT UNSIGNED NOT NULL,
    PRIMARY KEY ( age, id_contrat ),
    CONSTRAINT fk_contrat FOREIGN KEY ( id_contrat) REFERENCES contrat ( id_contrat )
) Engine=InnoDB;

-
Edité par Benzouye 8 novembre 2019 à 15:57:24

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

8 novembre 2019 à 16:28:49

Merci bien ça fonctionne, en fait quand je sélectionnais age en primary key ça me l'enlevait de id_contrat, et inversement.

Avec ta requête c'est passé tout seul.

Merci beaucoup pour ton aide, je vais bidouiller la dessus ce week end

Kuss

14 novembre 2019 à 14:19:00

Salut, c'est encore moi !

Alors j'ai pu avancé correctement grâce à ton aide.

Mais maintenant, ça se corse et j'ai encore besoin d'un p'tit coup de main.

Donc il faudrait maintenant que le tarif affiché, en plus de tenir compte de l'age, tienne compte de la zone tarifaire concernée.

La france est, par exemple, divisée en 4 zones tarifaires : ZA, ZB, ZC et ZD. Chacune contenant certains départements.

Je pensais rajouter à ma table tarif une colonne zone :

id_contrat age montant zone

1 60 80.21€ ZA

1 60 85.21€ ZB

.... ... .... .....

Et lié à ma colonne zone, une table zonier :

zone dpt

ZA 10

ZB 14

ZA 20

... ...

Le truc c'est que : l'utilisateur va juste rentrer le département lors de son utilisation.

Donc faire un formulaire pour récupérer le département et ensuite une requête pour aller chercher la zone qui correspond :

$zone : SELECT zone FROM zonier WHERE dpt ='$dpt_saisi' ---> j'aurai donc un retour unique : ZA ou ZB ou ....

La ou je bloque c'est maintenant.

SELECT C.id_contrat, C.nom AS nom_contrat, T.montant FROM tarif T

INNER JOIN contrat C ON T.id_contrat = C.id_contrat

WHERE age ="'.$age1.'" AND zone = '$zone"

Ok, mais il y'a certainement des trucs a rajouter dans la requete ou des "joints" a mettre en place ?

J'avoue que sur les joints j'ai du mal

-
Edité par Kuss 14 novembre 2019 à 14:45:51

Benzouye

14 novembre 2019 à 16:16:25

Tu dois donc maintenant organiser ta base autour de 4 tables :

contrat ( id_contrat [pk], nom )
zone_tarif ( id_zone [pk], libelle )
departement ( id_departement [pk], id_zone [fk] )
tarif ( age [pk], id_zone [pk][fk], id_contrat [pk][fk], montant )

Ce qui donne en SQL :

CREATE TABLE contrat (
	id_contrat INT UNSIGNED NOT NULL,
	nom VARCHAR(60) NOT NULL,
	PRIMARY KEY ( id_contrat ),
	UNIQUE ( nom )
) Engine=InnoDB;

CREATE TABLE zone_tarif (
	id_zone SMALLINT UNSIGNED NOT NULL,
	libelle VARCHAR(10),
	PRIMARY KEY ( id_zone ),
	UNIQUE ( libelle )
)

CREATE TABLE departement (
	id_departement SMALLINT UNSIGNED NOT NULL,
	id_zone SMALLINT UNSIGNED NOT NULL,
	PRIMARY KEY ( id_departement ),
	CONSTRAINT fk_zone_dpt FOREIGN KEY ( id_zone ) REFERENCES zone_tarif ( id_zone )
) Engine=InnoDB;
 
CREATE TABLE tarif (
	id_contrat INT UNSIGNED NOT NULL,
	age SMALLINT UNSIGNED NOT NULL,
	id_zone SMALLINT UNSIGNED NOT NULL,
	PRIMARY KEY ( age, id_contrat, id_zone ),
	CONSTRAINT fk_contrat FOREIGN KEY ( id_contrat) REFERENCES contrat ( id_contrat ),
	CONSTRAINT fk_zone_tarif FOREIGN KEY ( id_zone ) REFERENCES zone_tarif ( id_zone )
) Engine=InnoDB;

Tu enregistres tes différentes zones dans la table zone_tarif.

Tu enregistres tes départements dans la table departement en précisant l'id de la zone associée.

Tu enregistres tes tarifs dans la même logique avec une clé primaire sur 3 colonnes qui empêchera de saisir plusieurs tarifs pour le même couple age/zone/contrat.

Pour retrouver le tarif en fonction de l'âge et du département :

SELECT
	C.id_contrat,
	C.nom AS nom_contrat,
	Z.libelle AS zone_tarif,
	T.montant
FROM
	tarif T
		INNER JOIN contrat C
			ON T.id_contrat = C.id_contrat
		INNER JOIN zone_tarif Z
			ON T.id_zone = Z.id_zone
		INNER JOIN departement D
			ON Z.id_zone = D.id_zone
WHERE
	age = 'age calculé'
	AND D.id_departement = 'département saisi'

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

14 novembre 2019 à 16:47:23

Wouaw ... Tu gères !

Je vais tester tout ça de suite ! J'étais pas vraiment sur la bonne voie en fait, moi qui croyais avoir choper la logique ^^.

Merci beaucoup, c'est génial !

Benzouye

14 novembre 2019 à 16:57:00

Kuss a écrit:

J'étais pas vraiment sur la bonne voie en fait

Bah si, en tout cas sur le modèle de données c'était bon, j'ai juste détaillé pour les zones comme ça c'est plus rigoureux et modulaire.

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

14 novembre 2019 à 17:05:15

Juste une petite chose qui me trouble.

Est ce que ceci ne va pas posé de problème :

Table : zone_tarif(id_zone[pk], libelle)

Dans la requete : Z.libelle AS zone_tarif ----- alias ayant le même nom que la table ça va pas faire une erreur pour la ligne de l'INNER JOIN ?

INNER JOIN zone_tarif Z

Avant de copier/coller bêtement j'essaye de comprendre un peu ce que tu as fais pour me débrouiller au maximum tout seul, mais sur ce point j'ai l'impression d'être dans Inception xD

Benzouye

14 novembre 2019 à 17:10:36

Je ne me suis pas posé la question, mais je ne pense pas. Les alias établis dans le SELECT doivent être interprétés comme des noms de colonne.

Au pire tu auras une erreur, mais cela m'étonnerait

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

19 novembre 2019 à 15:28:08

Coucou ! C'est re-encore-moi.

Juste une petite précision, pour calculer mon tarif pour un couple je duplique donc certaines étapes.

Mais pour l'affichage des résultats je bloque sur comment additionner mes deux cotisations (celle l'adhérent 1 et celle du 2).

Déja voici comment ça marche pour un seul adhérent (je vire tout ce qui est Html/CSS pour être plus clair ici):

<?php

// Calcul de l'age et vérification de l'existence des variables pour éviter l'erreur au premier chargement de la page
if (isset ($_POST ['datenais']) AND isset ($_POST ['dateeff']) AND isset ($_POST ['dpt']))
{$datenais = $_POST ['datenais'];
$dateeff = $_POST ['dateeff'];
$age1 = $dateeff - $datenais;
$dpt = $_POST ['dpt'];

// Requete : on cherche le tarif selon age, département et année d'effet
 $tarif1 = $bdd->query('
SELECT L.id_contrat, L.nom AS nom_contrat, Ta.montant, annee
FROM tarifa Ta
INNER JOIN listecontrat L ON Ta.id_contrat = L.id_contrat
INNER JOIN zonierglobal Zg ON Ta.id_zone = Zg.id_zone
INNER JOIN zoniera Za ON Zg.id_zone = Za.id_zone
WHERE age ="'.$age1.'" AND Za.id_departement ="'.$dpt.'" AND Ta.annee ="'.$dateeff.'"' );

?> 
<?php
//Affichage des résultats
while ($resultats1 = $tarif1->fetch())
{	
?>
<p><h3><?php echo $resultats1['nom_contrat']; ?></h3><?php echo $resultats1['montant']; ?>€</p>
<?php
}
$tarif1->closeCursor();}
?>

L'idée c'est de récupérer les tarifs pour $age2 par exemple. Et des les additionner au tarif trouvés pour $age1.

Y'a t'il un moyen d'avoir un truc du style : <?php echo $resultats1['montant']+$resultats2['montant']; ?> ?

Merci !

-
Edité par Kuss 19 novembre 2019 à 15:46:22

Benzouye

19 novembre 2019 à 15:48:04

Pour moi il faut exécuter deux fois la requête, une fois par adhérent, et une fois que tu as tes deux tarifs tu les additionnes côté PHP pour les afficher ...

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

19 novembre 2019 à 15:57:39

C'était mon idée, mais je bloque ici. A comment additionner des valeurs issues de 2 while($resultats= $tarif->fetch())

différents.

Benzouye

19 novembre 2019 à 16:46:46

Exemple avec un code plus propre (requête préparée) :

<?php
// On prépare la requête avec des variables ( précédées par : )
$requete = $bdd->prepare('
	SELECT Ta.montant
	FROM
		tarifa Ta
			INNER JOIN listecontrat L
				ON Ta.id_contrat = L.id_contrat
			INNER JOIN zonierglobal Zg
				ON Ta.id_zone = Zg.id_zone
			INNER JOIN zoniera Za
				ON Zg.id_zone = Za.id_zone
	WHERE
		age = :age
		AND Za.id_departement = :dpt
		AND Ta.annee = :dif'
);

// On exécute une première fois la requête avec les valeurs du premier adhérent
$requete->execute( array(
	':age' => $age1,
	':dpt' => $dpt1,
	':dif' => $dif1,
));
// On récupère le montant du premier adhérent
$montant1 = $requete->fetchColumn();

// On exécute une deuxième fois la requête avec les valeurs du deuxième adhérent
$requete->execute( array(
	':age' => $age2,
	':dpt' => $dpt2,
	':dif' => $dif2,
));
// On récupère le montant du deuxième adhérent
$montant2 = $requete->fetchColumn();

// On additionne les deux montants
$total = $montant1 + $montant2;

// On affiche le résultat
echo "<p>Total = ".$total." ( ".$montant1." + ".$montant2." )</p>";

-
Edité par Benzouye 20 novembre 2019 à 13:26:09

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

20 novembre 2019 à 10:16:27

Salut et merci pour ton aide !

Quand je fais ça je n'ai qu'un seul retour de tarif, celui de la formule 1. Je n'ai aucun retour pour les autres formules.

C'est bien plus compliqué que ce que j'imaginais à la base.

J'ai clairement pas le niveau. Mais j'ai appris pas mal de truc grâce à toi ! Merci.

Je pense mettre ça de coté, parce que cette étape c'est juste le début des ennuis après ça se complique encore un peu.

Faudrait que je trouve quelqu'un dispo sur Discord pour expliquer clairement comment le tarif doit se calculer, donc pour le moment je met en pause.

Merci encore, à plus !

Benzouye

20 novembre 2019 à 13:27:45

Kuss a écrit:

Quand je fais ça je n'ai qu'un seul retour de tarif, celui de la formule 1. Je n'ai aucun retour pour les autres formules.

C'est à dire ? La variable $montant2 est null ? Cela signifie qu'avec les valeurs passées dans le deuxième execute, aucun enregistrement en base ne correspond ... Si tu lances la requête directement dans PHPMyAdmin (ou dans la console) avec les valeurs prévues que se passe-t-il ?

Kuss a écrit:

C'est bien plus compliqué que ce que j'imaginais à la base

Je veux bien essayer de t'aider, que trouves-tu compliqué ? Où est-ce que tu bloques ?

J'ai édité mon message précédent pour commenter un peu le code, au cas où ...

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

20 novembre 2019 à 14:12:25

Re,

Non montant2 n'est pas null. Il correspond bien au montant lié à l'age que je teste. Mais seulement pour la Formule 1.

(Actuellement j'ai mis dans ma BDD les données pour 6 formules)

Quand je faisais ma requete précédente et que j'affichais les résultats avec :

while ($resultats1 = $tarif1->fetch())
{  
?>
<p><h3><?php echo $resultats1['nom_contrat']; ?></h3><?php echo $resultats1['montant']; ?>€</p>
<?php
}
$tarif1->closeCursor();}
?>

J'avais bien un retour du montant pour chaque formule (donc de F1 à F6).

Là j'ai seulement le retour pour la F1 (mais montant1 et montant2 sont bien corrects).

EDIT : Si tu veux je peux tout reprendre depuis le début :D. Bien t'expliquer ou je veux arriver. Y'aurai un moyen de t'envoyer des fichiers ? (Par exemple mon fichier excel actuel qui a toutes les fonctionnalités que je cherche). Ça m’embête de mettre un lien ici. Ou je peux mettre en google tableur partagé. Enfin dis moi !

-
Edité par Kuss 20 novembre 2019 à 14:19:12

Benzouye

20 novembre 2019 à 16:35:44

Kuss a écrit:

j'ai mis dans ma BDD les données pour 6 formules

Mais oui ... j'avais zappé ce point important ... La requête retourne plusieurs lignes ...

Là on va parler de PHP, cela ne remet pas en cause la partie SQL.

Ta requête :

SELECT
	L.id_contrat,
	L.nom AS nom_contrat,
	Ta.annee,
	Ta.montant
FROM
	tarifa Ta
		INNER JOIN listecontrat L
			ON Ta.id_contrat = L.id_contrat
		INNER JOIN zonierglobal Zg
			ON Ta.id_zone = Zg.id_zone
		INNER JOIN zoniera Za
			ON Zg.id_zone = Za.id_zone
WHERE
	age = :age
	AND Za.id_departement = :dpt
	AND Ta.annee = :dif

Te retourne une ligne par contrat correspondant à l'âge, à la zone et à l'année. 6 lignes actuellement (6 contrats).

Au lieu d'utiliser fetchColumn, qui ne récupère que le résultat de la première colonne de la première ligne, on va plutôt utiliser fetchAll pour récupérer toutes les lignes retournées dans un même tableau PHP.

<?php
// On prépare la requête avec des variables ( précédées par : )
$requete = $bdd->prepare('
    SELECT
		L.id_contrat,
		L.nom AS nom_contrat,
		Ta.annee,
		Ta.montant
    FROM
        tarifa Ta
            INNER JOIN listecontrat L
                ON Ta.id_contrat = L.id_contrat
            INNER JOIN zonierglobal Zg
                ON Ta.id_zone = Zg.id_zone
            INNER JOIN zoniera Za
                ON Zg.id_zone = Za.id_zone
    WHERE
        age = :age
        AND Za.id_departement = :dpt
        AND Ta.annee = :dif'
);
 
// On exécute une première fois la requête avec les valeurs du premier adhérent
$requete->execute( array(
    ':age' => $age1,
    ':dpt' => $dpt1,
    ':dif' => $dif1,
));
// On récupère les contrats du premier adhérent dans un tableau
$contrats1 = $requete->fetchAll();
 
// On exécute une deuxième fois la requête avec les valeurs du deuxième adhérent
$requete->execute( array(
    ':age' => $age2,
    ':dpt' => $dpt2,
    ':dif' => $dif2,
));
// On récupère les contrats du deuxième adhérent dans un tableau
$contrats2 = $requete->fetchAll();

// On affiche tous les résultats dans un tableau HTML :
?>
<table>
	<thead>
		<tr>
			<th>Adhérent 1</th>
			<th>Adhérent 2</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>
<?php
	// On boucle sur les contrats adhérent 1
	foreach( $contrats1 as $contrat1 ) {
?>
				<p><?php echo $contrat1['nom_contrat']; ?> = <?php echo $contrat1['montant']; ?></p>
<?php		
	}
?>
			</td>
			<td>
<?php
	// On boucle sur les contrats adhérent 2
	foreach( $contrats2 as $contrat2 ) {
?>
				<p><?php echo $contrat2['nom_contrat']; ?> = <?php echo $contrat2['montant']; ?></p>
<?php		
	}
?>
			</td>
		</tr>
	</tbody>
</table>

-
Edité par Benzouye 20 novembre 2019 à 16:36:42

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

21 novembre 2019 à 10:49:03

Salut et merci encore pour ton aide!

Alors avant d'attaquer je te montre à quoi ça ressemble aujourd'hui sur mon fichier Excel. Histoire de ne pas sauter des étapes :

Donc mon calcul de tarif doit tenir compte de :

l'age
la date d'effet du contrat
département
RO (régime obligatoire) (Réduction de 5% ou 10% par exemple si tel ou tel régime)
la composition familiale. (Réduction de 5% si couple, 10% si couple + enfant par exemple)

Une fois tous ces champs renseignés par l'utilisateur le calcul se fait et, comme tu le vois, j'ai le retour du tarif TOTAL pour chaque formule de chaque compagnie d'assurance tenant compte de tous les critères.

Là ou j'en suis actuellement (grâce à toi) coté PHP/MySQL, je peux obtenir le tarif pour CHAQUE ADHERENT en fonction de

l'age
la date d'effet
département.

Et avoir l'affichage des tarifs pour chaque formule.

Manque donc l'addition du tarif pour CHAQUE ADHERENT afin d'obtenir le tarif TOTAL, la composition (solo, couple, couple + enfant,...) et le RO (Salarié, agricole, non salarié).

On en est donc à l'étape de l'addition des tarifs pour CHAQUE ADHERENT. Avant d'aller plus loin dans cette étape, est ce qu'il ne vaut pas mieux introduire les notions de composition et régime avant ?

Je comprends bien que ça n'a rien à voir mais, sur Excel, pour introduire ces notions j'ai fais un "embriquement" de SI/SINON SI/OU/ET etc.

Par ex pour un couple, j'ai un truc du style :

SI age conjoint > 0 ALORS va chercher tarif adhérent et tarif conjoint, fait 5% de réduction sur le tarif adhérent+conjoint SINON va cherché tarif adhérent.

Mais vu que des fois il peut y avoir des enfants, cette ligne là et comprise dans un autre SI.

Du style : SI age enfant1 ET age conjoint >0 ALORS ............ SINON SI (et hop la on met la ligne que j'ai écrite ou dessus)........

Je t'explique tout ça parce que je me vois mal faire ce même "embriquement" de IF/ELSE IF/ELSE pour couvrir chaque composition familiale possible en PHP. Y'aura t'il une autre possibilité ?

-
Edité par Kuss 21 novembre 2019 à 10:52:01

Benzouye

21 novembre 2019 à 11:09:20

Encore une fois, il faut te séparer complètement de ta vision Excel pour modéliser ta base de données, ta vision Excel est d'ailleurs déjà mauvaise en fait

C'est vrai qu'avec ces tableaux, on voit bien qu'il manque des notions côté base de données.

Tu as plusieurs compagnies ... cette notion n'existe pas dans la base actuelle, chaque compagnie a plusieurs formules (F1, F2, etc.) et chaque formule à plusieurs niveaux de prestations.

Tu as la notion de régime. Par contre, on ne voit pas comment celle-ci est liée aux formules.

Il semblerait aussi qu'il y ait des ages limite, ce qui peut impliquer des bornes inférieures et supérieures pour les ages.

Il faut absolument fixer ces points avant même de réfléchir à coder en PHP. Ton modèle de données doit être opérationnel avant tout.

Peux-tu reprendre avec précision ce que saisi l'utilisateur, et comment sa saisie te permet de rechercher les formules/niveaux correspondant ?

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

21 novembre 2019 à 13:59:46

Alors :

L'utilisateur saisi :

date de naissance de l'adhérent (et autres bénéficiaires si présents)
la date d'effet du contrat (soit c'est une date pendant l'année en cours, soit n+1, jamais de n+2)
le département de résidence
le régime obligatoire

// Chaque compagnie à sa propre règle de calcul, certaines accordent une réduction de X% pour un couple, d'autres Y%, d'autres pas. Idem pour les réductions selon les régimes. D'autres considèrent l'age exact à la date d'effet du contrat pour le tarif (par exemple date de naissance : 01/05/1950 contrat démarre au 01/01/2020 : age 69 ans, et pour d'autres c'est en différence de millésime, naissance 01/05/1950, effet 01/01/2020 : age 70 ans.)

Voila, je te dis ça pour que tu vois bien l'ensemble des particularités.(Ma construction Excel n'est effectivement surement pas la meilleure mais au moins ça fonctionne, pour moi c'est le principal ^^) //

Les étapes dans l'ordre (je vais prendre la logique globale, sans parler des particularités des compagnies) : (les étapes que je vais décrire sont pour la plupart des guides pour ma fonction "rechercheV" - Prenons donc le cas suivant : 01/05/1950, Salarié, dpt : 75, 01/01/2020).

0 - L'age est déterminé, on part donc cherché le tarif qui correspond à cet age en fonction de nos critères.

1 - SI année = 2019 ALORS Recherche dans TARIF2019, SINON recherche dans TARIF2020

Comme expliqué plus tôt chaque compagnie découpe la France en différentes zones tarifaires (bien sur, sinon trop facile, chaque compagnie à ses propres zones). Je met donc en place des Listes qui contiennent mes Dpts : ZoneA(01,06,13,75,...) ZoneB(02,14,41,93,...) Etc.

2 - TARIF2020 (SI Dpt apartient à ZoneA ALORS cherche tarif dans colonne 1 SINON SI dpt appartient à ZoneB cherche dans colonne 5 SINON SI ...) jusqu'à couvrir chaque zone (souvent 4 à 6 zones par compagnie)

A ce moment là j'ai donc "en ma possession" la tarif de chaque formule de chaque compagnie (puisque les étapes sont faites pour chaque formule, il y donc l'index des colonnes qui change simplement par ex: colonne 1 à 4 - tarifs des formules 1 à 4 de ZoneA / colonne 5 à 8 - tarifs des formules 1 à 4 de ZoneB, etc)

2.5 - Ces tarifs sont donc stockés dans des tableaux provisoire sur d'autres pages.

3 - Maintenant selon si on a un couple/couple avec enfant/solo/ etc. j'additionne les différents tarifs pour chaque personnes et je fais la réduction qui va avec, pareil pour le régime.

4 - J'affiche les tarifs sur ma page et j'ajoute certains frais à mon tarif général (par exemple des frais de gestions mensuels que prend la compagnie).

Voila, normalement j'ai rien zappé !

EDIT : Pour les ages limites, en fait si l'utilisateur rentre une date de naissance qui nous donne un age hors limite pour cette formule. Et bien c'est pas grave. Puisqu'en cherchant l'age, il n'y aura aucun tarif associé donc aucun retour de tarif.

-
Edité par Kuss 21 novembre 2019 à 14:02:49

Benzouye

21 novembre 2019 à 17:13:54

Je vais essayer de reformuler tout cela comme je le comprends.

Tu as des compagnies, des départements, des zones, des formules.

Une compagnie défini plusieurs zones, et une zone est liée à une compagnie. Relation 1,n.

Un département est lié à plusieurs zones, et une zone est liée à plusieurs départements. Relation n,n.

Une formule est proposé par une compagnie, et une compagnie propose plusieurs formules. Relation 1,n.

Un tarif attribut un montant pour une formule, une zone, une année et un age.

Cela donnerait le modèle suivant :

departement ( id_departement, libelle )
compagnie ( id_compagnie, raison_sociale )
formule ( id_formule, libelle, id_compagnie )
zone ( id_zone, libelle, id_compagnie )
zonage ( id_departement, id_zone )
tarif ( id_formule, id_zone, annee, age, montant )

En toute rigueur il faudrait mettre un contrôle en place pour empêcher d'avoir le même département sur plusieurs zones pour la même compagnie (un TRIGGER BEFORE INSERT).

Avec ce modèle, si un utilisateur saisi sa date de naissance, la date de début du contrat et son département, tu peux retrouver le tarif correspondant.

Kuss a écrit:

je vais prendre la logique globale, sans parler des particularités des compagnies

La logique globale je l'avais je pense (sans parler des années et des compagnies que tu n'avais pas évoqué au départ ...). Dans un modèle relationnel, c'est les particularités qui vont être vraiment difficiles à gérer ... Et ici tu n'as pas décrit comment tu gérais ces cas.

J'ai l'impression qu'il faudrait encore multiplier les tarifs en fonction du régime obligatoire, et d'un type de famille (solo, couple, famille) ou du nombre d'adhérents ?

Pour gérer les réductions, comment fais-tu ? quels sont les critères ? Il manque encore quelques éléments ...

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

22 novembre 2019 à 9:48:54

Coucou !

Alors petite précision, pour gérer plus facilement les zones/Dpts je fais plusieurs Listes en réalité.

Par exemple pour la compagnie A, j'ai : Zone1A, Zone2A, etc. et la compagnie B : ZoneB1, ZoneB2,.....

Et donc dans ma formule excel, vu que je sais pour quelle compagnie je tape ma formule :D, j'appelle seulement les zones qui lui sont liées à cette compagnie.

Je pensais donc faire une table par zonier de compagnie, pour gérer ça plus facilement. Et aussi, faire une table tarif par compagnie. En reprenant la structure que tu m'as indiqué un peu plus tot. Ca marchait bien hein, je lançais une requete sur TarifcompagnieA et sortait les tarifs, ensuite un autre requete construite pareil sur TarifCompagnieB etc.! C'est juste pour gérer l'addition des tarifs de chaque bénéficiaires et la vérification de la présence d'un conjoint/enfant que je bloquais (et les éventuelles réduction). Et ça me permettrait d'appliquer un calcul particulier à telle ou telle compagnie sans avoir à gérer les conséquences sur le calcul des tarifs des autres compagnies. (Qu'en penses tu ? C'est pour ça que je n'ai pas dit qu'il y avait plusieurs compagnies. Je pensais juste dupliquer les étapes pour chaque compagnie, et pouvoir modifier la requete, le calcul pour telle ou telle compagnie au choix sans impacter les autres)

Pour gérer les réductions, je fais comme expliqué quelques posts plus haut :

Sur chaque page ou mes tarifs sont stockés, j'ai fait un petit tableau pour stockés les tarifs correspondants à mon age recherché avant de passer à l'addition.

J'ai donc un truc du style :

2020	F1	F2	F3	F4	F5	F6
Adh	73,47	84,81	99,73	112,81	135,15	167,19
cnjt	68,93	79,88	94,01	106,39	127,57	157,95
enfant	0,00	0	0	0	0	0
enfat2	0,00	0	0	0	0	0
Total 1	142,40	164,70	193,75	219,21	262,73	325,08
Total 2	128,16	148,23	174,38	197,28	236,45	292,57
TOTAL 3	125,13	144,20	169,04	190,80	228,01	281,32

Mon total 1 est juste l'addition des tarifs des bénéficiaires.

Mon total 2 vérifie si tarif cjt>0 (et donc l'existence d'un conjoint en fait) et applique 10% de réduction si VRAI. (je n'ai rien fait pour les enfants, cette compagnie fait 10% de réduction couple seulement)

Mon total 3 vérifie si l'age de l'adhérent principal est >75 si non il fait 5% de réduction et rajoute 3.38€ au tarif. (Voila une particularité propre à cette compagnie, le fait de souscrire un autre contrat à 3.38€ applique une réduction de 5% sur sa formule, mais il doit avoir moins de 75 ans)

Pour les réductions liées au régime obligatoire, cette compagnie là par exemple, n'en fait pas donc c'est plus facile :D.

Sinon pour celles qui en font je fais par exemple un Total 4 ou je vérifie si RO adhérent (ou conjoint) = TNS OU Exp. Agricole Si VRAI j'applique X% de réduction.

-
Edité par Kuss 22 novembre 2019 à 9:59:20

Benzouye

22 novembre 2019 à 11:04:46

Kuss a écrit:

Je pensais donc faire une table par zonier de compagnie, pour gérer ça plus facilement

Non, il ne faut pas ... Tu dénormalises les données et ce n'est pas bon. Cela n'est pas facilement maintenable et évolutif ... Chaque fois que tu vas ajouter/modifier/supprimer une compagnie tu vas devoir modifier la structure de ta base de données ... pas glop.

Kuss a écrit:

faire une table tarif par compagnie

Même remarque que précédemment, pas glop ... Il faut vraiment adopter le modèle proposé avec les clés étrangères, ce sera plus maintenable et évolutif.

Kuss a écrit:

applique 10% de réduction si VRAI. (je n'ai rien fait pour les enfants, cette compagnie fait 10% de réduction couple seulement)

Mon total 3 vérifie si l'age de l'adhérent principal est >75 si non il fait 5% de réduction et rajoute 3.38€ au tarif. (Voila une particularité propre à cette compagnie, le fait de souscrire un autre contrat à 3.38€ applique une réduction de 5% sur sa formule, mais il doit avoir moins de 75 ans)

Pour les réductions liées au régime obligatoire, cette compagnie là par exemple, n'en fait pas donc c'est plus facile :D.

Sinon pour celles qui en font je fais par exemple un Total 4 ou je vérifie si RO adhérent (ou conjoint) = TNS OU Exp. Agricole Si VRAI j'applique X% de réduction

C'est là le plus délicat dans l'histoire ...

Ici tu présentes 3 types de réduction : selon le régime, selon l'age et selon le type de famille.

Pour rester rigoureux, il faut imaginer une table par type de réduction :

departement ( id_departement, libelle )
compagnie ( id_compagnie, raison_sociale )
formule ( id_formule, libelle, id_compagnie )
zone ( id_zone, libelle, id_compagnie )
zonage ( id_departement, id_zone )
tarif ( id_formule, id_zone, annee, age, montant )
regime ( id_regime, libelle )
reduction_regime ( id_compagnie, id_regime, pourcentage )
reduction_age ( id_compagnie, age_mini, age_maxi, pourcentage )
reduction_groupe ( id_compagnie, type_groupe, pourcentage ) ici type groupe vaudra "conjoint" ou "enfant" ou "enfants" etc.

Pour chaque personne saisie dans le formulaire, tu récupères les tarifs :

SELECT
	C.id_compagnie,
	C.raison_sociale AS compagnie,
	T.annee,
	T.id_formule,
	F.libelle AS formule,
	T.id_zone,
	Z.libelle AS zone,
	T.age,
	T.montant
FROM
	tarif T
		INNER JOIN formule F
			ON T.id_formule = F.id_formule
		INNER JOIN compagnie C
			ON F.id_compagnie = C.id_compagnie
		INNER JOIN zone Z
			ON T.id_zone = Z.id_zone
			AND C.id_compagnie = Z.id_compagnie
		INNER JOIN zonage ZZ
			ON Z.id_zone = ZZ.id_zone
		INNER JOIN departement D
			ON ZZ.id_departement = D.id_departement
WHERE
	ZZ.id_departement = 'departement saisi'
	AND T.age = 'age calculé'
	AND T.annee = 'année souhaitée'

Donc entre 1 et 4 fois selon ce qui est saisi ...

Ensuite, en fonction de la saisi tu peux récupérer les réductions associées, pour chaque personne :

SELECT id_compagnie, pourcentage
FROM reduction_regime
WHERE id_regime = 'régime saisi'

SELECT id_compagnie, pourcentage
FROM reduction_age
WHERE 'age calculé' BETWEEN age_mini AND age_maxi

SELECT id_compagnie, pourcentage
FROM reduction_groupe
WHERE type_groupe = 'type saisi'

Ainsi tu te retrouves avec un tableau de tarifs par personne, et un tableau de réduction par type et par personne.

Là ton modèle de données est en place. Il faut maintenant structurer ton code PHP pour permettre de traiter ces données et d'afficher ce dont tu as besoin.

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

Kuss

22 novembre 2019 à 11:27:30

Ah !

Merci, je vais prendre un peu de temps pour mettre tout ça en place. Je viendrai débriefer ici !

Merci beaucoup pour ton temps !