<?php

/**
PHP script that query a fatabase for values and push them into an Open Bee Portallist using the Web API
We can call the script like that :
php sql2ListWithApi.php --dbDsn="odbc:DataSourceName"  --dbLogin= -dbPassword= --dmsURL="http://localhost:8000" --dmsUser=userName --dmsPassword=userPassword --deleteOldValues=true --alphabeticSort=DESC --sourceCharset= --listId=11310 --query="select XXX from TABLE1"


Below, parameters' definitions and possible values :

dbDsn => 'mysql:host=127.0.0.1;dbname=XXXX', //DSN of the database to query  http://php.net/manual/fr/ref.pdo-mysql.connection.php
dbLogin => '',  // Login of the database
dbPassword => '', // Password of the database
dmsURL => 'http://127.0.0.1:8000',  // Open Bee Portal URL (port included if not 80 or 443)
dmsUser => 'admin',  // Open Bee Portal user. Must own administration permissions in order to edit list values
dmsPassword => '', // Open Bee Portal user password
deleteOldValues => 'true',//true, false, 0,1
alphabeticSort => 'Desc',//Asc,Desc
sourceCharset => 'CP1252',//'windows-1252'or'CP1252',//https://www.php.net/manual/fr/mbstring.supported-encodings.php , you could find different code. You could use also Alias.
listId => 1234,//The ID of the list in Open Bee Portal. How to get it is discussed here : http://docs.openbee.com/pages/viewpage.action?pageId=245104696
query => 'Desc',//The  query executed in order to retrieve the list of values that will be pushed to Open Bee Portal
*/

error_reporting(E_ERROR | E_WARNING | E_PARSE);
$options = getopt(null, ["dbDsn:", "dbLogin:", "dbPassword:", "dmsURL:", "dmsUser:", "dmsPassword:", "deleteOldValues:", "alphabeticSort:", "sourceCharset:","listId:", "query:"]);
if (!isset($options['listId'])) {
    printf("Please enter a valid id list \n\n");
    exit(0);
}

if (!isset($options['query'])) {
    printf("Please enter a valid query \n\n");
    exit(0);
}


$pdo = new PDO($options['dbDsn'], $options['dbLogin'], $options['dbPassword'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$ch = curl_init();
$stmt = $pdo->prepare($options['query']);
$stmt->execute();
$data = $stmt->fetchALL(PDO::FETCH_COLUMN);
//delete duplicate and empty values
if ($data && count($data) > 0) {
    $data = array_unique($data);
    $data = array_filter($data, function ($value) {
        return !empty($value);
    });
}
if ($data && count($data) > 0) {
    $url = $options['dmsURL'] . '/ws/v2/document/list/' . $options['listId'];
    printf("About to push %s items to %s ...", count($data), $url);
//curl_setopt($ch, CURLOPT_VERBOSE, 1);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_USERPWD, $options['dmsUser'] . ":" . $options['dmsPassword']);
    curl_setopt($ch, CURLOPT_POST, 1);
    $values = implode("\n", $data);
//printf("values : " . $values);
    curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query(array('values' => $values, 'deleteOldValues' => $options['deleteOldValues'], 'alphabeticTri' => $options['alphabeticSort'], 'sourceCharset' => $options['sourceCharset'])));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    $response = curl_exec($ch);

    if ($response === false) {
        print_r("resonse false \n" . $response . "\n");
        print_r("ERR\n" . curl_error($ch) . "\n" . $response . "\n");
    } else {
        $http_status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        print_r("HTTP $http_status ");

        if ($http_status != 200) {
            print_r("ERR\n" . $response . "\n");
        } else {
            print_r("OK\n");
        }
    }
} else {
    printf("There is no data to push for the list with id " . $options['listId'] . "\n\n");
}

curl_close($ch);