<?php

require_once '../lib/DataSourceResult.php';
require_once '../lib/Kendo/Autoload.php';


if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    header('Content-Type: application/json');

    $request = json_decode(file_get_contents('php://input'));

    $result = new DataSourceResult('sqlite:..//sample.db');

    $type = $_GET['type'];

    $fields = array('Id', 'JobTitle', 'Color');
    $connectionFields = array('Id', 'FromShapeId', 'ToShapeId', 'Text', 'FromPointX', 'FromPointY', 'ToPointX', 'ToPointY');

    switch($type) {
        case 'create':
            $result = $result->create('OrgChartShapes', $fields, $request, 'Id');
            break;
        case 'read':
            $result = $result->read('OrgChartShapes', $fields, $request);
            break;
        case 'update':
            $result = $result->update('OrgChartShapes', $fields, $request, 'Id');
            break;
        case 'destroy':
            $result = $result->destroy('OrgChartShapes', $request, 'Id');
            break;
        case 'createConnection':             
            $result = $result->create('OrgChartConnections', $connectionFields, $request, 'Id');
            break;
        case 'readConnections':
            $result = $result->read('OrgChartConnections', $connectionFields, $request);
            break;
        case 'updateConnection':     
            $result = $result->update('OrgChartConnections', $connectionFields, $request, 'Id');
            break;
        case 'destroyConnection':
            $result = $result->destroy('OrgChartConnections', $request, 'Id');
            break;
    }

    echo json_encode($result, JSON_NUMERIC_CHECK);

    exit;
}

?>

<script type="text/javascript">
	function visualTemplate(options) {
	    var dataviz = kendo.dataviz;
	    var g = new dataviz.diagram.Group();
	    var dataItem = options.dataItem;

	    if (dataItem.JobTitle === "President") {
	        g.append(new dataviz.diagram.Circle({
	            radius: 60,
	            stroke: {
	                width: 2,
	                color: "#586477"
	            },
	            fill: "#e8eff7"
	        }));
	    } else {
	        g.append(new dataviz.diagram.Rectangle({
	            width: 240,
	            height: 67,
	            stroke: {
	                width: 0
	            },
	            fill: "#e8eff7"
	        }));

	        g.append(new dataviz.diagram.Rectangle({
	            width: 8,
	            height: 67,
	            fill: dataItem.Color,
	            stroke: {
	                width: 0
	            }
	        }));
	    }

	    return g;
	}

	function onDataBound(e) {
	    var that = this;
	    setTimeout(function () {
	        that.bringIntoView(that.shapes);
	    }, 0);
	}
</script>

<?php
$transport = new \Kendo\Data\DataSourceTransport();

$create = new \Kendo\Data\DataSourceTransportCreate();

$create->url('editing.php?type=create')
     ->contentType('application/json')
     ->type('POST');

$read = new \Kendo\Data\DataSourceTransportRead();

$read->url('editing.php?type=read')
     ->contentType('application/json')
     ->type('POST');

$update = new \Kendo\Data\DataSourceTransportUpdate();

$update->url('editing.php?type=update')
     ->contentType('application/json')
     ->type('POST');

$destroy = new \Kendo\Data\DataSourceTransportDestroy();

$destroy->url('editing.php?type=destroy')
     ->contentType('application/json')
     ->type('POST');

$transport->create($create)
          ->read($read)
          ->update($update)
          ->destroy($destroy)
          ->parameterMap('function(data) {
              return kendo.stringify(data);
          }');

$model = new \Kendo\Data\DataSourceSchemaModel();

$shapeIDField = new \Kendo\Data\DataSourceSchemaModelField('Id');
$shapeIDField->type('number')
               ->editable(false);
$jobTitleField = new \Kendo\Data\DataSourceSchemaModelField('JobTitle');
$jobTitleField->type('string');

$colorField = new \Kendo\Data\DataSourceSchemaModelField('Color');
$colorField->type('string');

$model->id('Id')
    ->addField($shapeIDField)
    ->addField($jobTitleField)
    ->addField($colorField);

$schema = new \Kendo\Data\DataSourceSchema();
$schema->model($model)
        ->data('data')
        ->total('total');

$dataSource = new \Kendo\Data\DataSource();
$dataSource->transport($transport)
           ->schema($schema);


$connectionsTransport = new \Kendo\Data\DataSourceTransport();

$connectionsCreate = new \Kendo\Data\DataSourceTransportCreate();

$connectionsCreate->url('editing.php?type=createConnection')
     ->contentType('application/json')
     ->type('POST');

$connectionsRead = new \Kendo\Data\DataSourceTransportRead();

$connectionsRead->url('editing.php?type=readConnections')
     ->contentType('application/json')
     ->type('POST');

$connectionsUpdate = new \Kendo\Data\DataSourceTransportUpdate();

$connectionsUpdate->url('editing.php?type=updateConnection')
     ->contentType('application/json')
     ->type('POST');

$connectionsDestroy = new \Kendo\Data\DataSourceTransportDestroy();

$connectionsDestroy->url('editing.php?type=destroyConnection')
     ->contentType('application/json')
     ->type('POST');

$connectionsTransport->create($connectionsCreate)
          ->read($connectionsRead)
          ->update($connectionsUpdate)
          ->destroy($connectionsDestroy)
          ->parameterMap('function(data) {
              return kendo.stringify(data);
          }');

$connectionsModel = new \Kendo\Data\DataSourceSchemaModel();

$connectionIDField = new \Kendo\Data\DataSourceSchemaModelField('Id');
$connectionIDField->type('number')
               ->editable(false);
$textField = new \Kendo\Data\DataSourceSchemaModelField('Text');
$textField->type('string');

$fromField = new \Kendo\Data\DataSourceSchemaModelField('from');
$fromField->type('number')
          ->from('FromShapeId');

$toField = new \Kendo\Data\DataSourceSchemaModelField('to');
$toField->type('number')
        ->from('ToShapeId');
        
$fromXField = new \Kendo\Data\DataSourceSchemaModelField('fromX');
$fromXField->type('number')
          ->from('FromPointX');
$fromYField = new \Kendo\Data\DataSourceSchemaModelField('fromY');
$fromYField->type('number')
          ->from('FromPointY');

$toXField = new \Kendo\Data\DataSourceSchemaModelField('toX');
$toXField->type('number')
        ->from('ToPointX');

$toYField = new \Kendo\Data\DataSourceSchemaModelField('toY');
$toYField->type('number')
        ->from('ToPointY');

$connectionsModel->id('Id')
    ->addField($connectionIDField)
    ->addField($textField)
    ->addField($fromField)
    ->addField($toField)
    ->addField($fromXField)
    ->addField($fromYField)
    ->addField($toXField)
    ->addField($toYField);

$connectionsSchema = new \Kendo\Data\DataSourceSchema();
$connectionsSchema->model($connectionsModel)
                  ->data('data')
                  ->total('total');

$connectionsDataSource = new \Kendo\Data\DataSource();
$connectionsDataSource->transport($connectionsTransport)
           ->schema($connectionsSchema);


$layout = new \Kendo\Dataviz\UI\DiagramLayout();
$layout->type('tree')
       ->subtype('tipover')
       ->underneathHorizontalOffset(140);

       
$shapeContent = new \Kendo\Dataviz\UI\DiagramShapeDefaultsContent();
$shapeContent->template('#:dataItem.JobTitle#')
             ->fontSize(17);

$shape_defaults = new \Kendo\Dataviz\UI\DiagramShapeDefaults();
$shape_defaults->visual(new \Kendo\JavaScriptFunction('visualTemplate'))
               ->content($shapeContent);

$stroke = new \Kendo\Dataviz\UI\DiagramConnectionDefaultsStroke();
$stroke->color('#586477')
       ->width(2);

$connection_defaults = new \Kendo\Dataviz\UI\DiagramConnectionDefaults();
$connection_defaults->stroke($stroke);

$diagram = new \Kendo\Dataviz\UI\Diagram('diagram');
$diagram->dataSource($dataSource)
        ->connectionsDataSource($connectionsDataSource)
        ->layout($layout)
        ->dataBound('onDataBound')
        ->shapeDefaults($shape_defaults)
        ->connectionDefaults($connection_defaults);

echo $diagram->render();
?>                    

<?php

class DataSourceResult {
    protected $db;

    private $stringOperators = array(
        'eq' => 'LIKE',
        'neq' => 'NOT LIKE',
        'doesnotcontain' => 'NOT LIKE',
        'contains' => 'LIKE',
        'startswith' => 'LIKE',
        'endswith' => 'LIKE'
    );

    private $operators = array(
        'eq' => '=',
        'gt' => '>',
        'gte' => '>=',
        'lt' => '<',
        'lte' => '<=',
        'neq' => '!='
    );

    private $aggregateFunctions = array(
        'average' => 'AVG',
        'min' => 'MIN',
        'max' => 'MAX',
        'count' => 'COUNT',
        'sum' => 'SUM'
    );

    function __construct($dsn, $username=null, $password=null, $driver_options=null) {
        $this->db = new PDO($dsn, $username, $password, $driver_options);
    }

    private function total($tableName, $properties, $request) {
        if (isset($request->filter)) {
            $where = $this->filter($properties, $request->filter);
            $statement = $this->db->prepare("SELECT COUNT(*) FROM $tableName $where");
            $this->bindFilterValues($statement, $request->filter);
        } else {
            $statement = $this->db->prepare("SELECT COUNT(*) FROM $tableName");
        }

        $statement->execute();

        $total = $statement->fetch(PDO::FETCH_NUM);
        return (int)($total[0]);
    }

    private function page() {
        return ' LIMIT :take OFFSET :skip';
    }

    private function group($data, $groups, $table, $request, $propertyNames) {
        if (count($data) > 0) {
            return $this->groupBy($data, $groups, $table, $request, $propertyNames);
        }
        return array();
    }

    private function mergeSortDescriptors($request) {
        $sort = isset($request->sort) && count($request->sort) ? $request->sort : array();
        $groups = isset($request->group) && count($request->group) ? $request->group : array();

        return array_merge($sort, $groups);
    }

    private function groupBy($data, $groups, $table, $request, $propertyNames) {
        if (count($groups) > 0) {
            $field = $groups[0]->field;
            $count = count($data);
            $result = array();
            $value = $data[0][$field];
            $aggregates = isset($groups[0]->aggregates) ? $groups[0]->aggregates : array();

            $hasSubgroups = count($groups) > 1;
            $groupItem = $this->createGroup($field, $value, $hasSubgroups, $aggregates, $table, $request, $propertyNames);

            for ($index = 0; $index < $count; $index++) {
                $item = $data[$index];
                if ($item[$field] != $value) {
                    if (count($groups) > 1) {
                        $groupItem["items"] = $this->groupBy($groupItem["items"], array_slice($groups, 1), $table, $request, $propertyNames);
                    }

                    $result[] = $groupItem;

                    $groupItem = $this->createGroup($field, $data[$index][$field], $hasSubgroups, $aggregates, $table, $request, $propertyNames);
                    $value = $item[$field];
                }
                $groupItem["items"][] = $item;
            }

            if (count($groups) > 1) {
                $groupItem["items"] = $this->groupBy($groupItem["items"], array_slice($groups, 1), $table, $request, $propertyNames);
            }

            $result[] = $groupItem;

            return $result;
        }
        return array();
    }

    private function addFilterToRequest($field, $value, $request) {
        $filter = (object)array(
            'logic' => 'and',
            'filters' => array(
                (object)array(
                    'field' => $field,
                    'operator' => 'eq',
                    'value' => $value
                ))
            );

        if (isset($request->filter)) {
            $filter->filters[] = $request->filter;
        }

        return (object) array('filter' => $filter);
    }

    private function addFieldToProperties($field, $propertyNames) {
        if (!in_array($field, $propertyNames)) {
            $propertyNames[] = $field;
        }
        return $propertyNames;
    }

    private function createGroup($field, $value, $hasSubgroups, $aggregates, $table, $request, $propertyNames) {
        if (count($aggregates) > 0) {
            $request = $this->addFilterToRequest($field, $value, $request);
            $propertyNames = $this->addFieldToProperties($field, $propertyNames);
        }

        $groupItem = array(
            'field' => $field,
            'aggregates' => $this->calculateAggregates($table, $aggregates, $request, $propertyNames),
            'hasSubgroups' => $hasSubgroups,
            'value' => $value,
            'items' => array()
        );

        return $groupItem;
    }

    private function calculateAggregates($table, $aggregates, $request, $propertyNames) {
        $count = count($aggregates);

        if (count($aggregates) > 0) {
            $functions = array();

            for ($index = 0; $index < $count; $index++) {
                $aggregate = $aggregates[$index];
                $name = $this->aggregateFunctions[$aggregate->aggregate];
                $functions[] = $name.'('.$aggregate->field.') as '.$aggregate->field.'_'.$aggregate->aggregate;
            }

            $sql = sprintf('SELECT %s FROM %s', implode(', ', $functions), $table);

            if (isset($request->filter)) {
                $sql .= $this->filter($propertyNames, $request->filter);
            }

            $statement = $this->db->prepare($sql);

            if (isset($request->filter)) {
                $this->bindFilterValues($statement, $request->filter);
            }

            $statement->execute();

            $result = $statement->fetchAll(PDO::FETCH_ASSOC);

            return $this->convertAggregateResult($result[0]);
        }
        return (object)array();
    }

    private function convertAggregateResult($propertyNames) {
        $result = array();

        foreach($propertyNames as $property => $value) {
            $item = array();
            $split = explode('_', $property);
            $field = $split[0];
            $function = $split[1];
            if (array_key_exists($field, $result)) {
                $result[$field][$function] = $value;
            } else {
                $result[$field] = array($function => $value);
            }
        }

        return $result;
    }

    private function sort($propertyNames, $sort) {
        $count = count($sort);

        $sql = '';

        if ($count > 0) {
            $sql = ' ORDER BY ';

            $order = array();

            for ($index = 0; $index < $count; $index ++) {
                $field = $sort[$index]->field;

                if (in_array($field, $propertyNames)) {
                    $dir = 'ASC';

                    if ($sort[$index]->dir == 'desc') {
                        $dir = 'DESC';
                    }

                    $order[] = "$field $dir";
                }

            }

            $sql .= implode(',', $order);
        }

        return $sql;
    }

    private function where($properties, $filter, $all) {
        if (isset($filter->filters)) {
            $logic = ' AND ';

            if ($filter->logic == 'or') {
                $logic = ' OR ';
            }

            $filters = $filter->filters;

            $where = array();

            for ($index = 0; $index < count($filters); $index++) {
                $where[] = $this->where($properties, $filters[$index], $all);
            }

            $where = implode($logic, $where);

            return "($where)";
        }

        $field = $filter->field;

        $propertyNames = $this->propertyNames($properties);

        if (in_array($field, $propertyNames)) {
            $type = "string";

            $index = array_search($filter, $all);

            $value = ":filter$index";

            if (isset($properties[$field])) {
                $type = $properties[$field]['type'];
            } else if ($this->isDate($filter->value)) {
                $type = "date";
            } else if (array_key_exists($filter->operator, $this->operators) && !$this->isString($filter->value)) {
                $type = "number";
            }

            if ($type == "date") {
                $field = "date($field)";
                $value = "date($value)";
            }

            if ($type == "string") {
                $operator = $this->stringOperators[$filter->operator];
            } else {
                $operator = $this->operators[$filter->operator];
            }

            return "$field $operator $value";
        }
    }

    private function flatten(&$all, $filter) {
        if (isset($filter->filters)) {
            $filters = $filter->filters;

            for ($index = 0; $index < count($filters); $index++) {
                $this->flatten($all, $filters[$index]);
            }
        } else {
            $all[] = $filter;
        }
    }

    private function filter($properties, $filter) {
        $all = array();

        $this->flatten($all, $filter);

        $where = $this->where($properties, $filter, $all);

        return " WHERE $where";
    }

    private function isDate($value) {
        $result = date_parse($value);
        return $result["error_count"] < 1 && checkdate($result['month'], $result['day'], $result['year']);
    }

    private function isString($value) {
        return !is_bool($value) && !is_numeric($value) && !$this->isDate($value);
    }

    protected function propertyNames($properties) {
        $names = array();

        foreach ($properties as $key => $value) {
            if (is_string($value)) {
                $names[] = $value;
            } else {
                $names[] = $key;
            }
        }

        return $names;
    }

    private function bindFilterValues($statement, $filter) {
        $filters = array();
        $this->flatten($filters, $filter);

        for ($index = 0; $index < count($filters); $index++) {
            $value = $filters[$index]->value;
            $operator = $filters[$index]->operator;
            $date = date_parse($value);

            if ($operator == 'contains' || $operator == 'doesnotcontain') {
                $value = "%$value%";
            } else if ($operator == 'startswith') {
                $value = "$value%";
            } else if ($operator == 'endswith') {
                $value = "%$value";
            }

            $statement->bindValue(":filter$index", $value);
        }
    }

    public function create($table, $properties, $models, $key) {
        $result = array();
        $data = array();
        $propertyNames = $this->propertyNames($properties);

        if (!is_array($models)) {
            $models = array($models);
        }

        $errors = array();

        foreach ($models as $model) {
            $columns = array();
            $values = array();
            $input_parameters = array();

            foreach ($propertyNames as $property) {
                if ($property != $key) {
                    $columns[] = $property;
                    $values[] = '?';
                    $input_parameters[] = $model->$property;
                }
            }

            $columns = implode(', ', $columns);
            $values = implode(', ', $values);

            $sql = "INSERT INTO $table ($columns) VALUES ($values)";

            $statement = $this->db->prepare($sql);

            $statement->execute($input_parameters);

            $status = $statement->errorInfo();

            if ($status[1] > 0) {
                $errors[] = $status[2];
            } else {
                $model->$key = $this->db->lastInsertId();
                $data[] = $model;
            }
        }

        if (count($errors) > 0) {
            $result['errors'] = $errors;
        } else {
            $result['data'] = $data;
        }

        return $result;
    }

    public function destroy($table, $models, $key) {
        $result = array();

        if (!is_array($models)) {
            $models = array($models);
        }

        $errors = array();

        foreach ($models as $model) {
            $sql = "DELETE FROM $table WHERE $key=?";

            $statement = $this->db->prepare($sql);

            $statement->execute(array($model->$key));

            $status = $statement->errorInfo();

            if ($status[1] > 0) {
                $errors[] = $status[2];
            }
        }

        if (count($errors) > 0) {
            $result['errors'] = $errors;
        }

        return $result;
    }

    public function update($table, $properties, $models, $key) {
        $result = array();

        $propertyNames = $this->propertyNames($properties);

        if (in_array($key, $propertyNames)) {

            if (!is_array($models)) {
                $models = array($models);
            }

            $errors = array();

            foreach ($models as $model) {
                $set = array();

                $input_parameters = array();

                foreach ($propertyNames as $property) {
                    if ($property != $key) {
                        $set[] = "$property=?";
                        $input_parameters[] = $model->$property;
                    }
                }

                $input_parameters[] = $model->$key;

                $set = implode(', ', $set);

                $sql = "UPDATE $table SET $set WHERE $key=?";

                $statement = $this->db->prepare($sql);

                $statement->execute($input_parameters);

                $status = $statement->errorInfo();

                if ($status[1] > 0) {
                    $errors[] = $status[2];
                }
            }

            if (count($errors) > 0) {
                $result['errors'] = $errors;
            }
        }

        if (count($result) == 0) {
            $result = "";
        }

        return $result;
    }

    public function read($table, $properties, $request = null) {
        $result = array();

        $propertyNames = $this->propertyNames($properties);

        $result['total'] = $this->total($table, $properties, $request);

        $sql = sprintf('SELECT %s FROM %s', implode(', ', $propertyNames), $table);

        if (isset($request->filter)) {
            $sql .= $this->filter($properties, $request->filter);
        }

        $sort = $this->mergeSortDescriptors($request);

        if (count($sort) > 0) {
            $sql .= $this->sort($propertyNames, $sort);
        }

        if (isset($request->skip) && isset($request->take)) {
            $sql .= $this->page();
        }

        $statement = $this->db->prepare($sql);

        if (isset($request->filter)) {
            $this->bindFilterValues($statement, $request->filter);
        }

        if (isset($request->skip) && isset($request->take)) {
            $statement->bindValue(':skip', (int)$request->skip, PDO::PARAM_INT);
            $statement->bindValue(':take', (int)$request->take, PDO::PARAM_INT);
        }

        $statement->execute();

        $data = $statement->fetchAll(PDO::FETCH_ASSOC);

        if (isset($request->group) && count($request->group) > 0) {
            $data = $this->group($data, $request->group, $table, $request, $propertyNames);
            $result['groups'] = $data;
        } else {
            $result['data'] = $data;
        }

        if (isset($request->aggregate)) {
            $result["aggregates"] = $this->calculateAggregates($table, $request->aggregate, $request, $propertyNames);
        }

        return $result;
    }

    public function readJoin($table, $joinTable, $properties, $key, $column, $request = null) {
        $result = $this->read($table, $properties, $request);

        for ($index = 0, $count = count($result['data']); $index < $count; $index++) {
            $sql = sprintf('SELECT %s FROM %s WHERE %s = %s', $column, $joinTable, $key, $result['data'][$index][$key]);

            $statement = $this->db->prepare($sql);
            $statement->execute();
            $data = $statement->fetchAll(PDO::FETCH_NUM);
            $result['data'][$index]['Attendees'] = $data;
        }

        return $result;
    }
}

?>