Select database where [mysql] [GANTT]

8 posts, 0 answers
  1. Valerius
    Valerius avatar
    14 posts
    Member since:
    Oct 2017

    Posted 27 Oct Link to this post

    Hi there, can you guys help my to create select database where.... There is a code that working good.

    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('mysql:host=localhost:3306 ;dbname=database_4', 'user', 'password');

        $type = $_GET['type'];

        $operation = $_GET['operation'];

        if ($type == "dependency") {
            $columns = array('ID', 'PredecessorID', 'SuccessorID', 'Type');

            $table = "GanttDependencies";
        } else {
            $columns = array('ID', 'ParentID', 'OrderID', 'name', 'start_date', 'due_date', 'progress', 'Expanded', 'Summary');

            $table = "GanttTasks";
        }

    Is there any possible to put there a select with some conditions?

    For example I would like to select all the records where id = 10.
    Can you help my in this?

  2. Dimitar
    Admin
    Dimitar avatar
    225 posts

    Posted 31 Oct Link to this post

    Hello Valerius,

    A complete example on how to configure the Gantt widget dependencies with the PHP Wrappers can be found in this part of the Remote Binding Documentation. There, you will find a sample approach on how to implement the create, read, update and destroy end-points for the Gantt widget:
    <?php
        if ($type == 'read') {
            $statement = $db->prepare('
               SELECT *,
               strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start,
               strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End
               FROM GanttTasks
            ');
            $statement->execute();
            $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        }
    ?>

    I hope this helps.

    Regards,
    Dimitar
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Valerius
    Valerius avatar
    14 posts
    Member since:
    Oct 2017

    Posted 05 Nov in reply to Dimitar Link to this post

    Hi Dimitar, One thing. Could you please put this code:

    <?php
        if ($type == 'read') {
            $statement = $db->prepare('
               SELECT *,
               strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start,
               strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End
               FROM GanttTasks
            ');
            $statement->execute();
            $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        }
    ?>

     

    to the template below: I would like to check if I'm doing it right 'cause I'm using db to show the gantt results.Can you please check Yours example, and tell my where exactly I could use above condition?

     

    <!DOCTYPE html>
    <html>
    <head>
        <title></title>
        <link rel="stylesheet" href="styles/kendo.common.min.css" />
        <link rel="stylesheet" href="styles/kendo.default.min.css" />
        <link rel="stylesheet" href="styles/kendo.default.mobile.min.css" />
     
        <script src="js/jquery.min.js"></script>
        <script src="js/kendo.all.min.js"></script>
         
     
    </head>
    <body>
    <?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'];
     
        $operation = $_GET['operation'];
     
        if ($type == "dependency") {
            $columns = array('ID', 'PredecessorID', 'SuccessorID', 'Type');
     
            $table = "GanttDependencies";
        } else {
            $columns = array('ID', 'ParentID', 'OrderID', 'Title', 'Start', 'End', 'PercentComplete', 'Expanded', 'Summary');
     
            $table = "GanttTasks";
        }
     
        switch($operation) {
            case 'create':
                $result = $result->create($table, $columns, $request, 'ID');
                break;
            case 'update':
                $result = $result->update($table, $columns, $request, 'ID');
                break;
            case 'destroy':
                $result = $result->destroy($table, $request, 'ID');
                break;
            default:
                $result = $result->read($table, $columns, $request);
                break;
        }
     
        echo json_encode($result, JSON_NUMERIC_CHECK);
     
        exit;
    }
     
    // tasks datasource
    $transport = new \Kendo\Data\DataSourceTransport();
     
    $create = new \Kendo\Data\DataSourceTransportCreate();
     
    $create->url('index.php?type=task&operation=create')
         ->contentType('application/json')
         ->type('POST');
     
    $read = new \Kendo\Data\DataSourceTransportRead();
     
    $read->url('index.php?type=task&operation=read')
         ->contentType('application/json')
         ->type('POST');
     
    $update = new \Kendo\Data\DataSourceTransportUpdate();
     
    $update->url('index.php?type=task&operation=update')
         ->contentType('application/json')
         ->type('POST');
     
    $destroy = new \Kendo\Data\DataSourceTransportDestroy();
     
    $destroy->url('index.php?type=task&operation=destroy')
         ->contentType('application/json')
         ->type('POST');
     
    $transport->create($create)
              ->read($read)
              ->update($update)
              ->destroy($destroy)
              ->parameterMap('function(data) {
                  return kendo.stringify(data);
              }');
     
    $taskModel = new \Kendo\Data\DataSourceSchemaModel();
     
    $idField = new \Kendo\Data\DataSourceSchemaModelField('id');
    $idField->type('number')
            ->from('ID')
            ->nullable(true);
     
    $orderIdField = new \Kendo\Data\DataSourceSchemaModelField('orderId');
    $orderIdField->from('OrderID')
            ->type('number');
     
    $parentIdField = new \Kendo\Data\DataSourceSchemaModelField('parentId');
    $parentIdField->from('ParentID')
            ->defaultValue(null)
            ->type('number');
     
    $startField = new \Kendo\Data\DataSourceSchemaModelField('start');
    $startField->from('Start')
            ->type('date');
     
    $endField = new \Kendo\Data\DataSourceSchemaModelField('end');
    $endField->from('End')
            ->type('date');
     
    $titleField = new \Kendo\Data\DataSourceSchemaModelField('title');
    $titleField->from('Title')
            ->defaultValue('')
            ->type('string');
     
    $percentCompleteField = new \Kendo\Data\DataSourceSchemaModelField('percentComplete');
    $percentCompleteField->from('PercentComplete')
            ->type('number');
     
    $summaryField = new \Kendo\Data\DataSourceSchemaModelField('summary');
    $summaryField->from('Summary')
            ->type('boolean');
     
    $expandedField = new \Kendo\Data\DataSourceSchemaModelField('expanded');
    $expandedField->from('Expanded')
            ->defaultValue(true)
            ->type('boolean');
     
    $taskModel->id('id')
        ->addField($idField)
        ->addField($parentIdField)
        ->addField($orderIdField)
        ->addField($startField)
        ->addField($endField)
        ->addField($titleField)
        ->addField($percentCompleteField)
        ->addField($summaryField)
        ->addField($expandedField);
     
    $schema = new \Kendo\Data\DataSourceSchema();
    $schema->model($taskModel)
        ->data("data");
     
    $tasks = new \Kendo\Data\DataSource();
     
    $tasks->transport($transport)
        ->schema($schema)
        ->batch(false);
     
    // dependencies datasource
    $transport = new \Kendo\Data\DataSourceTransport();
     
    $create = new \Kendo\Data\DataSourceTransportCreate();
     
    $create->url('index.php?type=dependency&operation=create')
         ->contentType('application/json')
         ->type('POST');
     
    $read = new \Kendo\Data\DataSourceTransportRead();
     
    $read->url('index.php?type=dependency&operation=read')
         ->contentType('application/json')
         ->type('POST');
     
    $update = new \Kendo\Data\DataSourceTransportUpdate();
     
    $update->url('index.php?type=dependency&operation=update')
         ->contentType('application/json')
         ->type('POST');
     
    $destroy = new \Kendo\Data\DataSourceTransportDestroy();
     
    $destroy->url('index.php?type=dependency&operation=destroy')
         ->contentType('application/json')
         ->type('POST');
     
    $transport->create($create)
              ->read($read)
              ->update($update)
              ->destroy($destroy)
              ->parameterMap('function(data) {
                  return kendo.stringify(data);
              }');
     
    $dependenciesModel = new \Kendo\Data\DataSourceSchemaModel();
     
    $idField = new \Kendo\Data\DataSourceSchemaModelField('id');
    $idField->from('ID')
            ->type('number');
     
    $typeField = new \Kendo\Data\DataSourceSchemaModelField('type');
    $typeField->from('Type')
            ->type('number');
     
    $predecessorIdField = new \Kendo\Data\DataSourceSchemaModelField('predecessorId');
    $predecessorIdField->from('PredecessorID')
            ->type('number');
     
    $successorIdField = new \Kendo\Data\DataSourceSchemaModelField('successorId');
    $successorIdField->from('SuccessorID')
            ->type('number');
     
    $dependenciesModel->id('id')
        ->addField($idField)
        ->addField($typeField)
        ->addField($predecessorIdField)
        ->addField($successorIdField);
     
    $schema = new \Kendo\Data\DataSourceSchema();
    $schema->model($dependenciesModel)
        ->data("data");
     
    $dependencies = new \Kendo\Data\DataSource();
     
    $dependencies->transport($transport)
        ->schema($schema)
        ->batch(false);
     
    // columns
    $idColumn = new \Kendo\UI\GanttColumn();
    $idColumn->field("id")
             ->title("ID")
             ->width(50);
     
    $titleColumn = new \Kendo\UI\GanttColumn();
    $titleColumn->field("title")
                ->title("Title")
                ->editable(true)
                ->sortable(true);
     
    $startColumn = new \Kendo\UI\GanttColumn();
    $startColumn->field("start")
                ->title("Start Time")
                ->format("{0:MM/dd/yyyy}")
                ->width(100)
                ->editable(true)
                ->sortable(true);
     
    $endColumn = new \Kendo\UI\GanttColumn();
    $endColumn->field("end")
              ->title("End Time")
              ->format("{0:MM/dd/yyyy}")
              ->width(100)
              ->editable(true)
              ->sortable(true);
     
    // gantt
    $gantt = new \Kendo\UI\Gantt('gantt');
    $gantt->dataSource($tasks)
          ->dependencies($dependencies)
          ->height(700)
          ->addView(
              'day',
              array('type' => 'week', 'selected' => true),
              'month'
          )
          ->addColumn($idColumn, $titleColumn, $startColumn, $endColumn)
          ->showWorkHours(false)
          ->showWorkDays(false)
          ->snap(false);
    ?>
     
    <?php
    echo $gantt->render();
    ?>
     
     
    </body>
    </html>
  4. Valerius
    Valerius avatar
    14 posts
    Member since:
    Oct 2017

    Posted 06 Nov in reply to Valerius Link to this post

    Guys, I'm looking for the solution, and cannot find anything right. You're showing a full examples but it's not related to the documentation I can find on your pages.... I cannot find nothing which can help me to SELECT DATABASE WHERE basing on Your examples:http://demos.telerik.com/php-ui/gantt/index

    My last question here. Is there any possible to SELECT database with some conditions like:
    SELECT ID, ParentID, OrderID, Title, Start, End, PercentComplete, Expanded, Summary WHERE ID = 100
    Can I use this example, can you help me to connect these two things?

    http://demos.telerik.com/php-ui/gantt/index,
    and put there some conditions I've wrote above?

  5. Dimitar
    Admin
    Dimitar avatar
    225 posts

    Posted 07 Nov Link to this post

    Hello Valerius,

    For a complete example with detailed description and steps on how to configure the Gantt widget to work with remote data, you can refer to the following Gantt Remote Binding Documentation.

    In the code sample provided, the tasks and dependencies DataSources are configured, but not the remote end-points that return the JSON data. Below you can find sample steps to follow (more detailed exmplanation is available in the above documentation article):

    1) Configure the Tasks DataSource:
    <?php
    $taskTransport = new \Kendo\Data\DataSourceTransport();
     
    // Configure the remote service - a PHP file called 'tasks.php'
    // The query string parameter 'type' specifies the type of CRUD operation 
    $taskRead = new \Kendo\Data\DataSourceTransportRead();
     
    $taskRead->url('tasks.php?type=read')
    ->contentType('application/json')
    ->type('POST');   

       .... 
    ?>

    2)  Create file performing CRUD on tasks:
    <?php
    header('Content-Type: application/json');
         
    $request = json_decode(file_get_contents('php://input'));
    $result = new DataSourceResult('sqlite:..//sample.db');
    $type = $_GET['type']; 
    $data = null;
         
    if ($type == 'read') {
    // The 'read' method accepts table name, array of columns to select and request parameters as array
    $data = $result->read('GanttTasks',
    array('Title', 'strftime(\'%Y-%m-%dT%H:%M:%SZ\', Start) as Start', 'strftime(\'%Y-%m-%dT%H:%M:%SZ\', End) as End', 'ID', 'OrderID', 'ParentID', 'PercentComplete', 'Summary', 'Expanded'),
    $request);
        }
         
    header('Content-Type: application/json');
    echo json_encode($data, JSON_NUMERIC_CHECK);
    ?>


    Regards,
    Dimitar
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  6. Valerius
    Valerius avatar
    14 posts
    Member since:
    Oct 2017

    Posted 08 Nov in reply to Dimitar Link to this post

    Hi Dimitar, Can you please explain why demo samples (of gantt) are totally different like examples in the documentation? This dosen't works at all...

    Only what I've tried to do is to add some simple condition for tasks of gannt basing on the demo...(I've put here the demo example from your site three times.)
    http://demos.telerik.com/php-ui/gantt/index

    All the answer I've got here relates to the documentaion which is fully misundersttanding for me, and I don't really know what's going on there.

    Or you're don't understand what I'm asking for or kendo is to hard to me...
    Thanks, I'm not buy this.
  7. Nencho
    Admin
    Nencho avatar
    1671 posts

    Posted 10 Nov Link to this post

    Hello Valerius,

    I am really sorry to see your frustration about the difference in the resources that we have on the matter. I understand it, because Indeed you are right and there is a difference between the documentation and our demo site. There is some lack of examples, in order to achieve a parity in a code usage demonstration point of view. We will update our documentation, in order to provide accurate examples in the scenario that we discuss in this case.

    As for the issue itself - in order to query the result set with specific Where clause, you can use the DataSourceResult object. Actually this object is created, in order to ease such queries. This could be done by adding a DataSourceFilterItem. These filter items are interpreted as where clause parameters. In other words, you can create such item, define your where clause (ID=[value]) and add this item to the dataSource transport method:

    $tasks = new \Kendo\Data\DataSource();
     
    $filter = new \Kendo\Data\DataSourceFilterItem();
    $filter-$filter->field('ID')
        ->operator('eq')
        ->value('7');
     
    $tasks->transport($transport)
        ->schema($schema)
        ->addFilterItem($filter)
        ->serverFiltering(true)
        ->batch(false);

    Also, note that the serverFiltering should be enabled, so that the filter could be correctly applied.

    Having in mind that you use the index demo for testing purposes, the above-demonstrated implementation should render only the first task in the Gantt.

    Below you can find the documentation article, describing the DataSourceFilterItem:

    https://docs.telerik.com/kendo-ui/api/php/Kendo/Data/DataSourceFilterItem


    Regards,
    Nencho
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  8. Valerius
    Valerius avatar
    14 posts
    Member since:
    Oct 2017

    Posted 14 Nov in reply to Nencho Link to this post

    Thanks Nencho for a tip., I've done this anyway.
Back to Top