Following Server-side CRUD Tutorial and Not Working

[anna tang]

Hi, I followed the server side crud tutorial here: https://www.htmlelements.com/angular/demos/kanban/server-side-crud/

My problem is I can’t get the hard-coded tasks to load on start up. It works fine on the sample Stackblitz that was provided, but when replicated locally I only see the query in the logs but not the actual card.

Pictures here: https://gyazo.com/666e93b42e79c22f2bccc6ac7007fb52 https://gyazo.com/bf21bf423beb781692412a04ac3455f8

app.component.ts

import { AfterViewInit, Component, OnInit, ViewChild, ViewEncapsulation } from '@angular/core';
import {  KanbanColumn, KanbanComponent, KanbanUser, } from 'smart-webcomponents-angular/kanban';

// Component Documentation
// https://www.htmlelements.com/docs/kanban-api/#toc-kanbancolumn-interface
// https://www.htmlelements.com/angular/demos/kanban/overview/
declare global {
  interface Window {
    demoServer: any;
    query: HTMLElement;
    data: any;
    alasql: any;
  }
}
@Component({
  selector: 'app-sprint-board',
  templateUrl: './sprint-board.component.html',
  styleUrls: ['./sprint-board.component.css'],
  encapsulation: ViewEncapsulation.None
})

export class SprintBoardComponent implements AfterViewInit, OnInit {
  @ViewChild('kanban', { read: KanbanComponent, static: false }) 
  kanban: KanbanComponent;

  addNewButton = true;
  editable = true;
  taskActions = true;
  taskDue = true;
  taskProgress = true;
  userList = true;

  currentUser = '0'

  dataSource = new window.Smart.DataAdapter({
    virtualDataSource: function (resultCallbackFunction: any, details: any) {
      console.log('Calling datasource')
      if (details.action === 'dataBind') {
        // inits the demo server.
        window.demoServer = DemoServer();
        const result = window.demoServer.getData(details);
        // logs the data below the component.
        LogData(result.data);
        resultCallbackFunction({
          dataSource: result.data,
          lastId: result.lastId,
          virtualDataSourceLength: result.length,
        });
      } else {
        const result = window.demoServer.getData(details);
        // logs the data below the component.
        LogData(result.data);
        resultCallbackFunction({
          dataSource: result.data,
          lastId: result.lastId,
          virtualDataSourceLength: result.length,
        });
      }
    },
    dataFields: [
      'id: number',
      'status: string',
      'text: string',
      'tags: string',
      'priority: string',
      'progress: number',
      'startDate: date',
      'dueDate: date',
      'userId: string',
      'color: string'
    ],
    id: 'id',
  });

  users: KanbanUser[] = [
      { id: '0', name: 'Anna', image: 'https://cdn.discordapp.com/avatars/198645298753568768/af36c16139cf3a6adddf364603428f07.webp?size=240'},
      { id: '1', name: 'Harry', image: 'https://cdn.discordapp.com/avatars/714719074738765874/71c07878377ad1567c2ef80039eaa636.webp?size=240'},
  ];

  columns: KanbanColumn[] = [
      { label: 'Backlog', dataField: 'backLog' },
      { label: 'Ongoing Tasks', dataField: 'onGoing'},
      { label: 'Completed Tasks', dataField: 'completed'},
      { label: 'Blocked', dataField: 'blocked'}
  ];

  constructor() {}
  // constructor() {}
  
  ngOnInit():void {

  }

  ngAfterViewInit(): void {
    this.init();
  }

  init(): void {}
  
  // textTemplate -> function that provides templates for formatting task cards 
  textTemplate = (settings: { data: any, task: HTMLDivElement, text: string, template?: string }) => {
    //   const data = settings.data,
    //   text = settings.text;
      
    //   if (data.priority === 'high' && data.status !== 'done') {
    //     data.color = 'orangered';
    //   }
    //   if (data.checklist) {
    //     let toComplete = '';
    //     data.checklist.forEach((subtask: { completed: boolean, text: string }) => {
    //         if (!subtask.completed) {
    //             toComplete += <code><li>${subtask.text}</li></code>;
    //         }
    //     });
    //     if (toComplete) {
    //         toComplete = <code><br/><br/><p>Remaining Subtasks:</p><ul>${toComplete}</ul></code>;
    //     }

    //     settings.text = <code>${text}${toComplete}</code>;
    // }
  }

}

  function DemoServer() {
    window.alasql.options.cache = true;
    try {
      createTable();
    } catch (e) {
      alert(e)
    }
    // createTable();
    return {
      getData: function (request: any) {
        const queryResult = executeSql(request)
        console.log(<code>queryResult.data ${queryResult.data}</code>)
        return {
          data: queryResult.data,
          lastId: queryResult.lastId,
          length: queryResult.length,
        };
      },
    };
  }

  function createTable() {
    /* Tasks table */
    executeQuery(
      'CREATE TABLE Tasks (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, status NVARCHAR(255), text NVARCHAR(255), tags NVARCHAR(255), priority ENUM("low", "normal", "high"), progress TINYINT, startDate DATE, dueDate DATE, userId INTEGER, color NVARCHAR(255))'
    );
    executeQuery(
      'INSERT INTO Tasks (status, text, tags, priority, progress, startDate, dueDate, userId) VALUES ("done", "Research", "initial", "normal", 100, "2020-10-17", "2020-11-17", 2)'
    );
  }

  function LogData(data: any, logElementId: string = 'dataLog') {
    const log = document.getElementById(logElementId);
    let content = '<table>';
    for (let i = 0; i < data.length; i++) {
      let row = '<tr>';
      if (i === 0) {
        for (let column in data[i]) {
          row += '<td>' + column + '</td>';
        }
        row += '</tr>';
      }
      content += row;
      row = '<tr>';
      for (let column in data[i]) {
        row += '<td>' + data[i][column] + '</td>';
      }
      row += '</tr>';
      content += row;
    }
    content += '</table>';
    log.innerHTML = content;
  }

function executeSql(request: any) {
  let lastId;
  window.query.innerHTML = '';

  // Deletes a task
  if (request.action === 'remove') {
    const removeQuery = request.query['remove'],
      sqlDeleteQuery = 'DELETE FROM Tasks' + removeQuery,
      taskid = /WHERE id=(.+)/.exec(removeQuery)[1];
    window.alasql(sqlDeleteQuery);
    window.query.innerHTML = sqlDeleteQuery;
  }

  // Adds a task.
  else if (request.action === 'add') {
    const sqlAddQuery = 'INSERT Tasks' + request.query['add'];
    window.alasql(sqlAddQuery);
    lastId = window.alasql.tables['Tasks'].identities.id.value;
    window.query.innerHTML = sqlAddQuery;
  }

  // Updates a task
  else if (request.action === 'update') {
    const updateQuery = request.query['update'],
      taskid = /WHERE id=(.+)/.exec(updateQuery)[1];
    const sqlUpdateQuery = 'UPDATE Tasks' + updateQuery;
    window.alasql(sqlUpdateQuery);
    window.query.innerHTML = sqlUpdateQuery;
  }

  // SQL Select query.
  const sql =
    'SELECT * FROM Tasks' +
    request.query['where'] +
    request.query['groupBy'] +
    request.query['orderBy'] +
    request.query['limit'];

  // SQL Records Count query.
  const sqlCount =
    request.grouping.length === 0
      ? 'SELECT COUNT(*) as length from Tasks ' + request.query['where']
      : 'SELECT COUNT(DISTINCT ' +
        request.grouping[0] +
        ') as length from Tasks ' +
        request.query['where'];
  const result = window.alasql(sql);
  const length = window.alasql(sqlCount)[0].length;

  if (window.query.innerHTML === '') {
    window.query.innerHTML = sql;
  }
  console.log(<code>window.query.innerHTML: ${window.query.innerHTML}</code>)
  return { data: result, lastId: lastId, length: length };
}

function executeQuery(query: string) {
  return window.alasql(query);
}

app.component.html

<div class="d-flex justify-content-center">
    <div class="p-2"><h1>Sprint Board</h1></div>
</div>
<smart-kanban #kanban id="kanban" 
    [addNewButton]="addNewButton"
    [currentUser]="currentUser"
    [dataSource]="dataSource"
    [editable]="editable"
    [taskActions]="taskActions"
    [taskDue]="taskDue"
    [textTemplate]="textTemplate"
    [userList]="userList"
    [users]="users"
    [columns]="columns"
></smart-kanban>

<br />
<br />
<div id="dataSource">
    <h3>SQL Query</h3>
    <div id="query"></div>
    <br />
    <br />
    <h3>Data</h3>
    <div id="dataLog"></div>
    <br />
</div>

app.component.css

/* CSS Documentation: https://www.htmlelements.com/docs/kanban-css/ */

html,
body,
app-root,
#kanban {
    width: 100%;
    height: 100%;
}

.smart-kanban {
    padding-bottom: 2em;
    padding-right: 3em;
    padding-left: 3em;
}

.smart-kanban-column-content {
    background-color: rgb(240, 240, 240);
    border-radius: 0.5em;
}

.smart-kanban-task {
    border-radius: 0.2em;
    max-width: 95%;
    margin: auto;
}

#dataSource {
    padding-right: 3em;
    padding-left: 3em;
}

Thanks!

[ivanpeevski]

Hi anna,

The reason the task doesn’t appear is beacause it is set to a column that doesn’t exist.

As you can see in your code, the Kanban Columns are backLog, onGoing, completed and blocked. But in the INSERT query, the task’s status is set to “done”.
If you change “done” to any of the columns, for example “completed”, you will see that the task will appear.

If you have any other questions, please do not hesitate to contact us again!
Best Regards,
Ivan Peevski
Smart UI Team
https://www.htmlelements.com/

[anna tang]

Thank you Ivan for catching that detail! One last question – I’m noticing that the card isn’t being dragged properly here: https://gyazo.com/e7f3160b5019921726bd6bf5a7369ccf

Any leads as to why this might be the case?

Thanks again!

[anna tang]

Also how would I go about storing checklist in the Tasks table that was created?

[ivanpeevski]

Hello anna,

Regarding the first question, I was not able to reproduce this problem.
Could you please check if the issue appears in this demo: https://github-r8z6a4.stackblitz.io
If it does, please share your web browser version.

Regarding the second question, can you please clarify what you mean by “storing”?
Our Kanban component supports exporting the current state of the kanban in an external file – pdf, html, json, xlsx etc. (You can see demo here)
If you mean getting or storing the current state(for example which tasks belong to which columns), you can use the “getState()” method, have a look at the demo here(by clicking on the “Log State” button, you will see the current tasks in the console)

If you have any other questions, please do not hesitate to contact us again!
Best Regards,
Ivan Peevski
Smart UI Team
https://www.htmlelements.com/

[anna tang]

Hi Ivan,

The issue does not appear in your linked demo, but my browser version is Google Chrome Version 96.0.4664.93 (Official Build) (64-bit). I`m also using Bootstrap in my other Angular components as well, could that possibly mess up the issue? This is the version of the widgets that I’m using:

    "smart-webcomponents": "^10.0.0",
    "smart-webcomponents-angular": "^9.2.13",

As for the latter question, I was asking how I would store the checklists in the Tasks database created with this query here:

    executeQuery(
CREATE TABLE Tasks (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, status NVARCHAR(255), text NVARCHAR(255), 
      tags NVARCHAR(255), priority ENUM("low", "normal", "high"), progress TINYINT, startDate DATE, dueDate DATE, 
      userId INTEGER, color NVARCHAR(255))

);`
Currently in my dataSourceAdapter, I have it set like so:

  dataSource = new window.Smart.DataAdapter({
        ...
    dataFields: [
        ...
      'checklist: { completed: boolean, text: string }[]'
    ],
    id: 'id',
  });

When creating a task card with subtasks, the SQL Query displays it like so: checklist='[{"text":"subtask 1","completed":false},{"text":"subtask 2","completed":true}]' but I haven’t been able to replicate the data type of checklist in executeQuery(). I hope that was a better explanation, thanks!

• This reply was modified 2 years, 4 months ago by anna tang.

[ivanpeevski]

Hi anna,

Thank you for the additional information! The CSS issue is caused by the max-width property of .smart-kanban-task. Instead, I suggest setting left and right padding to the column-content element and you can achieve the same visual effect:

.smart-kanban-column-content {
background-color: rgb(240, 240, 240);
border-radius: 0.5em;
padding: 0 4%;
}

.smart-kanban-task {
border-radius: 0.2em;
margin: auto;
}

Regarding storing the checklist, you can have a look at the way we store comments in our tutorial demo. The idea with checklist is exactly the same. You need to create a new table Checks, with the check properties – text, completed, and an additional taskId field. Then everytime you execute a SELECT Query for Tasks, you need to execute an additional SELECT Query for Checks where taskId matches with the id of the task you are selecting. The array you receive from the second query, you need to set as a value to the task.checklist property. The same goes for Insert/Delete Queries. When you execute an UPDATE Query, you first need to delete the checks and then insert the new ones.
Have a look at an example with checklist here: demo with checklist

If you have any other questions, please do not hesitate to contact us again.
Best Regards,
Ivan Peevski
Smart UI Team
https://www.htmlelements.com/

 

[Author]

Posts