373 lines
11 KiB
PHP
373 lines
11 KiB
PHP
<?php
|
|
|
|
/***
|
|
* Bilan des tickets de permanences entre 2 dates incluses
|
|
*/
|
|
|
|
error_reporting(E_ALL);
|
|
ini_set("display_errors", 1);
|
|
|
|
require_once "inc_fonc_bd_rv.php";
|
|
require_once "inc_fonc_dolibarr.php";
|
|
require_once "inc_Table.class.php";
|
|
require_once "inc_fonc_fonc.php";
|
|
setlocale(LC_ALL, 'fr_FR');
|
|
|
|
$msg ='';
|
|
|
|
//formulaire pour saisir la période concernée
|
|
?>
|
|
<span style="font-size: 2em">
|
|
<form>
|
|
<label for="birthday">Jour début :</label>
|
|
<input type="date" id="jourdeb" name="jourdeb" value="<?php echo date("Y-m-d"); ?>">
|
|
<br>
|
|
<label for="birthday">Jour fin :</label>
|
|
<input type="date" id="jourfin" name="jourfin" value="<?php echo date("Y-m-d"); ?>">
|
|
<input type="submit">
|
|
</form>
|
|
<br>
|
|
</span>
|
|
|
|
<?php
|
|
|
|
if (isset($_GET['jourdeb']) AND isset($_GET['jourfin'])) {
|
|
$jourdeb = $_GET['jourdeb'];
|
|
$jourfin = $_GET['jourfin'];
|
|
$conditions = " fa.datef BETWEEN CAST('$jourdeb' AS DATE) AND CAST('$jourfin' AS DATE) ";
|
|
echo ("<h2>Bilan Caisse Matériauthèque du ".date_fr($jourdeb). " au ". date_fr($jourfin)." </h2>");
|
|
|
|
} else {
|
|
exit("Absence de dates");
|
|
}
|
|
|
|
|
|
?>
|
|
<script src="my_gridSort.js"></script>
|
|
<!--
|
|
<p>
|
|
<a href="DB_requetes_programmees.php">Retour à la liste des requêtes</a>
|
|
</p>
|
|
-->
|
|
<?php
|
|
|
|
$sql2 = "SELECT fa.ref as facture, fa.datef as jour, fd.fk_product, prod.label as produit ,
|
|
prod.ref as prod_ref, fd.qty as poids, cat.rowid as idcat, cat.label as categorie, fd.total_ttc as prix
|
|
FROM `llx_facturedet` as fd
|
|
LEFT JOIN `llx_facture` as fa
|
|
ON fd.fk_facture = fa.rowid
|
|
LEFT JOIN llx_product as prod
|
|
ON prod.rowid = fd.fk_product
|
|
LEFT JOIN llx_categorie_product as catprod
|
|
ON fd.fk_product = catprod.fk_product
|
|
LEFT JOIN llx_categorie as cat
|
|
ON catprod.fk_categorie = cat.rowid
|
|
|
|
WHERE (fa.ref LIKE 'TK%' OR fa.ref LIKE 'AV%' ) AND $conditions
|
|
ORDER BY cat.label DESC ";
|
|
|
|
$tab = db_lire_lignes_dans_tableau($sql2);
|
|
|
|
$tab_cat = array(); // tableau des categories
|
|
//
|
|
$total_TTC = 0; // somme TTC des tickets émis
|
|
$total_KG = 0 ; // total du poids sorti
|
|
$nb_cotis_phy = $nb_cotis_mor = 0;
|
|
|
|
// pour chaque ligne de ticket TC
|
|
foreach ($tab as $ligne_de_facture) {
|
|
$cat = $ligne_de_facture['categorie'] ;
|
|
$prod_ref = $ligne_de_facture['prod_ref'] ;
|
|
if ($cat == "caisse_permanence") {
|
|
// println("ligne caisse permanence" . " --- poids : " . $ligne_de_facture['poids']. "--- prix : " . $ligne_de_facture['prix'] . 'fact :'. $ligne_de_facture['facture']);
|
|
continue;
|
|
}
|
|
|
|
if ($cat == "*prix.minimum") {
|
|
// println("*prix.minimum" . " --- poids : " . $ligne_de_facture['poids']. "--- prix : " . $ligne_de_facture['prix'] );
|
|
continue;
|
|
}
|
|
|
|
if ($cat == "*cotisations") {
|
|
if ($prod_ref == 'adhesion.mor') {
|
|
$nb_cotis_mor++;
|
|
} else if ($prod_ref == 'adhesion.phy') {
|
|
$nb_cotis_phy++;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
// la catégorie est-elle présente dans le tableau des catégories ?
|
|
if (!isset($tab_cat[$cat])) {
|
|
$tab_cat[$cat] = array();
|
|
$tab_cat[$cat]['poids'] = 0;
|
|
$tab_cat[$cat]['prix'] = 0;
|
|
$tab_cat[$cat]['idcat'] = $ligne_de_facture['idcat'];
|
|
}
|
|
$tab_cat[$cat]['poids'] += $ligne_de_facture['poids'];
|
|
$tab_cat[$cat]['prix'] += $ligne_de_facture['prix'];
|
|
|
|
$total_KG += $ligne_de_facture['poids'];
|
|
$total_TTC += $ligne_de_facture['prix'];
|
|
|
|
}
|
|
|
|
$t = new myTableGrid('id_table');
|
|
|
|
|
|
$headers = array("catégorie","poids", "prix");
|
|
$t->setTableWidth("40%");
|
|
$t->setHeaderSortType(0, 'string');
|
|
$t->setHeaderSortType(1, 'number');
|
|
$t->setColumnsStyle(1, "text-align:right;");
|
|
$t->setHeaderSortType(2, 'number');
|
|
$t->setColumnsStyle(2, "text-align:right;");
|
|
|
|
|
|
$t->setHeader($headers);
|
|
|
|
$totalg = 0;
|
|
$phymor = " (phy :$nb_cotis_phy, mor :$nb_cotis_mor)";
|
|
|
|
foreach ($tab_cat as $categorie => $soustab) {
|
|
|
|
$row=array();
|
|
if ($categorie == '*cotisations') {
|
|
$row[]= addLink_categorie_factures($jourdeb, $jourfin, $categorie, $soustab['idcat']) . $phymor;
|
|
} else {
|
|
$row[]= addLink_categorie_factures($jourdeb, $jourfin, $categorie, $soustab['idcat']);
|
|
}
|
|
$row[]=$soustab['poids'];
|
|
$row[]= to_euro($soustab['prix']);
|
|
$t->setRow($row);
|
|
}
|
|
$footers=array("TOTAL", $total_KG . " kg", to_euro(($total_TTC)));
|
|
$t->setFooter($footers);
|
|
$t->setCaption("Poids et prix par catégorie");
|
|
|
|
$html = $t->getTableHtml();
|
|
echo $html;
|
|
|
|
|
|
//Phrase pour le nombre de cotisations enregistrées @CC
|
|
$sql_cot="SELECT count(*) AS nb from llx_subscription
|
|
WHERE CAST(datec AS DATE) BETWEEN CAST('$jourdeb' AS DATE) AND CAST('$jourfin' AS DATE) ";
|
|
/* echo $sql_cot . '<br>'; */
|
|
$ligne=db_lire_ligne($sql_cot);
|
|
echo "<br><br>";
|
|
echo "<strong>Cotisations enregistrées pour la/les journée.s du ". date_fr($jourdeb) ." <b> au </b>" . date_fr($jourfin) . " = " .$ligne['nb'] . "</strong> " ;
|
|
/*echo "<br><br>";*/
|
|
?>
|
|
<a href='https://dolibarr.laressourcerieverte.com/htdocs/takepos/albert/DB_bilan_cotisations_adhesions.php'>Vérifier les paiements et enregistrements</a>
|
|
<?php
|
|
|
|
//Créer le tableau d'affichage @CC
|
|
$sql4 = "SELECT adh.rowid, lastname as nom, firstname as prenom, cot.datec as date_adh
|
|
FROM llx_adherent as adh
|
|
LEFT JOIN llx_subscription as cot
|
|
ON cot.fk_adherent = adh.rowid
|
|
WHERE CAST(cot.datec AS DATE) BETWEEN CAST('$jourdeb' AS DATE) AND CAST('$jourfin' AS DATE)
|
|
ORDER BY nom ASC";
|
|
|
|
$tab_cot = db_lire_lignes_dans_tableau($sql4);
|
|
$t4 = new myTableGrid('cotisation');
|
|
|
|
$headers = array("Nom","Prénom", "Date et heure" );
|
|
|
|
$t4->setTableWidth("20%");
|
|
$t4->setHeaderSortType(0, 'string');
|
|
$t4->setHeaderSortType(1, 'string');
|
|
$t4->setHeaderSortType(2, 'string');
|
|
$t4->setColumnsStyle(0, "text-align:left;");
|
|
$t4->setColumnsStyle(1, "text-align:left;");
|
|
$t4->setColumnsStyle(2, "text-align:center;");
|
|
|
|
$t4->setHeader($headers);
|
|
foreach ($tab_cot as $cot) {
|
|
$row=array();
|
|
$row[]= $cot['nom'];
|
|
$row[] = $cot['prenom'];
|
|
$row[]= $cot['date_adh'];
|
|
$t4->setRow($row);}
|
|
|
|
|
|
$t4->setCaption("Liste des adhérents dont la cotisation a été enregistrée dans leur fiche");
|
|
|
|
|
|
$footers = array("<strong>Total des enregistrements de cotisation", " ", ($ligne['nb']). "</strong>");
|
|
$t4->setFooter($footers);
|
|
$t4->setCaption("Total des cotisations enregistrées dans les fiches adhérents");
|
|
|
|
$html = $t4->getTableHtml();
|
|
echo $html;
|
|
echo "<br><br>";
|
|
echo "<br><br>";
|
|
|
|
// liste des factures
|
|
|
|
// tableau des modes de paiement
|
|
//mise à jour pour types de paiement basés sur le règlement enregistré 20092024
|
|
|
|
$sql3 = "SELECT pm.fk_paiement as mode_p, SUM(pm.amount) as total_p , llx_c_paiement.libelle
|
|
FROM `llx_paiement_facture` as pf
|
|
RIGHT JOIN llx_facture as fa
|
|
ON pf.fk_facture = fa.rowid
|
|
RIGHT JOIN llx_paiement as pm
|
|
on pm.rowid = pf.fk_paiement
|
|
RIGHT JOIN llx_c_paiement
|
|
ON pm.fk_paiement = llx_c_paiement.id
|
|
|
|
|
|
WHERE (fa.ref LIKE 'T%' OR fa.ref LIKE 'AV%' ) AND $conditions GROUP BY llx_c_paiement.id ";
|
|
|
|
$tab_mode_p = db_lire_lignes_dans_tableau($sql3);
|
|
|
|
$t2 = new myTableGrid('mode');
|
|
|
|
$headers = array("mode paiement","somme");
|
|
$t2->setTableWidth("40%");
|
|
$t2->setHeaderSortType(0, 'string');
|
|
$t2->setHeaderSortType(1, 'number');
|
|
$t2->setColumnsStyle(1, "text-align:right;");
|
|
|
|
$t2->setHeader($headers);
|
|
$total_TTC = 0; // somme TTC des tickets émis
|
|
foreach ($tab_mode_p as $mode) {
|
|
$row=array();
|
|
$row[]= $mode['libelle'];
|
|
$row[] = to_euro($mode['total_p']);
|
|
$t2->setRow($row);
|
|
$total_TTC += $mode['total_p'];
|
|
}
|
|
$footers = array("TOTAL", to_euro($total_TTC));
|
|
$t2->setFooter($footers);
|
|
$t2->setCaption("Modes de paiement et sommes encaissées");
|
|
|
|
$html = $t2->getTableHtml();
|
|
echo $html;
|
|
echo "<br><br>";
|
|
|
|
// liste des factures
|
|
$sql3 = "SELECT fa.rowid as id, fa.datef, fa.ref, pm.amount as total_p , llx_c_paiement.libelle, llx_societe.nom, pm.fk_paiement as mode_p
|
|
FROM `llx_facture` as fa
|
|
RIGHT JOIN llx_paiement_facture as pf
|
|
ON fa.rowid = pf.fk_facture
|
|
RIGHT JOIN llx_paiement as pm
|
|
on pm.rowid = pf.fk_paiement
|
|
RIGHT JOIN llx_c_paiement
|
|
ON pm.fk_paiement = llx_c_paiement.id
|
|
right JOIN llx_societe
|
|
ON fa.fk_soc = llx_societe.rowid
|
|
WHERE (fa.ref LIKE 'TK%' OR fa.ref LIKE 'AV%' ) AND $conditions "
|
|
. " ORDER BY fa.datef DESC ";
|
|
$tab_fact = db_lire_lignes_dans_tableau($sql3);
|
|
|
|
$t2 = new myTableGrid('fact');
|
|
|
|
$headers = array("facture", "adhérent", "mode paiement", "somme", "date");
|
|
$t2->setTableWidth("40%");
|
|
$t2->setHeaderSortType(0, 'string');
|
|
$t2->setHeaderSortType(1, 'string');
|
|
$t2->setHeaderSortType(2, 'string');
|
|
$t2->setHeaderSortType(3, 'number');
|
|
$t2->setColumnsStyle(3, "text-align:right;");
|
|
$t2->setHeaderSortType(4, 'string');
|
|
|
|
$t2->setHeader($headers);
|
|
$total_TTC = 0; // somme TTC des tickets émis
|
|
foreach ($tab_fact as $facture) {
|
|
$row=array();
|
|
$row[]= addLink_facture_client($facture['id'], $facture['ref']);
|
|
$row[]= $facture['nom'];
|
|
$row[]= $facture['libelle'];
|
|
$row[] = to_euro($facture['total_p']);
|
|
$row[]= date_fr($facture['datef']);
|
|
|
|
$t2->setRow($row);
|
|
$total_TTC += $facture['total_p'];
|
|
}
|
|
$footers = array("TOTAL", "-", "-", "-", to_euro($total_TTC), "-");
|
|
$t2->setFooter($footers);
|
|
$t2->setCaption("Liste des factures et sommes encaissées");
|
|
|
|
$html = $t2->getTableHtml();
|
|
echo $html;
|
|
echo "<br><br>";
|
|
|
|
|
|
// liste des factures pour ticket moyen
|
|
$sql3 = "SELECT SUM(fa.total_ttc)as tot_client, llx_societe.nom as nom_client, fa.datef as jour
|
|
FROM `llx_facture` as fa
|
|
LEFT JOIN llx_societe
|
|
ON fa.fk_soc = llx_societe.rowid
|
|
WHERE fa.ref LIKE 'TK%' AND $conditions "
|
|
. " GROUP BY fa.datef asc, llx_societe.nom ASC ";
|
|
|
|
//println($sql3);
|
|
|
|
|
|
$tab_fact = db_lire_lignes_dans_tableau($sql3);
|
|
if (count($tab_fact) == 0) exit("Pas de factures !!!");
|
|
$t2 = new myTableGrid('fact');
|
|
|
|
$headers = array("date", "Total vente", "nb achats", "ticket moyen");
|
|
$t2->setTableWidth("40%");
|
|
$t2->setHeaderSortType(0, 'string');
|
|
$t2->setHeaderSortType(1, 'number');
|
|
$t2->setHeaderSortType(2, 'number');
|
|
$t2->setHeaderSortType(3, 'number');
|
|
$t2->setColumnsStyle(1, "text-align:right;");
|
|
$t2->setColumnsStyle(2, "text-align:right;");
|
|
$t2->setColumnsStyle(3, "text-align:right;");
|
|
|
|
|
|
$t2->setHeader($headers);
|
|
$total_TTC = 0; // somme TTC des tiockets émis
|
|
$nb_clients = 0;
|
|
$total_jour = 0;
|
|
$nb_cli_jour = 0;
|
|
$date = "";
|
|
foreach ($tab_fact as $facture) {
|
|
if (date_fr($facture['jour']) <> $date) {
|
|
if($date == "") {
|
|
// on est au début
|
|
$date = date_fr($facture['jour']);
|
|
} else {
|
|
// on solde les totaux de la perm
|
|
$row=array();
|
|
$row[]= $date;
|
|
$row[] = to_euro($total_jour);
|
|
$row[] = $nb_cli_jour;
|
|
$row[] = to_euro($total_jour/$nb_cli_jour);
|
|
$t2->setRow($row);
|
|
|
|
$date = date_fr($facture['jour']);
|
|
$total_jour = 0;
|
|
$nb_cli_jour = 0;
|
|
$t2->setRowClass('');
|
|
}
|
|
}
|
|
|
|
$total_jour += $facture['tot_client'];
|
|
$nb_cli_jour++;
|
|
$total_TTC += $facture['tot_client'];
|
|
$nb_clients++;
|
|
}
|
|
// on traite dernière date
|
|
$row=array();
|
|
$row[]= $date;
|
|
$row[] = to_euro($total_jour);
|
|
$row[] = $nb_cli_jour;
|
|
$row[] = to_euro($total_jour/$nb_cli_jour);
|
|
$t2->setRow($row);
|
|
|
|
$footers = array("TOTAL", to_euro($total_TTC), $nb_clients, to_euro($total_TTC/$nb_clients));
|
|
$t2->setFooter($footers);
|
|
$t2->setCaption("Ticket moyen hors adhésion");
|
|
$html = $t2->getTableHtml();
|
|
echo $html;
|
|
|
|
|
|
|