<!doctype html>
<html>

	<title>Chart</title>
	<script src="js/chart.min.js"></script>
	<script src='js/jquery-3.3.1.min.js'></script>
<script src="js/jquery.redirect.js"></script>
    
<link rel="stylesheet" href="css/bootstrap.css" >
<link rel="stylesheet" href="css/font-awesome.min.css" >
<script src="js/bootstrap.min.js" ></script>
    
    <style>
        .section-header{
            background-color: aliceblue;
            font-family:Inter UI,Source Sans Pro,Helvetica Neue,Arial,sans-serif;
            font-size:160px;
            font-weight:800;
            text-align:left;
            z-index:10;
            position:relative;
            line-height:0.6em;
            pointer-events:none;
            flex:1 100%
        }
     @media all and (max-width: 600px){.section-header{font-size:25vw}}
        
 /*--------------------------------*/

/*  9.review Area
/*--------------------------------*/

.reviews-area {
  background: url('../about/2.jpg');
  overflow: hidden;
  background-repeat: no-repeat;
  background-size: cover;
  background-position: top center;
  background-attachment: fixed;
  width: 100%;
  height: auto;
  position: relative;
}

.work-left-text {
  background: #3EC1D5 none repeat scroll 0 0;
}

.work-left-text {
  width: 50%;
}

.work-right-text {
  background: rgba(0, 0, 0, 0.8) none repeat scroll 0 0;
  float: right;
  height: 100%; 
  overflow: hidden;
  padding: 75px 0;
  width: 50%;
  position: absolute;
  right: 0;
  top: 0;
}

.work-right-text h2 {
  color: #fff;
  text-transform: uppercase;
  font-size: 24px;
}

.work-right-text h5 {
  color: #fff;
  font-size: 18px;
  font-weight: 700;
  line-height: 34px;
  text-transform: uppercase;
}

.work-right-text .sus-btn {
  margin-left: 0;
  margin-top: 20px;
}

.single-awesome-4 {
  display: block;
  float: left;
  overflow: hidden;
  width: 33.33%;
}

.single-awesome-4 .add-actions {
  padding: 10px 20px;
}

        
    </style>
    
    <body>

        
        
        <div class="container">
            
                   
        
            <br>
        <h2 class="section-header">Graphiques</h2> 
             <div class="row">
                  <div class="col-6 align-self-center col-md-push-5" style="background-color:brown">
                      <br>
                 </div>
                 <div class="col-6 align-self-center col-md-push-5" style="background-color:azure">
                     <br>
                 </div>
            </div>
            
             
            
            
              <div class="row">
            <div class="col-6 align-self-center col-md-push-5">
        <canvas id="bar-chart" width="800" height="450"></canvas>
                  </div>
                    <div class="col-6 align-self-center col-md-push-5">
                         <canvas id="bar-chart2" width="800" height="450"></canvas>
       
                  </div>
                  
            </div>
            
                <div class="row">
            <div class="col-6 align-self-center col-md-push-5">
        <canvas id="pie-chart" width="800" height="450"></canvas>
                  </div>
                    
                    
                    <div class="col-6 align-self-center col-md-push-5">
                         <canvas id="pie-chart2" width="800" height="450"></canvas>
     
                  </div>
                  
            </div>
            
           
              <canvas id="bar-chart-horizontal" width="800" height="450"></canvas>
            
           
            
        </div>
        
       
        
        
        <div>
        <div class="row reviews-area ">
            <div class="col hidden-xs">          
                <img src="../about/2.jpg" class="img-fluid" alt="">
            </div>
            
            <div class="col hidden-xs work-right-text ">
                <h2>working with us</h2> working with us
        Web Design, Ready Home, Construction and Co-operate Outstanding Buildings.
            </div>
            
        </div> 
        </div>
         <!-- Start reviews Area
  <div class="reviews-area hidden-xs">
    <div class="work-us">
      <div class="work-left-text">
        <a href="#">
						<img src="../about/2.jpg" alt="">
					</a>
      </div>
      <div class="work-right-text text-center">
        <h2>working with us</h2>
        <h5>Web Design, Ready Home, Construction and Co-operate Outstanding Buildings.</h5>
        <a href="#contact" class="ready-btn">Contact us</a>
      </div>
    </div>
  </div>
  <!-- End reviews Area -->
        
        
        <?php


$SOC=" ";
$NOM1=" ";
$NOM2=" ";
$NOM3=" ";
$NOM4=" ";
$NOM5=" ";
$TITRE=" ";
$PUB=" ";
$AC1=" ";
$AC2=" ";
$O1=" ";
$O2=" ";
$O3=" ";
$MAIL=" ";
$GSM=" ";
$TEL=" ";

// on se connecte à notre base

$servername = "localhost:8889";
$username = "test";
$password = "test";
$dbname = "Clients";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
        
$resm = array(0);
//array_push($stack, "apple", "raspberry");
//print_r($stack);
        echo $stack[2];
    $resm = array(0);

     for ($i = 1; $i <= 12; $i++) {
   $sql = " SELECT count(*) as 'nombre' FROM data WHERE SUBSTR(jour, 4, 5) ='0".$i."/18'";
         if ($i > 9) {  $sql = " SELECT count(*) as 'nombre' FROM data WHERE SUBSTR(jour, 4, 5) ='".$i."/18'";  } 
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
  
    while($row = $result->fetch_assoc()) {
     
   //    echo "Nombre ".$i." : " . $row["nombre"]. " ";
         array_push($resm, $row["nombre"]);
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
     $res[i] =$row["nombre"];
	
   
  //  print_r($resm);
	} else {
    echo "0 results";
} 
      
       } 
  //  echo $resm[1] .",".$resm[2] .",".$resm[3] .",".$resm[4] .",".$resm[5] .",".$resm[6] .",".$resm[7] .",".$resm[8] .",".$resm[9] .",".$resm[10] .",".$resm[11] .",".$resm[12] ;    
        
$sql = "SELECT * FROM data";
   $sql = " SELECT count(*) as 'nombre' FROM data WHERE (SUBSTR(jour, 7, 2) ='18') AND (pub like '%web%' OR pub like '%google%' OR pub like '%site%' OR pub like '%avis%' OR pub like '%internet%') group by id ";
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    $res=0;
    while($row = $result->fetch_assoc()) {
        $res++;
       // echo "Nombre: " . $row["nombre"]. " ";
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
    // echo "Nombre: " .$res;
	
	} else {
    echo "0 results";
}
 
           $sql = " SELECT count(*) as 'autre' FROM data WHERE (SUBSTR(jour, 7, 2) ='18') AND (pub ='' ) group by id";
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
     $res1=0;
    while($row = $result->fetch_assoc()) {
           $res1++;
       // echo "Autre: " . $row["autre"]. " ";
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
      echo "Vide : " .$res1;
	
	} else {
    echo "0 results";
}
        
        
   $sql = " SELECT count(*) as 'total' FROM data WHERE (SUBSTR(jour, 7, 2) ='18')";
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
    //    echo "Total: " . $row["total"]. " ";
        $restot=$row["total"];
     //   echo $restot;
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
    
	
	} else {
    echo "0 results";
}     
        
        
        print "<div class='container'>Classement par communes des prospects : <br> <div class='row'>
    <div class='col'>";
        

        $resautre=$restot-$res-$res1;
//$sql = "INSERT INTO data (societe,nom1,nom2,nom3,nom4,nom5,titre,ac1,ac2,o1,o2,o3,mail,tel,gsm,pub,jour)
//VALUES ('$SOC' , '$NOM1', '$NOM2', '$NOM3', '$NOM4', '$NOM5', '$TITRE', '$AC1', '$AC2', '$O1', '$O2', '$O3', '$MAIL', '$TEL', '$GSM', '$PUB', //'$today')";

        
         $sql = " SELECT count(*) as 'total', nom3, SUBSTR(nom3, 1, 4)  FROM  data where nom3<>'' group by SUBSTR(nom3, 1, 4) HAVING total > 2 order by total DESC";
    //    print $sql."<br>";
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);
$totprospects=0;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Total: " . $row["total"]. " pour la commune : ".$row["nom3"]."<br>";
        $totprospects=$totprospects+$row["total"];
       // $restotcommune++;
      
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
    
	
	} else {
    echo "0 results";
}  
     print "Total prospects : ".$totprospects."<br></div><div class='col'>";   
          
  $conn->close();      
        
        // on se connecte à notre base

$servername = "localhost:8889";
$username = "root";
$password = "root";
$dbname = "facture";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
        
        
         $sql = "SELECT facture.tete.num, Clients.Data.nom1, Clients.Data.nom3, count(Clients.Data.nom1) as 'total'  FROM facture.tete inner join Clients.Data on facture.tete.idxcli=Clients.Data.id GROUP by SUBSTR(Clients.Data.nom3, 1, 4) HAVING total > 2 ORDER BY TOTAL DESC";
    //    print $sql."<br>";
//$sql = "SELECT * FROM data WHERE id=1";
$result = $conn->query($sql);
$totprospects=0;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Total: " . $row["total"]. " pour la commune : ".$row["nom3"]."<br>";
        $totprospects=$totprospects+$row["total"];
       // $restotcommune++;
      
     //   echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. " - idx: " . $row["id"]. " " . $row["nom2"]. "<br>";
		//echo "Soc: " . $row["societe"]. " - Nom: " . $row["nom1"]. "  " . $row["nom2"]. "<br>";
    } 
    
	
	} else {
    echo "0 results";
}  
     print "Total prospects facturé : ".$totprospects."<br>";   
          
      $conn->close();      
        
        
        
        
        
        
        
        
        
        
        
        
     print "</div>    </div>
  </div>";
        print "<script>";
        print"  new Chart(document.getElementById(".chr(34)."pie-chart2".chr(34)."), {
    type: ".chr(34)."pie".chr(34).",
    data: {
      labels: [".chr(34)."Google".chr(34).", ".chr(34)."Vide".chr(34).", ".chr(34)."Autre".chr(34)."],
      datasets: [{
        label: ".chr(34)."provenance prospect".chr(34).",
        backgroundColor: [".chr(34)."#3e95cd".chr(34).", ".chr(34)."#8e5ea2".chr(34).",".chr(34)."#3cba9f".chr(34).",".chr(34)."#e8c3b9".chr(34).",".chr(34)."#c45850".chr(34).",".chr(34)."#a4262c".chr(34).",".chr(34)."#ca5010".chr(34).",".chr(34)."#4052ab".chr(34).",".chr(34)."#038387".chr(34).",".chr(34)."#854085".chr(34).",".chr(34)."#737373".chr(34).",".chr(34)."#867365".chr(34)."],
        data: [".$res.",".$res1.",".$resautre."]
      }]
    },
    options: {
      title: {
        display: true,
        text: ".chr(34)."Répartition des nouveaux prospects".chr(34)."
      }
    }
}); ";
print "</script>";
    // echo "Nombre de communes".$restotcommune;     




?>
        
        
        
        
        <br><br><br><br><br><br><br>
                
        <script>
        // Bar chart
new Chart(document.getElementById("bar-chart"), {
    type: 'bar',
    data: {
      labels: ["Janv.", "Fev.", "Mar.", "Avr.", "Mai.","Jui.","Jul.","Aou.","Sept.","Oct.","Nov.","Dec."],
      datasets: [
        {
          label: "Clients nouveaux ",
          backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#a4262c","#ca5010","#4052ab","#038387","#854085","#737373","#867365"],
        data: [<?php echo $resm[1] .",".$resm[2] .",".$resm[3] .",".$resm[4] .",".$resm[5] .",".$resm[6] .",".$resm[7] .",".$resm[8] .",".$resm[9] .",".$resm[10] .",".$resm[11] .",".$resm[12] ;   ?>]
        }
      ]
    },
    options: {
      legend: { display: false },
      title: {
        display: true,
        text: 'Nombre de nouveaux prospects par mois.'
      }
    }
});
            
 
            
  
            
            
      new Chart(document.getElementById("pie-chart"), {
    type: 'pie',
    data: {
      labels: ["Google", "Vide", "Autre"],
      datasets: [{
        label: "provenance prospect",
        backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#a4262c","#ca5010","#4052ab","#038387","#854085","#737373","#867365"],
        data: [<?php echo $res.",".$res1.",".$resautre; ?>]
      }]
    },
    options: {
      title: {
        display: true,
        text: 'Répartition des nouveaux prospects'
      }
    }
});      
          
            
            
            new Chart(document.getElementById("bar-chart-horizontal"), {
    type: 'horizontalBar',
    data: {
      labels: ["Africa", "Asia", "Europe", "Latin America", "North America"],
      datasets: [
        {
          label: "Population (millions)",
          backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850"],
          data: [2478,5267,734,784,433]
        }
      ]
    },
    options: {
      legend: { display: false },
      title: {
        display: true,
        text: 'Predicted world population (millions) in 2050'
      }
    }
});
            
     // Bar chart
new Chart(document.getElementById("bar-chart2"), {
    type: 'bar',
    data: {
      labels: ["Janv.", "Fev.", "Mar.", "Avr.", "Mai.","Jui.","Jul.","Aou.","Sept.","Oct.","Nov.","Dec."],
      datasets: [
        {
          label: "Clients nouveaux ",
          backgroundColor: ["#3e95cd", "#8e5ea2","#3cba9f","#e8c3b9","#c45850","#a4262c","#ca5010","#4052ab","#038387","#854085","#737373","#867365"],
        data: [<?php echo $resm[1] .",".$resm[2] .",".$resm[3] .",".$resm[4] .",".$resm[5] .",".$resm[6] .",".$resm[7] .",".$resm[8] .",".$resm[9] .",".$resm[10] .",".$resm[11] .",".$resm[12] ;   ?>]
        }
      ]
    },
    options: {
      legend: { display: false },
      title: {
        display: true,
        text: 'Nombre de nouveaux prospects par mois.'
      }
    }
});           
            
    </script>
        
    </body>

</html>
