Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- session_start();
- //error_reporting(0);
- include("constantes.php");
- function connexion(){
- try {
- $user = DBUSER;
- $passwd = DBPWD;
- $dbname = DBNAME;
- $host = SERVERNAME;
- $db = new PDO("pgsql:dbname=$dbname;host=$host", $user, $passwd);
- }
- catch ( PDOException $e ) {
- return false;
- }
- return $db;
- }
- function CoUtilisateur($db,$user,$pass){
- $exec = false;
- $result = false;
- try {
- $req = "SELECT * from utilisateur WHERE (utilisateur = ?) AND (pass = ?) AND (inactif = '0');";
- $sql = $db->prepare($req);
- $sql->bindParam(1, $user, PDO::PARAM_STR,500);
- $sql->bindParam(2, $pass, PDO::PARAM_STR,500);
- $exec = $sql->execute();
- $result = $sql->fetch(PDO::FETCH_ASSOC);
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $result;
- }
- function Visualisation($db,$table, $data){
- try {
- $req = null;
- $exec = false;
- $result = false;
- switch ($table) {
- case 'reference-bl':
- $req = " SELECT coalesce(COUNT(*)+1,1) as id, coalesce(configuration.code_atelier,'56') as code_atelier FROM bon_de_livraison as bl,configuration WHERE (configuration.id = ?) AND (YEAR(bl.date_expedition) = ?) GROUP BY configuration.code_atelier;";
- $sql = $db->prepare($req);
- $id = $_SESSION['id'];
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- $year = date("Y");
- $sql->bindParam(2, $year, PDO::PARAM_INT);
- break;
- case 'reference-facture':
- $req = " SELECT coalesce(max(factures.id)+1,1) as id, coalesce(configuration.code_atelier,'56') as code_atelier FROM bon_de_livraison as bl,factures WHERE configuration.id = ? GROUP BY configuration.code_atelier;";
- $sql = $db->prepare($req);
- $id = $_SESSION['id'];
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- break;
- case 'stock':
- $req = "SELECT id as N°, designation as Designation, quantite as Quantité, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Designation,Quantité' as colonnes, 'stock' as type FROM stock WHERE id > ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- break;
- case 'stock-all':
- $req = "SELECT id,designation FROM stock WHERE quantite > 0";
- $sql = $db->prepare($req);
- break;
- case 'aide':
- $req = "SELECT id as N°, titre as Titre, tag as Tag, contenu as Contenu, 'N°,Titre,Tag,Contenu' as colonnes,1 as Modifier, 1 as Supprimer,'aide' as type FROM aide WHERE id > ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- break;
- case 'intervention':
- if($data[1] == 0){
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention AS Type_intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_intervention' as colonnes, 'intervention' as type FROM interventions i1, clients c1, bon_de_livraison b1 WHERE (i1.id > ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison) AND (i1.archive = ?) ORDER BY i1.id ASC ";
- }else{
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention AS Type_intervention,0 as Visualiser, 0 as Modifier, 0 as Supprimer, 0 as Dissocier, 0 as Archiver, 1 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_intervention' as colonnes, 'intervention' as type FROM interventions i1, clients c1, bon_de_livraison b1 WHERE (i1.id > ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison) AND (i1.archive = ?) ORDER BY i1.id ASC ";
- }
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'client':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE id > ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- break;
- case 'all-client':
- $req = "SELECT id, nom from clients";
- $sql = $db->prepare($req);
- break;
- case 'bl':
- if($data[1] == 0){
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl as Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (bon.id > ?) AND (bon.archive = ?) ORDER BY bon.id ASC ";
- }else{
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl as Type_de_BL, 1 as Visualiser, 0 as Modifier, 0 as Supprimer, 0 as Dissocier, 0 as Archiver, 1 as Desarchiver , 1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (bon.id > ?) AND (bon.archive = ?) ORDER BY bon.id ASC ";
- }
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'prix-bl':
- $req = "SELECT coalesce(ROUND(SUM(produit.quantite*articles.prix),2),0) as PrixTotal from articles_tarification_laposte articles, produit_et_travaux produit WHERE (articles.id = produit.designation) AND (produit.reference_bon_de_livraison = ?);";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_STR);
- break;
- case 'bl-possible-intervention':
- $req = "SELECT * FROM bon_de_livraison WHERE reference = ? OR id = (SELECT id FROM bon_de_livraison WHERE bon_de_livraison.id NOT IN (SELECT bon_de_livraison FROM interventions))";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_STR);
- break;
- case 'info-bl':
- $req = "SELECT * FROM bon_de_livraison WHERE reference = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_STR);
- break;
- case 'prestation-bl':
- $req = "SELECT articles.id,produit.id as produit_et_travaux_id,articles.denomination,categorie_tarification_laposte.denomination as categorie, articles.prix,produit.quantite, ROUND(produit.quantite*articles.prix,2) as PrixTotalHT,produit.stock from articles_tarification_laposte articles, produit_et_travaux produit,categorie_tarification_laposte WHERE (categorie_tarification_laposte.id = articles.categorie_tarification) AND (articles.id = produit.designation) AND (produit.reference_bon_de_livraison = ?);";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_STR);
- break;
- case 'prestation-possible-bl':
- $req = "SELECT produit.id as id, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE produit.categorie_tarification = categorie.id ORDER BY categorie;";
- $sql= $db->query($req);
- break;
- case 'bl-facture':
- $req = "SELECT b.reference as Désignation, b.lieu_intervention as Lieu_intervention, ROUND(SUM(p.quantite*a.prix),2) as \"P.U_brut\", '' as \"%\", ROUND(SUM(p.quantite*a.prix),2) as \"P.U_net\", 1 as Quantité,ROUND(SUM(p.quantite*a.prix),2) as \"Montant_H.T\",5 as \"*\", 0 as Visualiser, 0 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 0 as Desarchiver ,'Désignation,Lieu_intervention,P.U_brut,%,P.U_net,Quantité,Montant_H.T,*' as colonnes, 'bl-facture' as type FROM bon_de_livraison as b, articles_tarification_laposte as a, produit_et_travaux as p, factures as f WHERE (b.reference = p.reference_bon_de_livraison) AND (p.designation = a.id) AND (b.facture = f.id) AND (f.reference = ?) AND (b.archive = 1) GROUP BY b.reference,b.lieu_intervention;";
- $sql = $db->prepare( $req );
- $sql->bindParam(1,$data[0],PDO::PARAM_STR);
- break;
- case 'bl-possible-facture':
- $req = "SELECT bon.id, bon.reference FROM bon_de_livraison bon WHERE (facture = 0) AND (archive = 1)";
- $sql= $db->prepare($req);
- break;
- case 'info-facture':
- $req = "SELECT * FROM factures WHERE reference = ?";
- $sql = $db->prepare( $req );
- $sql->bindParam(1,$data[0],PDO::PARAM_STR);
- break;
- case 'facture':
- if($data[1] == 0){
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.id > ?) AND (factures.archive = ?) ORDER BY factures.id ASC ";
- }else{
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 0 as Modifier, 0 as Supprimer, 1 as Dissocier, 0 as Archiver, 1 as Desarchiver, 1 as PDF,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.id > ?) AND (factures.archive = ?) ORDER BY factures.id ASC ";
- }
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'prix-facture':
- $req = "SELECT coalesce(ROUND(SUM(p.quantite*a.prix),2),0) as prix_avant_tva, 20 as tva, coalesce(ROUND(SUM(p.quantite*a.prix)*0.2,2),0) as prix_tva, coalesce(ROUND(SUM(p.quantite*a.prix)*1.2,2),0) as prix_total FROM bon_de_livraison as b, articles_tarification_laposte as a, produit_et_travaux as p, factures as f WHERE (b.reference = p.reference_bon_de_livraison) AND (p.designation = a.id) AND (b.facture = f.id) AND (f.reference = ?);";
- $sql = $db->prepare( $req );
- $sql->bindParam(1,$data,PDO::PARAM_STR);
- break;
- case 'facture-dernier-id':
- $req = "SELECT COUNT(*) FROM factures";
- $sql = $db->query( $req );
- break;
- case 'bl-facture-dissocier':
- $req = "SELECT bl.type_bl as Type_intervention,
- bl.reference as reference,
- bl.lieu_intervention,
- SUM(articles_tarification_laposte.prix*produit_et_travaux.quantite) as \"P.U_brut\",
- '' as \"%\" ,
- SUM(articles_tarification_laposte.prix*produit_et_travaux.quantite) as \"P.U_net\",
- 1 as Quantité,
- SUM(articles_tarification_laposte.prix*produit_et_travaux.quantite) as \"Montant_H.T\",
- 5 as \"*\",
- 0 as Visualiser,
- 0 as Modifier,
- 0 as Supprimer,
- 0 as Dissocier,
- 0 as Archiver,
- 0 as Desarchiver,
- 'Type_intervention,Reference,Lieu_intervention,P.U_brut,%,P.U_net,Quantité,Montant_H.T,*' as colonnes FROM
- public.articles_tarification_laposte,
- public.bon_de_livraison as bl,
- public.categorie_tarification_laposte,
- public.clients,
- public.produit_et_travaux
- WHERE
- produit_et_travaux.reference_bon_de_livraison = bl.reference AND
- produit_et_travaux.designation = articles_tarification_laposte.id AND
- bl.client = clients.id AND
- bl.reference IN (SELECT b.reference FROM bon_de_livraison as b,factures as f WHERE (b.facture = f.id) AND (f.reference = ?))
- GROUP BY bl.date_expedition, bl.reference,bl.lieu_intervention,bl.type_bl";
- //$req = "SEELCT ";
- $sql = $db->prepare( $req );
- $sql->bindParam(1,$data[0],PDO::PARAM_STR);
- break;
- case 'sauvegarde':
- $req = "SELECT id as N°, date_sauvegarde as Date_de_la_sauvegarde, save_struct as Sauvegarde_structurelle, save_data as Sauvegarde_des_données, save_full as Sauvegarde_complète, mise_a_jour as \"Mise_à_jour\", 0 as Visualiser, 0 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Date_de_la_sauvegarde,Sauvegarde_structurelle,Sauvegarde_des_données,Sauvegarde_complète,Mise_à_jour' as colonnes, 'sauvegarde' as type FROM sauvegarde WHERE id > ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- break;
- case 'categorie':
- $req = "SELECT id as N°, denomination as denomination, categorie_tarification_laposte.type as Classification, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Classification' as colonnes, 'categorie' as type FROM categorie_tarification_laposte WHERE id > ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- break;
- case 'all-categorie':
- $req = "SELECT id,denomination FROM categorie_tarification_laposte";
- $sql= $db->prepare($req);
- break;
- case 'config-redaction':
- $req = "SELECT * FROM configuration WHERE id = ?";
- $sql= $db->prepare($req);
- if(isset($_SESSION['id'])){
- $id = $_SESSION['id'];
- }else{
- $id = 1;
- }
- $sql->bindParam(1, $id,PDO::PARAM_INT);
- break;
- case 'info-user':
- $req = "SELECT utilisateur,nom,prenom FROM utilisateur WHERE id = ?";
- $sql= $db->prepare($req);
- $id = $_SESSION['id'];
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- break;
- case 'utilisateur':
- $req = "SELECT utilisateur,visualisation_des_stocks as \"Visualisation des stocks\", interventions as \"Interventions\", clients as \"Clients\", modification_client as \"Modifier les clients\", bon_de_livraison as \"Bon de livraison\", modification_bon_de_livraison as \"Modification des bons de livraison\", visualisation_bon_de_livraison as \"Visualisation des bons de livraisons\",factures as Factures,visualisation_facture as \"Visualisation des factures\",modification_facture as \"Modification des factures\", analyses as \"Visualisation des analyses\", configuration_stock as \"Configuration du stock\", configuration_prestations as \"Configuration des prestations\", configuration_categories as \"Configuration des Catégories\", option_redaction as \"Option de rédactions\", administration_des_comptes as \"Administration des comptes\", configuration_du_compte as \"Configuration du compte\",archives as \"Visualisation des archives\", sauvegarde as \"Administration des sauvegardes\",inactif as \"inactif\" FROM utilisateur WHERE (utilisateur != 'admin') AND (id > ?) ORDER BY utilisateur";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- break;
- case 'prestation':
- $req = "SELECT produit.id as N°, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Categorie,Prix' as colonnes, 'prestation' as type FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE (produit.categorie_tarification = categorie.id) AND (produit.id > ?) ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- //print_r($sql->errorInfo());
- //echo $table;
- //if($table != "prestation-bl"){
- $result = $sql->fetchAll(PDO::FETCH_ASSOC);
- /*}else{
- $result = $sql->fetchAll(PDO::FETCH_NUM);
- }*/
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $result;
- }
- function Modification($db, $table, $data){
- try {
- $req = null;
- $exec = false;
- switch ($table) {
- case 'stock':
- $req = "UPDATE stock SET designation = ?,quantite = ? WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_INT);
- $sql->bindParam(3, $data[0], PDO::PARAM_INT);
- break;
- case 'facture-information':
- $req = "UPDATE factures SET information = ? WHERE reference = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- break;
- case 'intervention':
- $req = "UPDATE bon_de_livraison SET type_bl = ? WHERE id = (SELECT bl.id FROM bon_de_livraison bl, interventions i1 WHERE (? = i1.id) AND (bl.id = i1.bon_de_livraison))";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[7], PDO::PARAM_STR);
- $sql->bindParam(2, $data[0], PDO::PARAM_INT);
- $sql->execute();
- $req = "UPDATE interventions SET reference = ?,date_intervention = ?,lieu = ?,client = ?,Intervenant = ?,bon_de_livraison = ?,type_intervention = ? WHERE (id = ?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_STR);
- $sql->bindParam(3, $data[3], PDO::PARAM_STR);
- $sql->bindParam(4, $data[4], PDO::PARAM_INT);
- $sql->bindParam(5, $data[5], PDO::PARAM_STR);
- $sql->bindParam(6, $data[6], PDO::PARAM_INT);
- $sql->bindParam(7, $data[7], PDO::PARAM_STR);
- $sql->bindParam(8, $data[0], PDO::PARAM_INT);
- break;
- case 'client':
- $req = "UPDATE clients SET nom = ?, complement = ?,responsable = ?,adresse = ?,code_postale = ?,ville = ?,telephone = ?, fax = ?,mail = ? WHERE (id = ?)";
- $sql= $db->prepare($req);
- //var_dump($data);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_STR);
- $sql->bindParam(3, $data[3], PDO::PARAM_STR);
- $sql->bindParam(4, $data[4], PDO::PARAM_INT);
- $sql->bindParam(5, $data[5], PDO::PARAM_STR);
- $sql->bindParam(6, $data[6], PDO::PARAM_INT);
- $sql->bindParam(7, $data[7], PDO::PARAM_STR);
- $sql->bindParam(8, $data[8], PDO::PARAM_STR);
- $sql->bindParam(9, $data[9], PDO::PARAM_STR);
- $sql->bindParam(10, $data[0], PDO::PARAM_INT);
- break;
- case 'bl':
- $req = "UPDATE interventions SET type_intervention = ? WHERE id = (SELECT i1.id FROM bon_de_livraison bl, interventions i1 WHERE (? = bl.id) AND (bl.id = i1.bon_de_livraison))";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[9], PDO::PARAM_STR);
- $sql->bindParam(2, $data[8], PDO::PARAM_INT);
- $sql->execute();
- $req = "UPDATE bon_de_livraison SET reference = ?,redacteur = ?,date_expedition = ?,date_facturation = ?, adresse_facturation = ?, lieu_intervention = ?, observation = ?, code_atelier = ?,type_bl = ? WHERE bon_de_livraison.id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- $sql->bindParam(6, $data[5], PDO::PARAM_STR);
- $sql->bindParam(7, $data[6], PDO::PARAM_STR);
- $sql->bindParam(8, $data[7], PDO::PARAM_STR);
- $sql->bindParam(9, $data[9], PDO::PARAM_STR);
- $sql->bindParam(10, $data[8], PDO::PARAM_INT);
- break;
- case 'categorie':
- $req = "UPDATE categorie_tarification_laposte SET denomination = ?, type = ? WHERE id = ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_STR);
- $sql->bindParam(3, $data[0], PDO::PARAM_INT);
- break;
- case 'prestation':
- $req = "UPDATE articles_tarification_laposte SET denomination = ?,categorie_tarification = ?,prix = ? WHERE (id = ?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_INT);
- $sql->bindParam(3, $data[3], PDO::PARAM_STR);
- $sql->bindParam(4, $data[0], PDO::PARAM_INT);
- break;
- case 'config-redaction':
- $req = "UPDATE configuration SET nom = ?, code_atelier = ?,rue = ?, bp = ?, code_postale = ?, ville = ?, cedex = ?, telephone = ?, fax = ?, email = ?, site_web = ?, code_ape = ?, code_urssaf = ?, num_intercommunautaire = ?, num_tva = ?, num_siret = ?, ordre_cheque = ?, IBAN = ?, BIC = ?, pied_de_page = ? WHERE id = ? ";
- $sql= $db->prepare($req);
- $id = $_SESSION['id'];
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- $sql->bindParam(6, $data[5], PDO::PARAM_STR);
- $sql->bindParam(7, $data[6], PDO::PARAM_STR);
- $sql->bindParam(8, $data[7], PDO::PARAM_STR);
- $sql->bindParam(9, $data[8], PDO::PARAM_STR);
- $sql->bindParam(10, $data[9], PDO::PARAM_STR);
- $sql->bindParam(11, $data[10], PDO::PARAM_STR);
- $sql->bindParam(12, $data[11], PDO::PARAM_STR);
- $sql->bindParam(13, $data[12], PDO::PARAM_STR);
- $sql->bindParam(14, $data[13], PDO::PARAM_STR);
- $sql->bindParam(15, $data[14], PDO::PARAM_STR);
- $sql->bindParam(16, $data[15], PDO::PARAM_STR);
- $sql->bindParam(17, $data[16], PDO::PARAM_STR);
- $sql->bindParam(18, $data[17], PDO::PARAM_STR);
- $sql->bindParam(19, $data[18], PDO::PARAM_INT);
- $sql->bindParam(20, $data[19], PDO::PARAM_INT);
- $sql->bindParam(21, $id, PDO::PARAM_INT);
- break;
- case 'config-permission':
- $req = "UPDATE utilisateur SET visualisation_des_stocks = ?, interventions = ?, clients = ?, modification_client = ?, bon_de_livraison = ?, modification_bon_de_livraison = ?, visualisation_bon_de_livraison = ?, factures = ?, visualisation_facture = ?, modification_facture = ?, analyses = ?, configuration_stock = ?,configuration_prestations = ?, configuration_categories = ?, option_redaction = ?, administration_des_comptes = ?, configuration_du_compte = ?,archives = ?,sauvegarde = ? ,inactif = ?,aide = 1 WHERE utilisateur = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- $sql->bindParam(6, $data[5], PDO::PARAM_STR);
- $sql->bindParam(7, $data[6], PDO::PARAM_STR);
- $sql->bindParam(8, $data[7], PDO::PARAM_STR);
- $sql->bindParam(9, $data[8], PDO::PARAM_STR);
- $sql->bindParam(10, $data[9], PDO::PARAM_STR);
- $sql->bindParam(11, $data[10], PDO::PARAM_STR);
- $sql->bindParam(12, $data[11], PDO::PARAM_STR);
- $sql->bindParam(13, $data[12], PDO::PARAM_STR);
- $sql->bindParam(14, $data[13], PDO::PARAM_STR);
- $sql->bindParam(15, $data[14], PDO::PARAM_STR);
- $sql->bindParam(16, $data[15], PDO::PARAM_STR);
- $sql->bindParam(17, $data[16], PDO::PARAM_STR);
- $sql->bindParam(18, $data[17], PDO::PARAM_STR);
- $sql->bindParam(19, $data[18], PDO::PARAM_STR);
- $sql->bindParam(20, $data[19], PDO::PARAM_STR);
- $sql->bindParam(21, $data[20], PDO::PARAM_STR);
- break;
- case 'bl-possible-facture':
- $req = "UPDATE bon_de_livraison SET facture = (SELECT id FROM factures WHERE reference = ?) WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- break;
- case 'mdp':
- $req = "UPDATE utilisateur SET pass = ? WHERE utilisateur = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- break;
- case 'quantite-produit-bl':
- // 0 - ID PRODUIT ET TRAVAUX
- // 1 - QUANTITE PRESTATION
- // 2 - QUANTITE PRECEDENTE PRESTATION
- // 3 - ID STOCK
- //var_dump($data);
- $req = "UPDATE stock SET quantite = ((SELECT quantite FROM stock WHERE id = (SELECT stock FROM produit_et_travaux WHERE id = ?))+?) WHERE id = (SELECT stock FROM produit_et_travaux WHERE id = ?)";
- $sql1 = $db->prepare($req);
- $sql1->bindParam(1,$data[0],PDO::PARAM_INT);
- $sql1->bindParam(2,$data[2],PDO::PARAM_INT);
- $sql1->bindParam(3,$data[0],PDO::PARAM_INT);
- $sql1->execute();
- //print_r($sql1->errorInfo());
- //echo "</br>";
- $req = "UPDATE stock SET quantite = (SELECT quantite FROM stock WHERE id = ?)-? WHERE id = ?";
- $sql2= $db->prepare($req);
- $sql2->bindParam(1,$data[3],PDO::PARAM_INT);
- $sql2->bindParam(2,$data[1],PDO::PARAM_INT);
- $sql2->bindParam(3,$data[3],PDO::PARAM_INT);
- $sql2->execute();
- //print_r($sql2->errorInfo());
- $req = "UPDATE produit_et_travaux SET quantite = ?,stock = ? WHERE produit_et_travaux.id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[1], PDO::PARAM_INT);
- $sql->bindParam(2, $data[3], PDO::PARAM_INT);
- $sql->bindParam(3, $data[0], PDO::PARAM_INT);
- break;
- case 'statut-utilisateur':
- $req = "UPDATE utilisateur SET inactif = ? WHERE utilisateur = ? ";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- break;
- case 'bl-facture':
- $req = "UPDATE bon_de_livraison SET facture = 0 WHERE reference = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_STR);
- break;
- case 'aide':
- $req = "UPDATE aide SET titre = ?,tag = ?, contenu = ? WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(4, $data[0], PDO::PARAM_STR);
- $sql->bindParam(1, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_STR);
- $sql->bindParam(3, $data[3], PDO::PARAM_STR);
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- /* var_dump($data);
- */// print_r($sql->errorInfo());
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $exec;
- }
- function Suppresion($db,$table, $data){
- try {
- $req = null;
- $exec = false;
- switch ($table) {
- case 'stock':
- $req = "DELETE FROM stock WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'intervention':
- $req = "UPDATE stock set quantite = stock.quantite+p.quantite FROM (SELECT quantite,stock FROM produit_et_travaux WHERE reference_bon_de_livraison = (SELECT bon_de_livraison.reference FROM interventions,bon_de_livraison WHERE interventions.id = ? AND interventions.bon_de_livraison = bon_de_livraison.id) ) AS p WHERE id = p.stock;";
- $sql = $db->prepare( $req );
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- $sql->execute();
- $req = "DELETE FROM bon_de_livraison WHERE bon_de_livraison.id = (SELECT interventions.bon_de_livraison FROM interventions WHERE interventions.id = ?)";
- $sql = $db->prepare( $req );
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- $exec = $sql->execute();
- if($exec){
- $req = "DELETE FROM interventions WHERE interventions.id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- }
- break;
- case 'client':
- $req = "DELETE FROM clients WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'bl':
- $req = "UPDATE stock set quantite = stock.quantite+p.quantite FROM (SELECT quantite,stock FROM produit_et_travaux WHERE reference_bon_de_livraison = (SELECT reference FROM bon_de_livraison WHERE id = ?)) AS p WHERE id = p.stock;";
- $sql = $db->prepare( $req );
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- $sql->execute();
- $req = "DELETE FROM bon_de_livraison WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'facture':
- $req = "UPDATE bon_de_livraison SET facture = 0 WHERE facture = ?";
- $sql = $db->prepare( $req );
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- $sql->execute();
- $req = "DELETE FROM factures WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'categorie':
- $req = "DELETE FROM categorie_tarification_laposte WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'prestation':
- $req = "DELETE FROM articles_tarification_laposte WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'aide':
- $req = "DELETE FROM aide WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- break;
- case 'produit-bl':
- $req = "UPDATE stock SET quantite = ((SELECT quantite FROM stock WHERE id = (SELECT stock FROM produit_et_travaux WHERE id = ?))+?) WHERE id = (SELECT stock FROM produit_et_travaux WHERE id = ?)";
- $sql1 = $db->prepare($req);
- $sql1->bindParam(1,$data[2],PDO::PARAM_INT);
- $sql1->bindParam(2,$data[3],PDO::PARAM_INT);
- $sql1->bindParam(3,$data[2],PDO::PARAM_INT);
- $sql1->execute();
- $req = "DELETE FROM produit_et_travaux WHERE (reference_bon_de_livraison = ?) AND (designation= ?)";
- $sql = $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'sauvegarde':
- $req ="SELECT * FROM sauvegarde WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- $res = $sql->execute();
- if($res){
- $infos = $sql->fetch(PDO::FETCH_NUM);
- $file_struct = $infos[2];
- $file_data = $infos[3];
- $file_full = $infos[4];
- $file_mise_a_jour = $infos[5];
- unlink ("/var/www/html/sauvegarde/file/".$file_struct);
- unlink ("/var/www/html/sauvegarde/file/".$file_data);
- unlink ("/var/www/html/sauvegarde/file/".$file_full);
- unlink ("/var/www/html/sauvegarde/file/".$file_mise_a_jour);
- $req = "DELETE FROM sauvegarde WHERE id = :id";
- $sql = $db->prepare( $req );
- $sql->bindParam(':id', $data, PDO::PARAM_INT);
- }else{
- $result = FALSE;
- }
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- $result = $sql->fetchAll(PDO::FETCH_ASSOC);
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $exec;
- }
- function Ajouter($db, $table, $data){
- try {
- $req = null;
- $exec = false;
- switch ($table) {
- case 'stock':
- $req = "INSERT INTO stock (designation, quantite) VALUES (?,?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'intervention':
- $req = "INSERT INTO interventions (reference,date_intervention, lieu, client, intervenant, bon_de_livraison,type_intervention) VALUES (?,?, ?, ?, ?,nextval('bon_de_livraison_id_seq')+1,?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_INT);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- $sql->bindParam(6, $data[5], PDO::PARAM_STR);
- $exec = $sql->execute();
- if($exec){
- $req = "INSERT INTO bon_de_livraison (reference, date_expedition, date_facturation, redacteur,client,lieu_intervention,adresse_facturation,code_atelier,type_bl) VALUES (?, ?,?, ?,?,?,(SELECT adresse FROM clients WHERE id = ?),(SELECT code_atelier FROM configuration WHERE id = ?),?)";
- $sql= $db->prepare($req);
- $redacteur = $_SESSION['nom']."-".$_SESSION['prenom'];
- $ReferenceBL = Visualisation($db,'reference-bl', null);
- print_r($ReferenceBL);
- $date = date('Y');
- $Ref = '0000'.$ReferenceBL['id'];
- $len = strlen($Ref);
- $Ref = $Ref[$len-4].$Ref[$len-3].$Ref[$len-2].$Ref[$len-1];
- $Ref = $ReferenceBL['code_atelier'].$date[2].$date[3].$Ref;
- echo $Ref;
- $sql->bindParam(1, $Ref, PDO::PARAM_STR);
- $sql->bindParam(2,$data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[1], PDO::PARAM_STR);
- $sql->bindParam(4, $redacteur, PDO::PARAM_STR);
- $sql->bindParam(5, $data[3], PDO::PARAM_INT);
- $sql->bindParam(6, $data[2], PDO::PARAM_STR);
- $sql->bindParam(7, $data[3], PDO::PARAM_INT);
- $sql->bindParam(8, $_SESSION['id'], PDO::PARAM_INT);
- $sql->bindParam(9, $data[5], PDO::PARAM_STR);
- $exec = $sql->execute();
- $req = null;
- if(!$exec){
- $req = "DELETE FROM interventions WHERE id = currval('interventions_id_seq')";
- $sql = $db->prepare($req);
- $exec = $sql->execute();
- if($exec){
- $req = null;
- $exec = false;
- }
- }
- }
- break;
- case 'client':
- $req = "INSERT INTO clients (nom, complement, responsable, adresse, code_postale, ville,telephone,fax,mail) VALUES (?, ?, ?, ?, ?,?,?,?,?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- $sql->bindParam(6, $data[5], PDO::PARAM_STR);
- $sql->bindParam(7, $data[6], PDO::PARAM_STR);
- $sql->bindParam(8, $data[7], PDO::PARAM_STR);
- $sql->bindParam(9, $data[8], PDO::PARAM_STR);
- break;
- case 'bl':
- $req = "INSERT INTO bon_de_livraison (reference, client,adresse_facturation,code_atelier,redacteur,type_bl) VALUES (?,?,(SELECT adresse FROM clients WHERE id = ?),(SELECT code_atelier FROM configuration WHERE id = ?),?,?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- $sql->bindParam(3, $data[1], PDO::PARAM_INT);
- $sql->bindParam(4, $_SESSION['id'], PDO::PARAM_INT);
- $redacteur = $_SESSION['nom']."-".$_SESSION['prenom'];
- $sql->bindParam(5, $redacteur, PDO::PARAM_STR);
- $sql->bindParam(6, $data[2], PDO::PARAM_STR);
- break;
- case 'facture':
- $req = "INSERT INTO factures (reference, client,date_debut, date_fin,date_creation,date_echeance,dissocier) VALUES (?, ?, ?, ?, ?,?,0)";
- $sql = $db->prepare($req);
- $date = date('Y-m-d');
- $date_echance = new DateTime();
- $interval = new DateInterval('P1M');
- $date_echance->add($interval);
- $date_eche = $date_echance->format('Y-m-d');
- $sql->bindParam(1, $data[0], PDO::PARAM_INT);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $sql->bindParam(5, $date, PDO::PARAM_STR);
- $sql->bindParam(6, $date_eche, PDO::PARAM_STR);
- $result = $sql->execute();
- $req = "UPDATE bon_de_livraison SET facture = (SELECT id FROM factures WHERE reference = ?) WHERE reference = (SELECT reference WHERE (date_expedition BETWEEN ? AND ?) AND (client = ?) AND (archive = 1) AND (facture = 0) AND (type_bl = ?) )";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(4, $data[1], PDO::PARAM_STR);
- $sql->bindParam(2, $data[2], PDO::PARAM_STR);
- $sql->bindParam(3, $data[3], PDO::PARAM_INT);
- $sql->bindParam(5, $data[4], PDO::PARAM_STR);
- break;
- case 'categorie':
- $req = "INSERT INTO categorie_tarification_laposte (denomination, type) VALUES (?, ?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- break;
- case 'prestation':
- $req = "INSERT INTO articles_tarification_laposte (denomination, categorie_tarification, prix) VALUES (?, ?, ?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- break;
- case 'aide':
- $req = "INSERT INTO aide (titre, tag, contenu) VALUES (?, ?, ?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- break;
- case 'utilisateur':
- $req = "INSERT INTO utilisateur (utilisateur, pass,nom,prenom) VALUES (?,?,?,?)";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_STR);
- $sql->bindParam(3, $data[2], PDO::PARAM_STR);
- $sql->bindParam(4, $data[3], PDO::PARAM_STR);
- $exec = $sql->execute();
- if($exec){
- $req = "SELECT * FROM (SELECT (SELECT utilisateur.id FROM utilisateur WHERE utilisateur.utilisateur = ?),configuration.nom,configuration.rue,configuration.bp,configuration.code_postale,configuration.ville,configuration.cedex,configuration.telephone,configuration.fax,configuration.email,configuration.site_web,configuration.code_ape,configuration.code_urssaf, configuration.num_intercommunautaire, configuration.num_tva,configuration.num_siret, configuration.ordre_cheque, configuration.iban,configuration.bic,configuration.pied_de_page FROM configuration,utilisateur WHERE utilisateur.utilisateur = 'admin') AS INFO;";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $exec = $sql->execute();
- $result = $sql->fetch(PDO::FETCH_NUM);
- $req = "INSERT INTO configuration (id,nom,rue,bp,code_postale,ville,cedex,telephone, fax,email,site_web,code_ape,code_urssaf, num_intercommunautaire, num_tva,num_siret, ordre_cheque, iban, bic, pied_de_page) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $result[0], PDO::PARAM_STR);
- $sql->bindParam(2, $result[1], PDO::PARAM_STR);
- $sql->bindParam(3, $result[2], PDO::PARAM_STR);
- $sql->bindParam(4, $result[3], PDO::PARAM_STR);
- $sql->bindParam(5, $result[4], PDO::PARAM_STR);
- $sql->bindParam(6, $result[5], PDO::PARAM_STR);
- $sql->bindParam(7, $result[6], PDO::PARAM_STR);
- $sql->bindParam(8, $result[7], PDO::PARAM_STR);
- $sql->bindParam(9, $result[8], PDO::PARAM_STR);
- $sql->bindParam(10, $result[9], PDO::PARAM_STR);
- $sql->bindParam(11, $result[10], PDO::PARAM_STR);
- $sql->bindParam(12, $result[11], PDO::PARAM_STR);
- $sql->bindParam(13, $result[12], PDO::PARAM_STR);
- $sql->bindParam(14, $result[13], PDO::PARAM_STR);
- $sql->bindParam(15, $result[14], PDO::PARAM_STR);
- $sql->bindParam(16, $result[15], PDO::PARAM_STR);
- $sql->bindParam(17, $result[16], PDO::PARAM_STR);
- $sql->bindParam(18, $result[17], PDO::PARAM_STR);
- $sql->bindParam(19, $result[18], PDO::PARAM_STR);
- $sql->bindParam(20, $result[19], PDO::PARAM_INT);
- }
- break;
- case 'produit-bl':
- $req = "INSERT INTO produit_et_travaux (reference_bon_de_livraison, designation) VALUES (?, ?);";
- $sql = $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'bl-facture':
- $req = "UPDATE bon_de_livraison SET facture = (SELECT id FROM factures WHERE reference = ?) WHERE id = ?;";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data[0], PDO::PARAM_STR);
- $sql->bindParam(2, $data[1], PDO::PARAM_INT);
- break;
- case 'sauvegarde':
- $date = date('Y-m-d');
- $date_file = date('Y_m_d_H_i_s');
- $file_struct = "SAUVEGARDE_STRUCTURE_".$date_file.'.sql';
- $file_data = "SAUVEGARDE_DATA_".$date_file.'.sql';
- $file_full = "SAUVEGARDE_COMPLETE_".$date_file.'.sql';
- $file_update = "MISE_A_JOUR_".$date_file.".sql";
- $req = "INSERT INTO sauvegarde (date_sauvegarde,save_struct,save_data,save_full,mise_a_jour) VALUES (?,?,?,?,?)";
- $sql = $db->prepare( $req );
- $sql->bindParam(1, $date,PDO::PARAM_STR);
- $sql->bindParam(2, $file_struct,PDO::PARAM_STR);
- $sql->bindParam(3, $file_data,PDO::PARAM_STR);
- $sql->bindParam(4, $file_full,PDO::PARAM_STR);
- $sql->bindParam(5, $file_update,PDO::PARAM_STR);
- $result = $sql->execute();
- $connection = ssh2_connect($_SERVER['SERVER_ADDR'], 22);
- if($connection && $result){
- $user = ssh2_auth_password($connection, 'root', 'esat2017');
- if($user){
- FichierMiseAJour();
- $output1 = ssh2_exec($connection,'pg_dump -U root --schema-only facture > /var/www/html/sauvegarde/file/SAUVEGARDE_STRUCTURE_'.$date_file.'.sql');
- $output2 = ssh2_exec($connection,'pg_dump -U root facture > /var/www/html/sauvegarde/file/SAUVEGARDE_COMPLETE_'.$date_file.'.sql');
- $output3 = ssh2_exec($connection,'pg_dump -U root --column-inserts --attribute-inserts --data-only --exclude-schema=schema facture > /var/www/html/sauvegarde/file/SAUVEGARDE_DATA_'.$date_file.'.sql');
- $output4 = ssh2_exec($connection,'exit');
- if($output1 && $output2 && $output3 && $output4){
- $req = null;
- $exec = true;
- }
- }
- }
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- //print_r($sql->errorInfo());
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $exec;
- }
- function Archiver($db,$table, $data){
- try {
- $req = null;
- $exec = false;
- $id = $data;
- switch ($table) {
- case 'intervention':
- $req = "UPDATE bon_de_livraison SET archive = 1 WHERE id = (SELECT bl.id FROM bon_de_livraison bl, interventions i1 WHERE (? = i1.id) AND (bl.id = i1.bon_de_livraison))";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- $sql->execute();
- $req = "UPDATE interventions SET archive = 1 WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- break;
- case 'bl':
- $req = "UPDATE interventions SET archive = 1 WHERE id = (SELECT i1.id FROM bon_de_livraison bl, interventions i1 WHERE (? = bl.id) AND (bl.id = i1.bon_de_livraison))";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- $sql->execute();
- $req = "UPDATE bon_de_livraison SET archive = 1 WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- break;
- case 'facture':
- $req = "UPDATE factures SET archive = 1 WHERE id = ?";
- $sql= $db->prepare($req);
- $sql->bindParam(1, $id, PDO::PARAM_INT);
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $exec;
- }
- function Desarchiver($db,$type,$data){
- try {
- $req = null;
- $exec = false;
- switch ($type) {
- case 'intervention':
- $req = "UPDATE interventions SET archive = 0 WHERE id = ?";
- break;
- case 'bl':
- $req = "UPDATE bon_de_livraison SET archive = 0 WHERE id = ?";
- break;
- case 'facture':
- $req = "UPDATE factures SET archive = 0 WHERE id = ?";
- break;
- default:
- break;
- }
- if($req != null){
- $sql= $db->prepare($req);
- $sql->bindParam(1, $data, PDO::PARAM_INT);
- $exec = $sql->execute();
- $result = $sql->fetchAll(PDO::FETCH_NUM);
- }
- }
- catch ( PDOException $e ) {
- return false;
- }
- if ( !$exec ) {
- return false;
- }
- return $exec;
- }
- function Rechercher($db, $type, $data,$archive){
- try{
- $req = null;
- $select = $data[0];
- $value = $data[1];
- switch ($type) {
- case 'stock':
- switch ($select) {
- case 'N°':
- $req = "SELECT id as N°, designation as Designation, quantite as Quantité, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Designation,Quantité' as colonnes, 'stock' as type FROM stock WHERE id = ?;";
- break;
- case 'Designation':
- $req = "SELECT id as N°, designation as Designation, quantite as Quantité, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Designation,Quantité' as colonnes, 'stock' as type FROM stock WHERE (REPLACE(UPPER(designation),' ','') LIKE ?) ";
- break;
- case 'Quantité':
- $req = "SELECT id as N°, designation as Designation, quantite as Quantité, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Designation,Quantité' as colonnes, 'stock' as type FROM stock WHERE quantite = ?;";
- break;
- }
- break;
- case 'intervention':
- switch($select){
- case 'N°':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (i1.id = ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (i1.bon_de_livraison = b1.id);";
- break;
- case 'Référence':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(i1.reference),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- break;
- case 'Date_Intervention':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (i1.date_intervention = ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- # code...
- break;
- case 'Client':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(c1.nom),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- # code...
- break;
- case 'Lieu':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(i1.lieu),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- # code...
- break;
- case 'Reference_BL':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(b1.reference),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- break;
- case 'Intervenant':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(i1.intervenant),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- # code...
- case 'Type_intervention':
- $req = "SELECT i1.id as N°, i1.reference as Référence, i1.date_intervention as Date_Intervention, i1.lieu as Lieu, c1.nom as Client, i1.intervenant as Intervenant, b1.reference as Reference_BL,type_intervention as Type_Intervention, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Date_Intervention,Lieu,Client,Intervenant,Reference_BL,Type_Intervention' as colonnes, 'intervention' as type FROM interventions i1,clients c1,bon_de_livraison b1 WHERE (REPLACE(UPPER(i1.type_intervention),' ','') LIKE ?) AND (i1.archive = ?) AND (i1.client = c1.id) AND (b1.id = i1.bon_de_livraison);";
- break;
- }
- break;
- case 'client':
- switch ($select) {
- case 'N°':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE id = ?";
- # code...
- break;
- case 'Nom':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(nom),' ','') LIKE ?)";
- # code...
- break;
- case 'Complément':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(complement),' ','') LIKE ?)";
- # code...
- break;
- case 'Responsable':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(responsable),' ','') LIKE ?)";
- # code...
- break;
- case 'Adresse':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(adresse),' ','') LIKE ?)";
- # code...
- break;
- case 'Code_Postale':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(code_postale),' ','') LIKE ?)";
- # code...
- break;
- case 'Ville':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(ville),' ','') LIKE ?)";
- # code...
- break;
- case 'Telephone':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(telephone),' ','') LIKE ?)";
- # code...
- break;
- case 'Fax':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(fax),' ','') LIKE ?)";
- # code...
- break;
- case 'Mail':
- $req = "SELECT id as N°, nom as Nom, complement as Complément, responsable as Responsable, adresse as Adresse, code_postale as Code_Postale, ville as Ville, telephone as Telephone, fax as Fax, mail as Mail, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Nom,Complément,Responsable,Adresse,Code_Postale,Ville,Telephone,Fax,Mail' as colonnes, 'client' as type FROM clients WHERE (REPLACE(UPPER(mail),' ','') LIKE ?)";
- # code...
- break;
- default:
- # code...
- break;
- }
- break;
- case 'bl':
- switch($select){
- case 'N°':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (bon.id = ?) AND (bon.archive = ?)";
- break;
- case 'Référence':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur, type_bl AS Type_de_BL,1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(bon.reference),' ','') LIKE ?) AND (bon.archive = ?)";
- break;
- case 'Client':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(cli.nom),' ','') LIKE ?) AND (bon.archive = ?)";
- break;
- case 'Date_expedition':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (bon.date_expedition = ?) AND (bon.archive = ?)";
- break;
- case 'Date_de_facturation':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (bon.date_facturation = ?) AND (bon.archive = ?)";
- break;
- case 'adresse_Facturation':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(bon.adresse_facturation),' ','') = ?) AND (bon.archive = ?)";
- break;
- case 'Redacteur':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(bon.redacteur),' ','') LIKE ?) AND (bon.archive = ?)";
- break;
- case 'Lieu Intervention':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver ,1 as PDF, 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(bon.lieu_intervention),' ','') LIKE ?) AND (bon.archive = ?)";
- # code...
- break;
- case 'Type_de_BL':
- $req = "SELECT bon.id as N°, bon.reference as Référence,cli.nom as Client,bon.date_expedition as Date_expedition,bon.date_facturation as Date_de_facturation,bon.adresse_facturation as Adresse_Facturation, bon.redacteur as Redacteur,type_bl AS Type_de_BL, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 1 as Archiver, 0 as Desarchiver , 'N°,Référence,Client,Date_expedition,Date_de_facturation,Adresse_Facturation,Redacteur,Type_de_BL' as colonnes, 'bl' as type FROM bon_de_livraison bon, clients cli WHERE (bon.client = cli.id) AND (REPLACE(UPPER(bon.type_bl),' ','') LIKE ?) AND (bon.archive = ?)";
- #code...
- break;
- }
- break;
- case 'facture':
- switch($select){
- case 'N°':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.id = ?) AND (factures.archive = ?)";
- break;
- case 'Référence':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND ((REPLACE(UPPER(factures.reference),' ','')) LIKE ?) AND (factures.archive = ?)";
- break;
- case 'Client':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND ((REPLACE(UPPER(clients.nom),' ','')) LIKE ?) AND (factures.archive = ?)";
- break;
- case 'Debut_Facturation':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.date_debut = ?) AND (factures.archive = ?)";
- # code...
- break;
- case 'Fin_Facturation':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver, 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.date_fin = ?) AND (factures.archive = ?)";
- # code...
- break;
- case 'Date_Echeance':
- $req = "SELECT factures.id as N°, factures.reference as Référence, clients.nom as Client, factures.date_debut as Debut_Facturation,factures.date_fin as Fin_Facturation, factures.date_echeance as Date_Echeance, 1 as Visualiser, 1 as Modifier, 1 as Supprimer, 1 as Dissocier, 1 as Archiver, 0 as Desarchiver , 1 as PDF ,'N°,Référence,Client,Debut_Facturation,Fin_Facturation,Date_Echeance' as colonnes, 'facture' as type from factures, clients WHERE (factures.paye = 0) AND (clients.id = factures.client) AND (factures.date_echeance = ?) AND (factures.archive = ?)";
- break;
- }
- break;
- case 'prestation':
- switch ($select) {
- case 'N°':
- $req = "SELECT produit.id as N°, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Categorie,Prix' as colonnes, 'prestation' as type FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE (produit.categorie_tarification = categorie.id) AND (produit.id = ?)";
- # code...
- break;
- case 'Denomination':
- $req = "SELECT produit.id as N°, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Categorie,Prix' as colonnes, 'prestation' as type FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE (produit.categorie_tarification = categorie.id) AND ((REPLACE(UPPER(produit.denomination),' ','')) LIKE ?)";
- # code...
- break;
- case 'Categorie':
- $req = "SELECT produit.id as N°, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Categorie,Prix' as colonnes, 'prestation' as type FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE (produit.categorie_tarification = categorie.id) AND ((REPLACE(UPPER(categorie.denomination),' ','')) LIKE ?)";
- # code...
- break;
- case 'Prix':
- $req = "SELECT produit.id as N°, produit.denomination as denomination, categorie.denomination as categorie, produit.prix as prix, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'N°,Denomination,Categorie,Prix' as colonnes, 'prestation' as type FROM articles_tarification_laposte produit, categorie_tarification_laposte categorie WHERE (produit.categorie_tarification = categorie.id) AND (produit.prix = ?)";
- # code...
- break;
- default:
- # code...
- break;
- }
- # code...
- break;
- case 'categorie':
- switch ($select) {
- case 'N°':
- $req = "SELECT id as N°, denomination as denomination, categorie_tarification_laposte.type as Classification, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'categorie' as type,'N°,Denomination,Classification' as colonnes FROM categorie_tarification_laposte WHERE id = ?";
- # code...
- break;
- case 'Denomination':
- $req = "SELECT id as N°, denomination as denomination, categorie_tarification_laposte.type as Classification, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'categorie' as type, 'N°,Denomination,Classification' as colonnes FROM categorie_tarification_laposte WHERE ((REPLACE(UPPER(denomination),' ','')) LIKE ?)";
- # code...
- break;
- case 'Type':
- $req = "SELECT id as N°, denomination as denomination, categorie_tarification_laposte.type as Classification, 0 as Visualiser, 1 as Modifier, 1 as Supprimer, 0 as Dissocier, 0 as Archiver, 'categorie' as type, 'N°,Denomination,Classification' as colonnes FROM categorie_tarification_laposte WHERE ((REPLACE(UPPER(type),' ','')) LIKE ?)";
- # code...
- break;
- default:
- # code...
- break;
- }
- # code...
- break;
- case 'aide':
- switch ($select) {
- case 'N°':
- $req = "SELECT id as N°, titre as Titre, tag as Tag, contenu as Contenu, 'N°,Titre,Tag,Contenu' as colonnes,1 as Modifier,1 as Supprimer,'aide' as type FROM aide WHERE id = ?";
- break;
- case 'Titre':
- $req = "SELECT id as N°, titre as Titre, tag as Tag, contenu as Contenu, 'N°,Titre,Tag,Contenu' as colonnes,1 as Modifier,1 as Supprimer,'aide' as type FROM aide WHERE ((REPLACE(UPPER(titre),' ','')) LIKE ?)";
- break;
- case 'Tag':
- $req = "SELECT id as N°, titre as Titre, tag as Tag, contenu as Contenu, 'N°,Titre,Tag,Contenu' as colonnes,1 as Modifier,1 as Supprimer,'aide' as type FROM aide WHERE ((REPLACE(UPPER(tag),' ','')) LIKE ?)";
- break;
- case 'Contenu':
- $req = "SELECT id as N°, titre as Titre, tag as Tag, contenu as Contenu, 'N°,Titre,Tag,Contenu' as colonnes,1 as Modifier,1 as Supprimer,'aide' as type FROM aide WHERE ((REPLACE(UPPER(contenu),' ','')) LIKE ?)";
- break;
- }
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $sql = $db->prepare($req);
- if( ($select != "N°") && ($select != "Quantité") && ($select != "Prix") ){
- $value = str_replace(" ","",mb_strtoupper('%'.$value.'%'));
- $sql->bindParam(1, $value, PDO::PARAM_STR);
- }else{
- $sql->bindParam(1, $value, PDO::PARAM_INT);
- }
- if( ($type == "bl") || ($type == "facture") || ($type == "intervention") ){
- $sql->bindParam(2, $archive, PDO::PARAM_INT);
- }
- $exec = $sql->execute();
- $result = $sql->fetchAll(PDO::FETCH_ASSOC);
- }else{
- $exec = false;
- }
- }catch ( PDOException $e ) {
- return false;
- }
- if ( !$exec ) {
- return false;
- }
- return $result;
- }
- function Analyse($db,$table,$data){
- try {
- $req = null;
- $exec = false;
- switch ($table) {
- case 'Excel1':
- $req = "SELECT b.date_expedition as Date, b.reference as Reference, b.lieu_intervention as lieu_intervention, ROUND(SUM(p.quantite*a.prix),2) as prix, type_bl FROM bon_de_livraison as b, articles_tarification_laposte as a, produit_et_travaux as p WHERE (b.reference = p.reference_bon_de_livraison) AND (p.designation = a.id) AND (b.archive = 1) AND (b.date_expedition BETWEEN ? AND ?) AND ( (b.type_bl = 'HORS_CIDEX') OR (b.type_bl = 'CIDEX'))GROUP BY b.reference,b.lieu_intervention,b.date_expedition,b.type_bl ORDER BY b.date_expedition;";
- $sql = $db->prepare($req);
- $sql->bindParam(1,$data[0],PDO::PARAM_STR);
- $sql->bindParam(2,$data[1],PDO::PARAM_STR);
- break;
- case 'Excel2':
- $req = "(SELECT EXTRACT(year from bl.date_expedition) as year, EXTRACT(month from bl.date_expedition) as month, 'POSTE' as client, SUM(tarif.prix*pt.quantite) as Prix, type_bl as type FROM articles_tarification_laposte as tarif, bon_de_livraison as bl,clients, produit_et_travaux as pt WHERE (pt.reference_bon_de_livraison = bl.reference) AND (pt.designation = tarif.id) AND (bl.client = clients.id) AND (bl.archive = 1) AND ((REPLACE(UPPER(clients.nom),' ','')) LIKE '%POSTE%') GROUP BY month,type,year) UNION (SELECT EXTRACT(year from bl.date_expedition) as year, EXTRACT(month from bl.date_expedition) as month, 'AUTRES_CLIENTS' as client, SUM(tarif.prix*pt.quantite) as Prix, type_bl as type FROM articles_tarification_laposte as tarif, bon_de_livraison as bl,clients, produit_et_travaux as pt WHERE (pt.reference_bon_de_livraison = bl.reference) AND (pt.designation = tarif.id) AND (bl.client = clients.id) AND (bl.archive = 1) AND ((REPLACE(UPPER(clients.nom),' ','')) NOT LIKE '%POSTE%') GROUP BY month,type,year);";
- $sql = $db->prepare($req);
- break;
- default:
- # code...
- break;
- }
- if($req != null){
- $exec = $sql->execute();
- // print_r($sql->errorInfo());
- $result = $sql->fetchAll(PDO::FETCH_ASSOC);
- }
- }
- catch (PDOException $e) {
- return false;
- }
- if (!$exec) {
- return false;
- }
- return $result;
- }
- function FichierMiseAJour(){
- $db = connexion();
- //$req = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
- //$sql = $db->prepare($req);
- //$exec = $sql->execute();
- //$table = $sql->fetchAll(PDO::FETCH_ASSOC);
- $table = array(0 => 'clients',1 => 'interventions',2 => 'bon_de_livraison',3 => 'categorie_tarification_laposte',4 => 'utilisateur',5 => 'configuration',6 => 'articles_tarification_laposte',7 => 'produit_et_travaux',8 => 'stock',9 => 'sauvegarde',10 => 'facture');
- //var_dump($table);
- $date_file = date('Y_m_d_H_i_s');
- $myfile = fopen("../sauvegarde/file/MISE_A_JOUR_".$date_file.".sql", "w") or die(json_encode(false));
- $ref = "";
- $update = "";
- $insert = "";
- for($i = 0; $i < sizeof($table) ; $i++){
- $req = "SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name= '".$table[$i]."'";
- $sql = $db->prepare($req);
- $exec = $sql->execute();
- $column = $sql->fetchAll(PDO::FETCH_ASSOC);
- //var_dump($column);
- $req = "SELECT * FROM ".$table[$i]." ORDER BY id";
- $sql = $db->prepare($req);
- $exec = $sql->execute();
- $result = $sql->fetchAll(PDO::FETCH_ASSOC);
- //var_dump($result);
- // INSERT INTO articles_tarification_laposte (id, denomination, categorie_tarification, prix) VALUES (1, 'CIDEX B1', 1, 8.49);
- /* INSERT */
- for($k = 0; $k < sizeof($result) ; $k++){
- if(sizeof($result[$k]) > 0){
- $ref = "INSERT INTO ".$table[$i]." ( ";
- $pre = "INSERT INTO ".$table[$i]." ( ";
- $post = "(";
- for($j = 0; $j < sizeof($column); $j++){
- $pre .= $column[$j]['column_name'].",";
- $type = gettype($result[$k][$column[$j]['column_name']]);
- if($type = 'string'){
- $post .= "'".str_replace("'","'' ",$result[$k][$column[$j]['column_name']])."' ,";
- }else{
- $post .= $result[$k][$column[$j]['column_name']]." ,";
- }
- }
- $pre = rtrim($pre,", ");
- $post = rtrim($post,", ");
- $pre .= ")";
- $post .= ");";
- if($insert != $ref){
- $insert .= $pre." VALUES ".$post."\n";
- }
- }
- }
- /* UPDATE */
- for($k = 0; $k < sizeof($result) ; $k++){
- if(sizeof($result[$k]) > 0){
- $ref = "UPDATE ".$table[$i]." SET ";
- $update .= "UPDATE ".$table[$i]." SET ";
- for($j = 0; $j < sizeof($column); $j++){
- if(($column[$j]['column_name'] != 'id') && ($column[$j]['column_name'] != 'facture')){
- $type = gettype($result[$k][$column[$j]['column_name']]);
- if($type = 'string'){
- $update .= $column[$j]['column_name']." = '".str_replace("'","'' ",$result[$k][$column[$j]['column_name']])."' ,";
- }else{
- $update .= $column[$j]['column_name']." = ".$result[$k][$column[$j]['column_name']]." ,";
- }
- }
- }
- $update = rtrim($update,", ");
- if($update != $ref){
- $update .= " WHERE id = ".$result[$k]['id'].";\n";
- }
- }
- }
- }
- if($update != $ref){
- fwrite($myfile, $insert);
- fwrite($myfile, $update);
- }
- fclose($myfile);
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement