<?php
    include_once '../../../scripts/bd.php';
    
    if($_SESSION["type"] == 'it') {
      die("404 not found: <a href='/app/manager/offer/show.php'>Go back</a>");
    }
    
    if(isset($_GET['action'])){
        $action=$_GET['action'];
        if($action=='add')add();
        if($action=='add_tables')add_tables();
        if($action=='show')show();
        if($action=='import')import();
        if($action=='get')get();
        if($action=='set')set();
        if($action=='set_active_on')set_active_on();
        if($action=='set_active_off')set_active_off();
        if($action=='get_news')get_news();
        if($action=='get_data_tables_not_linked')get_data_tables_not_linked();
        if($action=='get_data_tables')get_data_tables();
   }
   
   function verify_name($id,$name){
       $query = bd::query("SELECT * FROM data_list where name like '$name' && id!='$id'");
       if(mysql_num_rows($query)>0){
           return true;
       }
       return false;
   }
   
   function import()
   {
      if(isset($_FILES['file']) AND isset($_POST['table'])) {
        $validextensions  = array("txt");
        $temporary      = explode(".", $_FILES["file"]["name"]);
        $file_extension   = end($temporary);

        if ((in_array($file_extension, $validextensions)) and ($_FILES["file"]["type"] == "text/plain")) 
        {
          if ($_FILES["file"]["error"] > 0)
          {
            echo "Return Code: " . $_FILES["file"]["error"] . "<br/><br/>";
          }
          else
          {
            $sourcePath = $_FILES['file']['tmp_name'];
            $targetPath =   "/home/data/".basename($_FILES['file']['name']);
            
            if(move_uploaded_file($sourcePath,$targetPath))
            {
              ini_set("memory_limit","10000M");
              $table      =   explode("\n", file_get_contents('/home/data/'.basename($_FILES['file']['name'])));
              $cpt        =   0;
              foreach ($table as $ligne) 
              {
                $ligne  = trim($ligne);
                if ($ligne != "")
                {       
                  $ligne=explode(",",$ligne);
                  $cpt++;

                  if(filter_var($ligne[0],FILTER_VALIDATE_EMAIL)) {
                    $sql = bd::query("SELECT email_address FROM ".$_POST['table']." WHERE email_address = '".$ligne[0]."'");
                    if(!mysql_num_rows($sql)) {
                      $email_md5=md5($ligne[0]);
                    $req=bd::query("insert into ".$_POST['table']." values (NULL,'btsx$cpt','$ligne[0]','$email_md5');");
                    }
                    
                  }
                  
                              
                }            
              }
              unset($targetPath);
              echo "Data uploaded !";
            } else {
              echo 'Error ! ';
            }
        }
      }
        else
        {
          echo 'The extension of the uploaded file is not permited !';
        }
    }
   }
   function verify_data_table($data_table){
       $query = bd::query("SELECT * FROM data_list where data_table like '$data_table'");
       if(mysql_num_rows($query)>0){
           return true;
       }
       return false;
   }

   function add(){ 
        $id_news=$_POST["id_news"];
        $name=trim($_POST["name"]);
        $data_table=trim($_POST["data_table"]);
        $active=0;
        $date_add=date('Y-m-d h:i:s');
        $date_set=date('Y-m-d h:i:s');
        $ok=0;
        
        if(!verify_name('0',$name)){
            if($data_table==""){
                $rep=bd::query("INSERT INTO data_list VALUES (NULL, '$id_news', '$name', '$data_table', '0', '0', '$active', '$date_add', '$date_set');");
                if(!$rep){
                   $ok=3; 
                }
            }else{
                if(!verify_data_table($data_table)){
                    $rep=bd::query("INSERT INTO data_list VALUES (NULL, '$id_news', '$name', '$data_table', '0', '0', '$active', '$date_add', '$date_set');");
                    if(!$rep){
                       $ok=3; 
                    }
                }else{
                    $ok=2;
                }
            }
            
        }else{
            $ok=1; 
        }
        echo $ok;
   }

   function add_tables()
   {
      if(isset($_POST['name']) AND !empty($_POST['name'])) {
        $name = $_POST['name'];
        $req = bd::query("DESCRIBE `$name`");
        if($req) {
          echo 2;
        } else {
          $sql = "CREATE TABLE IF NOT EXISTS `$name` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `unique_id` varchar(255) NOT NULL,
            `email_address` varchar(255) NOT NULL,
            `email_md5` varchar(255) NOT NULL,
            PRIMARY KEY (`id`)
          )";
          if(bd::query($sql)) {
            echo 1;
          } else {
            echo "ERROR !";
          }
        }
      }
   }
   
   function show(){
      refresh_count();
      $list=array();
      if($_GET["id_news"]==0){
          $data= bd::query("SELECT id,name,data_table,count,active FROM data_list");
      }else{
          $data= bd::query("SELECT id,name,data_table,count,active FROM data_list WHERE id_news='{$_GET["id_news"]}'");
      }
      while ($row = mysql_fetch_object($data)) {
          $list[]=$row;
      }
      echo json_encode($list);
   }
   
   function get_data_tables(){
      $data=array();
      $query = bd::query("show tables like 'table_%'");
      while ($row = mysql_fetch_row($query)) {
              $data[]=$row;
      }
      echo json_encode($data);
   }
   
   function get(){ 
      $data = bd::query("SELECT * FROM data_list WHERE id='{$_GET["id_list"]}'");
      $row = mysql_fetch_array($data);
      return $row;
   }
   
    function set(){ 
       $id_news=$_POST["id_news"];
       $name=trim($_POST["name"]);
       $data_table=trim($_POST["data_table"]);
       $date_set=date('Y-m-d h:i:s');
       $ok=0;
       
       if(!verify_name($_POST["id"],$name)){
           if($data_table=="0"){
              $rep=bd::query("UPDATE data_list SET id_news='$id_news', name='$name', date_set='$date_set' WHERE id='{$_POST["id"]}'");
              if(!$rep){
                 $ok=3; 
              } 
           }else{
                if($data_table==""){
                    $rep=bd::query("UPDATE data_list SET id_news='$id_news', name='$name', data_table='$data_table', count='0', date_set='$date_set' WHERE id='{$_POST["id"]}'");
                    if(!$rep){
                       $ok=3; 
                    }
                }else{
                    if(!verify_data_table($data_table)){
                        $rep=bd::query("UPDATE data_list SET id_news='$id_news', name='$name', data_table='$data_table', count=(SELECT COUNT(*) from $data_table), date_set='$date_set' WHERE id='{$_POST["id"]}'");
                        if(!$rep){
                           $ok=3; 
                        }
                    }else{
                        $ok=2;
                    }
                }
           }
        }else{
            $ok=1; 
        }
        echo $ok;
   }
   
   function set_active_on(){ 
       bd::query("UPDATE data_list SET active= '1' WHERE id='{$_GET["id"]}'");
   }
   
   function set_active_off(){ 
       bd::query("UPDATE data_list SET active= '0' WHERE id='{$_GET["id"]}'");
   }
   
   function get_news(){
      $data=array();
      $query = bd::query("SELECT id,name FROM data_news");
      while ($row = mysql_fetch_object($query)) {
          $data[]=$row;
      }
      echo json_encode($data);
   }
   
   function refresh_count(){
       $query= bd::query("SELECT id,data_table FROM data_list");
       while ($row = mysql_fetch_array($query)) {
           $queryx=bd::query("SELECT COUNT(*) FROM {$row['data_table']}");
           if(mysql_num_rows($queryx)>0){
               $rowx=mysql_fetch_row($queryx);
               bd::query("UPDATE data_list SET count='$rowx[0]' WHERE id='{$row['id']}'");
           }else{
               bd::query("UPDATE data_list SET count='-1' WHERE id='{$row['id']}'");
           }
           
       }
   }
   
   function get_data_tables_not_linked(){
      $data=array();
      $query = bd::query("SHOW TABLES LIKE 'table_%'");
      while ($row = mysql_fetch_row($query)) {
          if(!verify_data_table($row[0])){
              $data[]=$row;
          }
      }
      echo json_encode($data);
   }
   
   function get_data_table_count($data_table){
      $query = bd::query("SELECT COUNT(*) FROM $data_table");
      $row=  mysql_fetch_row($query);
      return $row[0];
   }
   
   function get_data_list_linked($data_table){
      $query = bd::query("SELECT name FROM data_list WHERE data_table='$data_table'");
      $row=  mysql_fetch_row($query);
      return $row[0];
   }
 
   

