Displaying data from multiple tables

[VBScab]

I am building a timesheet system and will use the scheduler to allow users to report time spent on a task. A task is part of a project and the project “belongs” to a client.

The back-end database is as relational as I can make it. The tables are linked by “common” columns. So:

The Timesheet table has a TaskID column. That TaskID is obtained from the Task table.

The Task table has a ProjectID column which is obtained from the Project table.

The Project table has a ClientID column. The ClientID comes from the Client table.

There are other ID columns which are used, for example, to flag a task’s status.

So, I will need to create a custom lightbox to display the client, project and task in drop-downs, which is fine (I think!) However, I’m struggling to cope with how to initially display the data for completed entries (this is test data that I’ve created by hand), as it comes from these disparate sources. I looked at the ‘mix’ example in the documentation but it only deals with 2 tables.

EDIT:
About the custom lightbox…I said “which is fine” but…it isn’t. I’m lost.

The lightbox should look something like this example docs.dhtmlx.com/scheduler/snippet/34161a15

However, on selecting ‘Type’ (in my case, this will be ‘Client’) I need the next drop-down to display only that client’s projects. Then, once a project is selected, the 3rd drop-down should display only the tasks associated with that project.

Please bear in mind that my JavaScript knowledge is limited but I’m reasonable at looking at working examples and figuring stuff out, provided it’s not too obfuscated.

[VBScab]

OK! I found ‘26_linked_selects_in_lightbox.html’ in the samples folder so I think I can cobble something together from that. Three levels is going to be a challenge but hey, ho! “Faint heart…” and all that!

[Aliaksandr]

Hello,
actually, doing what you want should be relatively simple.
Firstly you need to load everything you need to the client (a common approach here to load events/projects/clients/tasks to the client-side as plain lists and combine everything you need with client side code, instead of joining tables on a backend) - you can load everything in a single request that can be parsed via scheduler.load docs.dhtmlx.com/scheduler/data_f … ollections - you’ll have array of events in “data” property, and arrays of users/projects/tasks in “collections”

Then on the client you’ll use collections of projects/clients/tasks to populate lightbox selects and text description of events.
In order to have linked dropdowns you can capture ‘change’ event of master dropdowns (client and project) and when any of those changes - you get dependent dropdowns and manually populate them with options.

Please check that demo:
docs.dhtmlx.com/scheduler/snippet/17dff4bd

Data structure i used:
Event:
{ “id”: 1, “start_date”: “2017-03-27 03:00”, “end_date”: “2017-03-27 07:00”, “text”:“Task A-12458”, “client_id”:1, “project_id”: 11, “task_id”: 4}
Client:
{“key”:1,“label”:“Client 1”}

• use “key” and “label” for id/name fields so such items can be loaded into lightbox select without any additional postprocessing docs.dhtmlx.com/scheduler/select.html
  Project:
  {“key”:1,“label”:“Project 1.1”,“client”:1}
• same reasoning for “key” and “label”. “client” - client id
  Task:
  {“key”:1,“label”:“Task 1.1.1”,“project”:1,“client”:1}
• similar to above.

You can find the test data at the end of “JS Code” section of demo

Related docs:
docs.dhtmlx.com/scheduler/data_f … ollections
docs.dhtmlx.com/scheduler/api__s … rlist.html
docs.dhtmlx.com/scheduler/api__s … ction.html

please tell me if you have any questions regarding demo, overall approach or anything

[VBScab]

Wow! That was a lot more comprehensive than I was expecting! Thank you so much.

If I may be so bold, however…

• Is there not a performance hit with downloading everything to the client side? Or is this a little like what I’m used to with ADO’s GetRows method (I’m a classic ASP dinosaur, as you can probably tell by now)?
• I have played with the ‘events.php’ file, ‘scheduler.load’ and ‘var dp = new dataProcessor…’ etc., etc. so that it accesses the MySQL d/b instead of ‘events.xml’ but that example accesses only one table. I have also played around with the presentation so now the task will be the event and I’ll need only 2 drop-downs: for ‘Client’ and ‘Project’. These tables have these structures:

‘Client’
ClientID, Name, SiteID, Description,URL

‘Project’
ProjectID, ClientID,Name,Description,StartDate,EndDate,Deadline,StatusID,ProjectLeaderID

My question, then, is - how do I alter the sample code so that it accesses these tables and populates the drop-downs with the respective ‘Name’ field and assigns the respective ID as the page element’s value? For now, the Project dates are immaterial to what I’m doing. They’ll be used in a reporting module at some point.

[Aliaksandr]

What backend do you use for this app, is php or do you write it in asp?

• Is there not a performance hit with downloading everything to the client side? Or is this a little like what I’m used to with ADO’s GetRows method (I’m a classic ASP dinosaur, as you can probably tell by now)?

Weell, it depends on number of projects/clients/tasks and how it will change over time. But I don’t think it could create any noticeable overhead on the client.

Although, there are couple things you’ll need to be aware of

1. if you’ll be reading whole scheduler events table, you’ll start getting very noticeable delay for data loading after some time. I.e. if your users generate couple thousands of events each month, and you load all events at once - after several months client will be loading couple of MBs of events in order to start calendar.
   It is usually solved by enabling dynamic loading:
   docs.dhtmlx.com/scheduler/loadin … micloading

• scheduler will send date boundaries to the backend so you’ll load events for required data ranges on demand
  GET datasource.asp?from=2017-10-01&to=2018-01-01
  related demo docs.dhtmlx.com/scheduler/sample … abase.html
  If you use dhtmlx connector it should work automatically, otherwise you’ll need to filter data by hand.

2. Please note how event labels are retrieved - screencast.com/t/13RiNbiUrKmr - in order to get label for a certain event we do linear search in clients/projects. Usually, the overhead is neglectable, in some conditions it may become noticeable. If you’ll see in browser profiler that event templates takes too much time you could optimize that code a bit, e.g. by replacing linear search with hash.

My question, then, is - how do I alter the sample code so that it accesses these tables and populates the drop-downs with the respective ‘Name’ field and assigns the respective ID as the page element’s value? For now, the Project dates are immaterial to what I’m doing. They’ll be used in a reporting module at some point.

Dropdowns and some other elements (i.e. Units view or Timeline view) always use ‘key’ and ‘label’ properties of the option object, they are hardcoded.
I would just use aliases for these columns on a backend -
I.e. “SELECT ClientID as key, Name as label FROM Client”
If you use php connector, it could look following:

[code]$clients = new JSONOptionsConnector($res, $dbtype);
$clients->render_table(“Client”,“ClientID”,“ClientID(key),Name(label)”);// select ClientID as key, Name as label from Client

$projects = new JSONOptionsConnector($res, $dbtype);
$projects->render_table(“Project”,“ProjectID”,“ProjectID(key),Name(label),ClientID(client)”);// select ProjectID as key, Name as label, ClientID as client from Project

$scheduler = new JSONSchedulerConnector($res, $dbtype);
$scheduler->set_options(“clients”, $clients);
$scheduler->set_options(“projects”, $projects);
$scheduler->render_table(“events”,“id”,“start_date,end_date,event_name,project_id,client_id”);[/code] Related sample in scheduler package - samples\01_initialization_loading\data\types_json.php

[VBScab]

What backend do you use for this app, is php or do you write it in asp?

It’s PHP. If I had the time and patience, I’d translate it all to ASP but life’s too short and I probably need to get into this new-fangled technology!

You’ve been so helpful: I really do appreciate it. I feel like I’m this close but I’m getting lost in translating between the code you’ve kindly written, what’s in the sample pages, etc. particularly with things like where I need to have ‘client_id’ versus ‘ClientID’.

So, if I might impinge on your good nature for what is hopefully the last time…the below results in an error message is a dialog box. I have added an ‘echo $sql’ statement to ‘db_mysqli.php’ in a futile attempt to see where things are going wrong. As you can see, we end up with two SELECT queries:

[code]SELECT TimesheetID,TaskID,StartDate,EndDate,UserAssignedToID,Notes,SubmittedOn,AuthorisedOn,AuthorisedBy,AuthoriseEmailSent FROM t_timesheet WHERE TaskID < ‘2017-02-27’ AND StartDate > '2017-02-20’SELECT ClientID,ClientID as key,Name as label FROM t_client

Fatal error: Uncaught exception ‘Exception’ with message ‘MySQL operation failed
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘key,Name as label FROM t_client’ at line 1’ in D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\db_mysqli.php:14
Stack trace:
#0 D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\db_common.php(755): MySQLiDBDataWrapper->query(‘SELECT `ClientI…’)
#1 D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\data_connector.php(315): DBDataWrapper->select(Object(DataRequestConfig))
#2 D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\scheduler_connector.php(202): JSONOptionsConnector->render()
#3 D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\scheduler_connector.php(165): JSONSchedulerConnector->fill_collections()
#4 D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\scheduler_connector.php(221): JSONSchedule in D:\Projects\Web\ASP\Timesheet\IPN\3.00\Scheduler\codebase\connector\db_mysqli.php on line 14
[/code]

This is my main page:

[code]<!doctype html>

Scheduler html, body{ margin:0px; padding:0px; height:100%; overflow:hidden; }

 

 

 

[/code]

This is ‘GetTasks.PHP’:

[code]<?php

require_once("…\codebase\connector\scheduler_connector.php"); // Include the connector file
require_once("…\codebase\connector\db_mysqli.php");
include (“ConnectToDatabase.php”);

$clients = new JSONOptionsConnector($timesheet_db, $dbtype);
$clients->render_table(“t_client”,“ClientID”,“ClientID(key),Name(label)”);// select ClientID as key, Name as label from t_client

$projects = new JSONOptionsConnector($timesheet_db, $dbtype);
$projects->render_table(“t_project”,“ProjectID”,“ProjectID(key),Name(label),ClientID(client)”);// select ProjectID as key, Name as label, ClientID as client from t_project

$tasks = new JSONOptionsConnector($timesheet_db, $dbtype);
$tasks->render_table(“t_task”,“TaskID”,“TaskID,ProjectID,Name,Description,StatusID,PriorityID”);

$scheduler = new JSONSchedulerConnector($timesheet_db, $dbtype);
$scheduler->set_options(“clients”, $clients);
$scheduler->set_options(“projects”, $projects);
$scheduler->set_options(“tasks”, $tasks);
$scheduler->render_table(“t_timesheet”,“TimesheetID”,“TaskID,StartDate,EndDate,UserAssignedToID,Notes,SubmittedOn,AuthorisedOn,AuthorisedBy,AuthoriseEmailSent”);

?>[/code]

This is ‘ConnectToDatabase.PHP’ (for completeness/clarity):

[code]<?php
$dbtype = “MySQLi”;

// ID and password obfuscated here!
$timesheet_db = mysqli_connect(“localhost”,“xxxxxxxxxx”,“yyyyyyyy”, “timesheet”); // Connect to the server hosting the database
if (!$timesheet_db) {
echo “Error: Unable to connect to ‘timesheet’ database.” . PHP_EOL;
echo "Number: " . mysqli_connect_errno() . PHP_EOL;
echo "Description: " . mysqli_connect_error() . PHP_EOL;
exit;
}

$conn 			= new SchedulerConnector($timesheet_db, $dbtype);						// Initialise the connector object
if (!$conn) {
    echo "Error: Unable to initialise connection to 'timesheet' database." . PHP_EOL;
    echo "Number: " . mysqli_connect_errno() . PHP_EOL;
    echo "Description: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

?>[/code]

What we’re supposed to end up with is:

• when dragging for a new task, the lightbox should have a drop-down labelled “Client” with all possible clients listed from the ‘t_client’ table. When selecting a client, that client’s projects will populate the 2nd drop-down, “Project”, with all of that client’s projects listed. There should then be a textarea, “Task”, ready to accept the name of the new task.
• when editing an existing task, the Client and Project drop-downs will display the client name and project name associated with that task. It would be useful if ALL the other clients and projects were listed, with the associated item pre-selected. This will allow users to reallocate tasks to another client or project.

A feature I want to add is an ability to copy a task as, for the target audience I have in mind, a week (possibly more than a week, too) will be taken up with the same task, albeit with slightly differing start and end times. It would be really useful to have that as a context-menu item so if you have any guidance on how that functionality might be incorporated, that would be very much appreciated. Pro temp, I could probably use a button instead.

[VBScab]

Good news, on the context-menu request. I found this http://forum.dhtmlx.com/viewtopic.php?f=6&t=37213, which I will attempt to adapt, once I get the rest of this working.

[VBScab]

Commenting-out line 20 of ‘GetTasks.PHP’:

$scheduler->render_table("t_timesheet","TimesheetID","TaskID,StartDate,EndDate,UserAssignedToID,Notes,SubmittedOn,AuthorisedOn,AuthorisedBy,AuthoriseEmailSent"); results in an empty message box so there’s obviously something wrong with that.

However, the 2 drop-downs are not populated with data from the tables, although I see no errors.

[Aliaksandr]

Hi,
regarding the error, looks like it was my mistake, sorry
There is an sql syntax error on ‘Select something as key FROM …’ since ‘key’ is a keyword in mysql. I was checking my code in sqlite, so it worked correctly for me.
As a solution, you can replace $connector->render_table with render_sql calls and escape the keyword, i.e.:

[code]$clients = new JSONOptionsConnector($timesheet_db, $dbtype);
$clients->render_sql(“SELECT ClientID AS key, Name AS label FROM t_client”,“key”,“key,label”);

$projects = new JSONOptionsConnector($timesheet_db, $dbtype);
$projects->render_sql(“SELECT ProjectID AS key, Name AS label, ClientID AS client FROM t_project”,“key”,“key,label,client”);[/code]

As for event.ClientID, I can’t see where it’s selected. I suppose events are selected here:

$scheduler->render_table("t_timesheet","TimesheetID","TaskID,StartDate,EndDate,UserAssignedToID,Notes,SubmittedOn,AuthorisedOn,AuthorisedBy,AuthoriseEmailSent");

• and there is no ClientId column. If t_timesheet have this column, you need to add to column list in render_table. If it’s not, you’ll probably need to join tables after all. Can you send me a database schema (only related tables, test data would be also helpfull, but not required. You can send it in private messages) - i could check it locally. Otherwise, it’s getting a bit difficult to figure everything out

[VBScab]

Here is a SQL dump of the database. I’m happy to post it here, as it’s pretty much POC presently.

Some of the column data types may change, as they’re probably too large for what they’ll contain but that’s a tuning exercise. For now, I’d just like to get things working. I’m sure that once I see how this fits together - getting terminology and PHP syntax right in my head, for example - I’ll be able to carry on on my own. For now, once again, thank you so much for all your help! [code]CREATE DATABASE IF NOT EXISTS timesheet /*!40100 DEFAULT CHARACTER SET utf8 */;
USE timesheet;
– MySQL dump 10.13 Distrib 5.7.12, for Win64 (x86_64)

– Host: localhost Database: timesheet

---

– Server version 5.7.17-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=’+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

–
– Table structure for table t_client

DROP TABLE IF EXISTS t_client;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_client (
ClientID bigint(20) NOT NULL,
Name longtext NOT NULL,
SiteID bigint(20) NOT NULL,
Description longtext CHARACTER SET utf8mb4,
URL longtext CHARACTER SET utf8mb4,
PRIMARY KEY (ClientID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_client

LOCK TABLES t_client WRITE;
/*!40000 ALTER TABLE t_client DISABLE KEYS /;
INSERT INTO t_client VALUES (1,‘Aviva Investors’,1,NULL,NULL),(2,‘BOC’,1,NULL,NULL);
/!40000 ALTER TABLE t_client ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_clientsite

DROP TABLE IF EXISTS t_clientsite;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_clientsite (
ClientSiteID bigint(20) NOT NULL,
ClientID bigint(20) NOT NULL,
SiteName longtext CHARACTER SET utf8mb4,
Address1 longtext CHARACTER SET utf8mb4,
Address2 longtext CHARACTER SET utf8mb4,
City longtext CHARACTER SET utf8mb4,
County longtext CHARACTER SET utf8mb4,
Postcode longtext CHARACTER SET utf8mb4,
CountryID bigint(20) DEFAULT NULL,
ContactFirstName longtext,
ContactLastName longtext,
ContactEmail longtext,
ContactPhoneNumber longtext,
PRIMARY KEY (ClientSiteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_clientsite

LOCK TABLES t_clientsite WRITE;
/*!40000 ALTER TABLE t_clientsite DISABLE KEYS /;
INSERT INTO t_clientsite VALUES (1,1,‘Head Office’,‘1 Poultry’,NULL,‘London’,’’,‘EC2V 6ET’,826,NULL,NULL,NULL,NULL),(2,2,‘UK Head Office’,‘Surrey Research Park’,‘10 Priestley Road’,‘Guildford’,‘Surrey’,‘GU2 7XY’,826,NULL,NULL,NULL,NULL);
/!40000 ALTER TABLE t_clientsite ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_country

DROP TABLE IF EXISTS t_country;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_country (
CountryID char(3) NOT NULL,
CountryNameLowerCase varchar(50) CHARACTER SET utf8mb4 NOT NULL,
CountryNameUpperCase varchar(50) CHARACTER SET utf8mb4 NOT NULL,
ISO_ALPHA3 char(3) NOT NULL,
ISO_ALPHA2 char(2) NOT NULL,
PRIMARY KEY (CountryID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_country

LOCK TABLES t_country WRITE;
/*!40000 ALTER TABLE t_country DISABLE KEYS /;
INSERT INTO t_country VALUES (‘004’,‘Afghanistan’,‘AFGHANISTAN’,‘AFG’,‘AF’),(‘008’,‘Albania’,‘ALBANIA’,‘ALB’,‘AL’),(‘012’,‘Algeria’,‘ALGERIA’,‘DZA’,‘DZ’),(‘016’,‘American Samoa’,‘AMERICAN SAMOA’,‘ASM’,‘AS’),(‘020’,‘Andorra’,‘ANDORRA’,‘AND’,‘AD’),(‘024’,‘Angola’,‘ANGOLA’,‘AGO’,‘AO’),(‘028’,‘Antigua and Barbuda’,‘ANTIGUA AND BARBUDA’,‘ATG’,‘AG’),(‘031’,‘Azerbaijan’,‘AZERBAIJAN’,‘AZE’,‘AZ’),(‘032’,‘Argentina’,‘ARGENTINA’,‘ARG’,‘AR’),(‘036’,‘Australia’,‘AUSTRALIA’,‘AUS’,‘AU’),(‘040’,‘Austria’,‘AUSTRIA’,‘AUT’,‘AT’),(‘044’,‘Bahamas’,‘BAHAMAS’,‘BHS’,‘BS’),(‘048’,‘Bahrain’,‘BAHRAIN’,‘BHR’,‘BH’),(‘050’,‘Bangladesh’,‘BANGLADESH’,‘BGD’,‘BD’),(‘051’,‘Armenia’,‘ARMENIA’,‘ARM’,‘AM’),(‘052’,‘Barbados’,‘BARBADOS’,‘BRB’,‘BB’),(‘056’,‘Belgium’,‘BELGIUM’,‘BEL’,‘BE’),(‘060’,‘Bermuda’,‘BERMUDA’,‘BMU’,‘BM’),(‘064’,‘Bhutan’,‘BHUTAN’,‘BTN’,‘BT’),(‘068’,‘Bolivia (Plurinational State of)’,‘BOLIVIA, PLURINATIONAL STATE OF’,‘BOL’,‘BO’),(‘070’,‘Bosnia and Herzegovina’,‘BOSNIA AND HERZEGOVINA’,‘BIH’,‘BA’),(‘072’,‘Botswana’,‘BOTSWANA’,‘BWA’,‘BW’),(‘076’,‘Brazil’,‘BRAZIL’,‘BRA’,‘BR’),(‘084’,‘Belize’,‘BELIZE’,‘BLZ’,‘BZ’),(‘092’,‘British Virgin Islands’,‘BRITISH INDIAN OCEAN TERRITORY’,‘VGB’,‘IO’),(‘096’,‘Brunei Darussalam’,‘BRUNEI DARUSSALAM’,‘BRN’,‘BN’),(‘100’,‘Bulgaria’,‘BULGARIA’,‘BGR’,‘BG’),(‘104’,‘Myanmar’,‘MYANMAR’,‘MMR’,‘MM’),(‘108’,‘Burundi’,‘BURUNDI’,‘BDI’,‘BI’),(‘112’,‘Belarus’,‘BELARUS’,‘BLR’,‘BY’),(‘116’,‘Cambodia’,‘CAMBODIA’,‘KHM’,‘KH’),(‘120’,‘Cameroon’,‘CAMEROON’,‘CMR’,‘CM’),(‘124’,‘Canada’,‘CANADA’,‘CAN’,‘CA’),(‘132’,‘Cape Verde’,‘CAPE VERDE’,‘CPV’,‘CV’),(‘136’,‘Cayman Islands’,‘CAYMAN ISLANDS’,‘CYM’,‘KY’),(‘140’,‘Central African Republic’,‘CENTRAL AFRICAN REPUBLIC’,‘CAF’,‘CF’),(‘144’,‘Sri Lanka’,‘SRI LANKA’,‘LKA’,‘LK’),(‘148’,‘Chad’,‘CHAD’,‘TCD’,‘TD’),(‘152’,‘Chile’,‘CHILE’,‘CHL’,‘CL’),(‘156’,‘China’,‘CHINA’,‘CHN’,‘CN’),(‘170’,‘Colombia’,‘COLOMBIA’,‘COL’,‘CO’),(‘174’,‘Comoros’,‘COMOROS’,‘COM’,‘KM’),(‘175’,‘Mayotte’,‘MAYOTTE’,‘MYT’,‘YT’),(‘178’,‘Congo’,‘CONGO’,‘COG’,‘CG’),(‘180’,‘Democratic Republic of the Congo’,‘CONGO, THE DEMOCRATIC REPUBLIC OF THE’,‘COD’,‘CD’),(‘184’,‘Cook Islands’,‘COOK ISLANDS’,‘COK’,‘CK’),(‘188’,‘Costa Rica’,‘COSTA RICA’,‘CRI’,‘CR’),(‘191’,‘Croatia’,‘CROATIA’,‘HRV’,‘HR’),(‘192’,‘Cuba’,‘CUBA’,‘CUB’,‘CU’),(‘196’,‘Cyprus’,‘CYPRUS’,‘CYP’,‘CY’),(‘203’,‘Czech Republic’,‘CZECH REPUBLIC’,‘CZE’,‘CZ’),(‘204’,‘Benin’,‘BENIN’,‘BEN’,‘BJ’),(‘208’,‘Denmark’,‘DENMARK’,‘DNK’,‘DK’),(‘212’,‘Dominica’,‘DOMINICA’,‘DMA’,‘DM’),(‘214’,‘Dominican Republic’,‘DOMINICAN REPUBLIC’,‘DOM’,‘DO’),(‘218’,‘Ecuador’,‘ECUADOR’,‘ECU’,‘EC’),(‘222’,‘El Salvador’,‘EL SALVADOR’,‘SLV’,‘SV’),(‘226’,‘Equatorial Guinea’,‘EQUATORIAL GUINEA’,‘GNQ’,‘GQ’),(‘231’,‘Ethiopia’,‘ETHIOPIA’,‘ETH’,‘ET’),(‘232’,‘Eritrea’,‘ERITREA’,‘ERI’,‘ER’),(‘233’,‘Estonia’,‘ESTONIA’,‘EST’,‘EE’),(‘234’,‘Faeroe Islands’,‘FAROE ISLANDS’,‘FRO’,‘FO’),(‘238’,‘Falkland Islands (Malvinas)’,‘FALKLAND ISLANDS (MALVINAS)’,‘FLK’,‘FK’),(‘242’,‘Fiji’,‘FIJI’,‘FJI’,‘FJ’),(‘246’,‘Finland’,‘FINLAND’,‘FIN’,‘FI’),(‘248’,‘Ã…land Islands’,‘Ã…LAND ISLANDS’,‘ALA’,‘AX’),(‘250’,‘France’,‘FRANCE’,‘FRA’,‘FR’),(‘254’,‘French Guiana’,‘FRENCH GUIANA’,‘GUF’,‘GF’),(‘258’,‘French Polynesia’,‘FRENCH POLYNESIA’,‘PYF’,‘PF’),(‘262’,‘Djibouti’,‘DJIBOUTI’,‘DJI’,‘DJ’),(‘266’,‘Gabon’,‘GABON’,‘GAB’,‘GA’),(‘268’,‘Georgia’,‘GEORGIA’,‘GEO’,‘GE’),(‘270’,‘Gambia’,‘GAMBIA’,‘GMB’,‘GM’),(‘275’,‘Occupied Palestinian Territory’,‘PALESTINIAN TERRITORY, OCCUPIED’,‘PSE’,‘PS’),(‘276’,‘Germany’,‘GERMANY’,‘DEU’,‘DE’),(‘288’,‘Ghana’,‘GHANA’,‘GHA’,‘GH’),(‘292’,‘Gibraltar’,‘GIBRALTAR’,‘GIB’,‘GI’),(‘296’,‘Kiribati’,‘KIRIBATI’,‘KIR’,‘KI’),(‘300’,‘Greece’,‘GREECE’,‘GRC’,‘GR’),(‘304’,‘Greenland’,‘GREENLAND’,‘GRL’,‘GL’),(‘308’,‘Grenada’,‘GRENADA’,‘GRD’,‘GD’),(‘312’,‘Guadeloupe’,‘GUADELOUPE’,‘GLP’,‘GP’),(‘316’,‘Guam’,‘GUAM’,‘GUM’,‘GU’),(‘320’,‘Guatemala’,‘GUATEMALA’,‘GTM’,‘GT’),(‘324’,‘Guinea’,‘GUINEA’,‘GIN’,‘GN’),(‘328’,‘Guyana’,‘GUYANA’,‘GUY’,‘GY’),(‘332’,‘Haiti’,‘HAITI’,‘HTI’,‘HT’),(‘336’,‘Holy See’,‘HOLY SEE (VATICAN CITY STATE)’,‘VAT’,‘VA’),(‘340’,‘Honduras’,‘HONDURAS’,‘HND’,‘HN’),(‘344’,‘China, Hong Kong Special Administrative Region’,‘HONG KONG’,‘HKG’,‘HK’),(‘348’,‘Hungary’,‘HUNGARY’,‘HUN’,‘HU’),(‘352’,‘Iceland’,‘ICELAND’,‘ISL’,‘IS’),(‘356’,‘India’,‘INDIA’,‘IND’,‘IN’),(‘360’,‘Indonesia’,‘INDONESIA’,‘IDN’,‘ID’),(‘364’,‘Iran (Islamic Republic of)’,‘IRAN, ISLAMIC REPUBLIC OF’,‘IRN’,‘IR’),(‘368’,‘Iraq’,‘IRAQ’,‘IRQ’,‘IQ’),(‘372’,‘Ireland’,‘IRELAND’,‘IRL’,‘IE’),(‘376’,‘Israel’,‘ISRAEL’,‘ISR’,‘IL’),(‘380’,‘Italy’,‘ITALY’,‘ITA’,‘IT’),(‘384’,‘Côte d’Ivoire’,‘CÔTE D’IVOIRE’,‘CIV’,‘CI’),(‘388’,‘Jamaica’,‘JAMAICA’,‘JAM’,‘JM’),(‘392’,‘Japan’,‘JAPAN’,‘JPN’,‘JP’),(‘398’,‘Kazakhstan’,‘KAZAKHSTAN’,‘KAZ’,‘KZ’),(‘400’,‘Jordan’,‘JORDAN’,‘JOR’,‘JO’),(‘404’,‘Kenya’,‘KENYA’,‘KEN’,‘KE’),(‘408’,‘Democratic People’s Republic of Korea’,‘KOREA, DEMOCRATIC PEOPLE’S REPUBLIC OF’,‘PRK’,‘KP’),(‘410’,‘Republic of Korea’,‘KOREA, REPUBLIC OF’,‘KOR’,‘KR’),(‘414’,‘Kuwait’,‘KUWAIT’,‘KWT’,‘KW’),(‘417’,‘Kyrgyzstan’,‘KYRGYZSTAN’,‘KGZ’,‘KG’),(‘418’,‘Lao People’s Democratic Republic’,‘LAO PEOPLE’S DEMOCRATIC REPUBLIC’,‘LAO’,‘LA’),(‘422’,‘Lebanon’,‘LEBANON’,‘LBN’,‘LB’),(‘426’,‘Lesotho’,‘LESOTHO’,‘LSO’,‘LS’),(‘428’,‘Latvia’,‘LATVIA’,‘LVA’,‘LV’),(‘430’,‘Liberia’,‘LIBERIA’,‘LBR’,‘LR’),(‘434’,‘Libya’,‘LIBYA’,‘LBY’,‘LY’),(‘438’,‘Liechtenstein’,‘LIECHTENSTEIN’,‘LIE’,‘LI’),(‘440’,‘Lithuania’,‘LITHUANIA’,‘LTU’,‘LT’),(‘442’,‘Luxembourg’,‘LUXEMBOURG’,‘LUX’,‘LU’),(‘446’,‘China, Macao Special Administrative Region’,‘MACAO’,‘MAC’,‘MO’),(‘450’,‘Madagascar’,‘MADAGASCAR’,‘MDG’,‘MG’),(‘454’,‘Malawi’,‘MALAWI’,‘MWI’,‘MW’),(‘458’,‘Malaysia’,‘MALAYSIA’,‘MYS’,‘MY’),(‘462’,‘Maldives’,‘MALDIVES’,‘MDV’,‘MV’),(‘466’,‘Mali’,‘MALI’,‘MLI’,‘ML’),(‘470’,‘Malta’,‘MALTA’,‘MLT’,‘MT’),(‘474’,‘Martinique’,‘MARTINIQUE’,‘MTQ’,‘MQ’),(‘478’,‘Mauritania’,‘MAURITANIA’,‘MRT’,‘MR’),(‘480’,‘Mauritius’,‘MAURITIUS’,‘MUS’,‘MU’),(‘484’,‘Mexico’,‘MEXICO’,‘MEX’,‘MX’),(‘492’,‘Monaco’,‘MONACO’,‘MCO’,‘MC’),(‘496’,‘Mongolia’,‘MONGOLIA’,‘MNG’,‘MN’),(‘498’,‘Republic of Moldova’,‘MOLDOVA, REPUBLIC OF’,‘MDA’,‘MD’),(‘499’,‘Montenegro’,‘MONTENEGRO’,‘MNE’,‘ME’),(‘500’,‘Montserrat’,‘MONTSERRAT’,‘MSR’,‘MS’),(‘504’,‘Morocco’,‘MOROCCO’,‘MAR’,‘MA’),(‘508’,‘Mozambique’,‘MOZAMBIQUE’,‘MOZ’,‘MZ’),(‘512’,‘Oman’,‘OMAN’,‘OMN’,‘OM’),(‘516’,‘Namibia’,‘NAMIBIA’,‘NAM’,‘NA’),(‘520’,‘Nauru’,‘NAURU’,‘NRU’,‘NR’),(‘524’,‘Nepal’,‘NEPAL’,‘NPL’,‘NP’),(‘528’,‘Netherlands’,‘NETHERLANDS’,‘NLD’,‘NL’),(‘531’,‘Curaçao’,‘CURAÇAO’,‘CUW’,‘CW’),(‘533’,‘Aruba’,‘ARUBA’,‘ABW’,‘AW’),(‘534’,‘Sint Maarten (Dutch part)’,‘SINT MAARTEN (DUTCH PART)’,‘SXM’,‘SX’),(‘535’,‘Bonaire, Saint Eustatius and Saba’,‘BONAIRE, SINT EUSTATIUS AND SABA’,‘BES’,‘BQ’),(‘540’,‘New Caledonia’,‘NEW CALEDONIA’,‘NCL’,‘NC’),(‘548’,‘Vanuatu’,‘VANUATU’,‘VUT’,‘VU’),(‘554’,‘New Zealand’,‘NEW ZEALAND’,‘NZL’,‘NZ’),(‘558’,‘Nicaragua’,‘NICARAGUA’,‘NIC’,‘NI’),(‘562’,‘Niger’,‘NIGER’,‘NER’,‘NE’),(‘566’,‘Nigeria’,‘NIGERIA’,‘NGA’,‘NG’),(‘570’,‘Niue’,‘NIUE’,‘NIU’,‘NU’),(‘574’,‘Norfolk Island’,‘NORFOLK ISLAND’,‘NFK’,‘NF’),(‘578’,‘Norway’,‘NORWAY’,‘NOR’,‘NO’),(‘580’,‘Northern Mariana Islands’,‘NORTHERN MARIANA ISLANDS’,‘MNP’,‘MP’),(‘583’,‘Micronesia (Federated States of)’,‘MICRONESIA, FEDERATED STATES OF’,‘FSM’,‘FM’),(‘584’,‘Marshall Islands’,‘MARSHALL ISLANDS’,‘MHL’,‘MH’),(‘585’,‘Palau’,‘PALAU’,‘PLW’,‘PW’),(‘586’,‘Pakistan’,‘PAKISTAN’,‘PAK’,‘PK’),(‘591’,‘Panama’,‘PANAMA’,‘PAN’,‘PA’),(‘598’,‘Papua New Guinea’,‘PAPUA NEW GUINEA’,‘PNG’,‘PG’),(‘600’,‘Paraguay’,‘PARAGUAY’,‘PRY’,‘PY’),(‘604’,‘Peru’,‘PERU’,‘PER’,‘PE’),(‘608’,‘Philippines’,‘PHILIPPINES’,‘PHL’,‘PH’),(‘612’,‘Pitcairn’,‘PITCAIRN’,‘PCN’,‘PN’),(‘616’,‘Poland’,‘POLAND’,‘POL’,‘PL’),(‘620’,‘Portugal’,‘PORTUGAL’,‘PRT’,‘PT’),(‘624’,‘Guinea-Bissau’,‘GUINEA-BISSAU’,‘GNB’,‘GW’),(‘626’,‘Timor-Leste’,‘TIMOR-LESTE’,‘TLS’,‘TL’),(‘630’,‘Puerto Rico’,‘PUERTO RICO’,‘PRI’,‘PR’),(‘634’,‘Qatar’,‘QATAR’,‘QAT’,‘QA’),(‘638’,‘Réunion’,‘RÉUNION’,‘REU’,‘RE’),(‘642’,‘Romania’,‘ROMANIA’,‘ROU’,‘RO’),(‘643’,‘Russian Federation’,‘RUSSIAN FEDERATION’,‘RUS’,‘RU’),(‘646’,‘Rwanda’,‘RWANDA’,‘RWA’,‘RW’),(‘652’,‘Saint-Barthélemy’,‘SAINT BARTHÉLEMY’,‘BLM’,‘BL’),(‘654’,‘Saint Helena’,‘SAINT HELENA, ASCENSION AND TRISTAN DA CUNHA’,‘SHN’,‘SH’),(‘659’,‘Saint Kitts and Nevis’,‘SAINT KITTS AND NEVIS’,‘KNA’,‘KN’),(‘660’,‘Anguilla’,‘ANGUILLA’,‘AIA’,‘AI’),(‘662’,‘Saint Lucia’,‘SAINT LUCIA’,‘LCA’,‘LC’),(‘663’,‘Saint-Martin (French part)’,‘SAINT MARTIN (FRENCH PART)’,‘MAF’,‘MF’),(‘666’,‘Saint Pierre and Miquelon’,‘SAINT PIERRE AND MIQUELON’,‘SPM’,‘PM’),(‘670’,‘Saint Vincent and the Grenadines’,‘SAINT VINCENT AND THE GRENADINES’,‘VCT’,‘VC’),(‘674’,‘San Marino’,‘SAN MARINO’,‘SMR’,‘SM’),(‘678’,‘Sao Tome and Principe’,‘SAO TOME AND PRINCIPE’,‘STP’,‘ST’),(‘682’,‘Saudi Arabia’,‘SAUDI ARABIA’,‘SAU’,‘SA’),(‘686’,‘Senegal’,‘SENEGAL’,‘SEN’,‘SN’),(‘688’,‘Serbia’,‘SERBIA’,‘SRB’,‘RS’),(‘690’,‘Seychelles’,‘SEYCHELLES’,‘SYC’,‘SC’),(‘694’,‘Sierra Leone’,‘SIERRA LEONE’,‘SLE’,‘SL’),(‘702’,‘Singapore’,‘SINGAPORE’,‘SGP’,‘SG’),(‘703’,‘Slovakia’,‘SLOVAKIA’,‘SVK’,‘SK’),(‘704’,‘Viet Nam’,‘VIET NAM’,‘VNM’,‘VN’),(‘705’,‘Slovenia’,‘SLOVENIA’,‘SVN’,‘SI’),(‘706’,‘Somalia’,‘SOMALIA’,‘SOM’,‘SO’),(‘710’,‘South Africa’,‘SOUTH AFRICA’,‘ZAF’,‘ZA’),(‘716’,‘Zimbabwe’,‘ZIMBABWE’,‘ZWE’,‘ZW’),(‘724’,‘Spain’,‘SPAIN’,‘ESP’,‘ES’),(‘728’,‘South Sudan’,‘SOUTH SUDAN’,‘SSD’,‘SS’),(‘729’,‘Sudan’,‘SUDAN’,‘SDN’,‘SD’),(‘732’,‘Western Sahara’,‘WESTERN SAHARA’,‘ESH’,‘EH’),(‘740’,‘Suriname’,‘SURINAME’,‘SUR’,‘SR’),(‘744’,‘Svalbard and Jan Mayen Islands’,‘SVALBARD AND JAN MAYEN’,‘SJM’,‘SJ’),(‘748’,‘Swaziland’,‘SWAZILAND’,‘SWZ’,‘SZ’),(‘752’,‘Sweden’,‘SWEDEN’,‘SWE’,‘SE’),(‘756’,‘Switzerland’,‘SWITZERLAND’,‘CHE’,‘CH’),(‘760’,‘Syrian Arab Republic’,‘SYRIAN ARAB REPUBLIC’,‘SYR’,‘SY’),(‘762’,‘Tajikistan’,‘TAJIKISTAN’,‘TJK’,‘TJ’),(‘764’,‘Thailand’,‘THAILAND’,‘THA’,‘TH’),(‘768’,‘Togo’,‘TOGO’,‘TGO’,‘TG’),(‘772’,‘Tokelau’,‘TOKELAU’,‘TKL’,‘TK’),(‘776’,‘Tonga’,‘TONGA’,‘TON’,‘TO’),(‘780’,‘Trinidad and Tobago’,‘TRINIDAD AND TOBAGO’,‘TTO’,‘TT’),(‘784’,‘United Arab Emirates’,‘UNITED ARAB EMIRATES’,‘ARE’,‘AE’),(‘788’,‘Tunisia’,‘TUNISIA’,‘TUN’,‘TN’),(‘792’,‘Turkey’,‘TURKEY’,‘TUR’,‘TR’),(‘795’,‘Turkmenistan’,‘TURKMENISTAN’,‘TKM’,‘TM’),(‘796’,‘Turks and Caicos Islands’,‘TURKS AND CAICOS ISLANDS’,‘TCA’,‘TC’),(‘798’,‘Tuvalu’,‘TUVALU’,‘TUV’,‘TV’),(‘800’,‘Uganda’,‘UGANDA’,‘UGA’,‘UG’),(‘804’,‘Ukraine’,‘UKRAINE’,‘UKR’,‘UA’),(‘807’,‘The former Yugoslav Republic of Macedonia’,‘MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF’,‘MKD’,‘MK’),(‘818’,‘Egypt’,‘EGYPT’,‘EGY’,‘EG’),(‘826’,‘United Kingdom’,‘UNITED KINGDOM’,‘GBR’,‘GB’),(‘831’,‘Guernsey’,‘GUERNSEY’,‘GGY’,‘GG’),(‘832’,‘Jersey’,‘JERSEY’,‘JEY’,‘JE’),(‘833’,‘Isle of Man’,‘ISLE OF MAN’,‘IMN’,‘IM’),(‘834’,‘United Republic of Tanzania’,‘TANZANIA, UNITED REPUBLIC OF’,‘TZA’,‘TZ’),(‘840’,‘United States of America’,‘UNITED STATES’,‘USA’,‘US’),(‘850’,‘United States Virgin Islands’,‘VIRGIN ISLANDS, U.S.’,‘VIR’,‘VI’),(‘854’,‘Burkina Faso’,‘BURKINA FASO’,‘BFA’,‘BF’),(‘858’,‘Uruguay’,‘URUGUAY’,‘URY’,‘UY’),(‘860’,‘Uzbekistan’,‘UZBEKISTAN’,‘UZB’,‘UZ’),(‘862’,‘Venezuela (Bolivarian Republic of)’,‘VENEZUELA, BOLIVARIAN REPUBLIC OF’,‘VEN’,‘VE’),(‘876’,‘Wallis and Futuna Islands’,‘WALLIS AND FUTUNA’,‘WLF’,‘WF’),(‘882’,‘Samoa’,‘SAMOA’,‘WSM’,‘WS’),(‘887’,‘Yemen’,‘YEMEN’,‘YEM’,‘YE’),(‘894’,‘Zambia’,‘ZAMBIA’,‘ZMB’,‘ZM’),(‘90’,‘Solomon Islands’,‘SOLOMON ISLANDS’,‘SLB’,‘SB’);
/!40000 ALTER TABLE t_country ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_project

DROP TABLE IF EXISTS t_project;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_project (
ProjectID bigint(20) NOT NULL,
ClientID bigint(20) DEFAULT NULL,
Name longtext NOT NULL,
Description longtext,
StartDate date DEFAULT NULL,
EndDate date DEFAULT NULL,
Deadline date DEFAULT NULL,
StatusID bigint(20) NOT NULL,
ProjectLeaderID bigint(20) DEFAULT NULL,
PRIMARY KEY (ProjectID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_project

LOCK TABLES t_project WRITE;
/*!40000 ALTER TABLE t_project DISABLE KEYS /;
INSERT INTO t_project VALUES (1,1,‘Windows 10 roll-out’,‘Windows 10 roll-out’,‘2017-02-20’,NULL,NULL,1,NULL);
/!40000 ALTER TABLE t_project ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_projectstatus

DROP TABLE IF EXISTS t_projectstatus;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_projectstatus (
ProjectStatusID bigint(20) NOT NULL,
Status longtext,
PRIMARY KEY (ProjectStatusID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_projectstatus

LOCK TABLES t_projectstatus WRITE;
/*!40000 ALTER TABLE t_projectstatus DISABLE KEYS /;
INSERT INTO t_projectstatus VALUES (1,‘Not started’),(10,‘Open’),(20,‘On hold’),(50,‘Cancelled’),(100,‘Completed’);
/!40000 ALTER TABLE t_projectstatus ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_rate

DROP TABLE IF EXISTS t_rate;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_rate (
TaskRateID bigint(20) DEFAULT NULL,
ClientID bigint(20) DEFAULT NULL,
ProjectID bigint(20) DEFAULT NULL,
TaskID bigint(20) DEFAULT NULL,
UserID bigint(20) DEFAULT NULL,
RateHourly bigint(20) DEFAULT NULL,
RateDaily bigint(20) DEFAULT NULL,
RateMonthly bigint(20) DEFAULT NULL,
OvertimeMultiplier smallint(6) DEFAULT NULL,
OvertimeMultiplierSaturday smallint(6) DEFAULT NULL,
OvertimeMultiplierSunday smallint(6) DEFAULT NULL,
OvertimeMultiplierBankHoliday smallint(6) DEFAULT NULL,
OvertimeMultiplierNonContracted smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_rate

LOCK TABLES t_rate WRITE;
/*!40000 ALTER TABLE t_rate DISABLE KEYS /;
/!40000 ALTER TABLE t_rate ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_securitylevel

DROP TABLE IF EXISTS t_securitylevel;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_securitylevel (
SecurityLevelID bigint(20) NOT NULL,
SecurityLevelDescription longtext,
PRIMARY KEY (SecurityLevelID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_securitylevel

LOCK TABLES t_securitylevel WRITE;
/*!40000 ALTER TABLE t_securitylevel DISABLE KEYS /;
INSERT INTO t_securitylevel VALUES (0,‘Root’),(1,‘Global Administrator’),(2,‘Site Administrator’),(4,‘Client Adminisrator’),(8,‘Agency Administrator’),(16,‘Contractor Administrator’),(32,‘Editor (can edit other details)’),(64,‘Sub-editor (can edit some details)’),(128,‘Client Line Manager’),(256,‘Agency Line Manager’),(65536,‘Vanilla (can edit own details)’);
/!40000 ALTER TABLE t_securitylevel ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_task

DROP TABLE IF EXISTS t_task;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_task (
TaskID bigint(20) NOT NULL,
ProjectID bigint(20) DEFAULT NULL,
Name longtext,
Description longtext,
StatusID bigint(20) DEFAULT NULL,
PriorityID bigint(20) DEFAULT NULL,
PRIMARY KEY (TaskID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_task

LOCK TABLES t_task WRITE;
/*!40000 ALTER TABLE t_task DISABLE KEYS /;
INSERT INTO t_task VALUES (1,1,‘Windows 10 image’,‘Create an image for Windows 10 Build’,1,10),(2,1,‘Application Packaging’,‘Package applications for Windows 10’,1,10);
/!40000 ALTER TABLE t_task ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_taskpriority

DROP TABLE IF EXISTS t_taskpriority;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_taskpriority (
TaskPriorityID bigint(20) NOT NULL,
Priority longtext,
PRIMARY KEY (TaskPriorityID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_taskpriority

LOCK TABLES t_taskpriority WRITE;
/*!40000 ALTER TABLE t_taskpriority DISABLE KEYS /;
INSERT INTO t_taskpriority VALUES (1,‘Low’),(10,‘Standard’),(20,‘Accelerated’),(30,‘High’),(40,‘Emergency’);
/!40000 ALTER TABLE t_taskpriority ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_taskstatus

DROP TABLE IF EXISTS t_taskstatus;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_taskstatus (
TaskStatusID bigint(20) NOT NULL,
Status longtext,
PRIMARY KEY (TaskStatusID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_taskstatus

LOCK TABLES t_taskstatus WRITE;
/*!40000 ALTER TABLE t_taskstatus DISABLE KEYS /;
INSERT INTO t_taskstatus VALUES (1,‘New’),(10,‘Allocated’),(20,‘In progress’),(30,‘On hold, awaiting user input’),(40,‘On hold, awaiting hardware’),(50,‘On hold, awaiting software’),(60,‘On hold, other’),(70,‘In QA’),(80,‘In UAT’),(90,‘Returned to requestor’),(100,‘Cancelled’);
/!40000 ALTER TABLE t_taskstatus ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_timesheet

DROP TABLE IF EXISTS t_timesheet;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_timesheet (
TimesheetID bigint(20) NOT NULL,
TaskID bigint(20) NOT NULL,
StartDate datetime DEFAULT NULL,
EndDate datetime DEFAULT NULL,
UserAssignedToID bigint(20) NOT NULL,
Notes longtext,
SubmittedOn datetime DEFAULT NULL,
AuthorisedOn datetime DEFAULT NULL,
AuthorisedBy bigint(20) DEFAULT NULL,
AuthoriseEmailSent datetime DEFAULT NULL,
PRIMARY KEY (TimesheetID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_timesheet

LOCK TABLES t_timesheet WRITE;
/*!40000 ALTER TABLE t_timesheet DISABLE KEYS /;
INSERT INTO t_timesheet VALUES (1,1,‘2017-02-20 11:15:00’,‘2017-02-20 15:50:00’,1,NULL,‘2017-02-20 16:10:00’,NULL,NULL,NULL);
/!40000 ALTER TABLE t_timesheet ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_user

DROP TABLE IF EXISTS t_user;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_user (
UserID bigint(20) NOT NULL,
FirstName longtext,
LastName longtext,
SecurityLevelID bigint(20) NOT NULL,
AccessLevel bigint(20) DEFAULT NULL,
Password longtext,
Email longtext,
StatusID bigint(20) NOT NULL,
PRIMARY KEY (UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_user

LOCK TABLES t_user WRITE;
/*!40000 ALTER TABLE t_user DISABLE KEYS /;
INSERT INTO t_user VALUES (1,‘Joe’,‘User’,0,0,‘123’,‘joe.user@hotmail.com’,10);
/!40000 ALTER TABLE t_user ENABLE KEYS */;
UNLOCK TABLES;

–
– Table structure for table t_userstatus

DROP TABLE IF EXISTS t_userstatus;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t_userstatus (
UserStatusID bigint(20) NOT NULL,
Status longtext,
PRIMARY KEY (UserStatusID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

–
– Dumping data for table t_userstatus

LOCK TABLES t_userstatus WRITE;
/*!40000 ALTER TABLE t_userstatus DISABLE KEYS /;
INSERT INTO t_userstatus VALUES (1,‘New’),(10,‘Current’),(20,‘Retired’);
/!40000 ALTER TABLE t_userstatus ENABLE KEYS /;
UNLOCK TABLES;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2017-02-22 16:06:41[/code]

[Aliaksandr]

I’ve checked the code and did some changes, everything seems to be working ok now.
Updated files are attached. Be sure to change paths to js/css files, relative paths in require_once imports, etc.

Some changes that i made:

1. GetTasks.PHP: $optionsConnector->render_sql instead of render_table, as in my prev reply

2. index.html: scheduler.load(url) → scheduler.load(url, “json”) , you use JSON connector and client-side scheduler expects XML by default, so you need to specify “json” format explicitly
   docs.dhtmlx.com/scheduler/api__ … _load.html

3. index.html: in order to make lightbox write values into ClientId/ProjectId properties, you need to change mappings of lightbox controls screencast.com/t/sU9gC2K8P

4. index.html: If I understand correctly, you have following structure
   Timesheet -(FK to)-> Task
   Task -(FK to)-> Project
   Project -(FK to)-> Client
   Client -(FK to)-> ClientSite

ClientSite is not used, and TimeSheet items used as scheduler events.
Timesheet item has link to related task (TaskID), but don’t have direct relations to project and client.
So, firstly, I think you need to add a control for selecting Tasks back to the Timesheet item, since due to database design that’s the only way to link timesheet item to Project and Client

Secondly, in order to show client/project/task in lightbox, event should have all three ids (ClientID, ProjectID, TaskID). So you need to add these ids - it can be done either via sql join on a backend, or by js code on the client. I did it on the client since at the moment it was a quicker for me. Please see updated index.html for code

5. db: You need to add auto increment to t_timesheet.TimesheetID
   ALTER TABLE t_timesheet CHANGE TimesheetID TimesheetID BIGINT(20) NOT NULL AUTO_INCREMENT;

6. index.html: looks like scheduler.init is called twice during scheduler initialization. Not sure if it causes any issues, but it’s not needed.

There also must be some other changes in GetTasks and index.html that I forgot to mention.
timesheets.zip (11 KB)

[VBScab]

OK! We’re making progress now!

Using the first block of code below (which is a lightly edited version of what you sent me), the tasks that I have added manually to the database (all PK fields in most of the important tables are now set to auto-increment, BTW) now display on the scheduler, with the correct date and time and with the Task Name in the “body” of the event. My problems now are:

• I want tasks to appear in a textarea. However, when I change the control type from ‘select’ to ‘textarea’, I get a figure ‘2’, which is obviously the TaskID. How can I get the Task Name to appear in a textarea?

• When I double-click any of the events (tasks, in my implementation), it displays the drop-downs as if I were adding a new event. The actual values from the database should mark the item’s index in the drop-down as ‘SELECTED’.

• When I make changes or add a new event and click ‘Save’, the change isn’t saved or no new record added. I thought that that was automatic with the scheduler

[code]<!doctype html>

Timesheet html, body{ margin:0px; padding:0px; height:100%; overflow:hidden; }

 

 

 

[/code] Lastly, this is a *tiny* database and yet the initial load is taking almost 20 seconds. I would expect sub-2 seconds!! What could that be due to?

[Aliaksandr]

Hi,

• I want tasks to appear in a textarea. However, when I change the control type from ‘select’ to ‘textarea’, I get a figure ‘2’, which is obviously the TaskID. How can I get the Task Name to appear in a textarea? I suspect I need to change this:

I’m not completely sure what you want to do at the concept level.
You have Task (t_task table) which has its name, and you have Timesheet (t_timesheet) which has dates and which is linked to a task.
Timesheets are shown as events in Scheduler.

If you have lightbox with textarea for Task name and everything else for Timesheet item - how do you expect to save it to the database?
E.g. if I open ligthbox for existing event, modify task text in textarea and dates of timesheet and click Save - what should be written to t_timesheet and t_task tables?
should it update both t_task and t_timesheet tables (apply modified dates to timesheet, modify or insert new task?) or should it do something else?

• When I double-click any of the events (tasks, in my implementation), it displays the drop-downs as if I were adding a new event. The actual values from the database should mark the item’s index in the drop-down as ‘SELECTED’.

Looks like my mistake, I’ve mixed up properties when was renaming everything (ProjectId vs ProjectID vs project, ClientId vs ClientId), and seems onLightbox seemed to work not exactly as expected. please check the updated attachment, everything seems good now

• When I make changes or add a new event and click ‘Save’, the change isn’t saved or no new record added. I thought that that was automatic with the scheduler

Everything seems working ok in the demo I’ve sent, or do you have an error there as well?
Please try enabling log on a backend - uncomment ‘//$scheduler->enable_log(“log.txt”);’ in GetTasks.php , it could help locating the issue.

Lastly, this is a tiny database and yet the initial load is taking almost 20 seconds. I would expect sub-2 seconds!! What could that be due to?

probably something with your setup. It should take milliseconds (up to a couple hundreds ms) locally, and a bit more from remote server. Try creating a php script where you open mysqli connection and do selects from all tables used in gettasks.php - does it works as slow as in the app?
timesheet_170223.zip (11.1 KB)

[VBScab]

If you have lightbox with textarea for Task name and everything else for Timesheet item - how do you expect to save it to the database?
E.g. if I open ligthbox for existing event, modify task text in textarea and dates of timesheet and click Save - what should be written to t_timesheet and t_task tables?
should it update both t_task and t_timesheet tables (apply modified dates to timesheet, modify or insert new task?) or should it do something else?

If the Task text has changed, the ‘t_task’ table should be updated. If the dates/times change, the ‘t_timesheet’ table should be updated.

I’ve just d/l the new ZIP so I’ll have a play with that.

As an aside, the d/b was built this way in order for it to be normalised, especially from a “no repeat of data” point of view. It’s a method I’ve always used, as I read once that it is The One True Way of d/b design.

[VBScab]

Testing with the Task as a drop-down is good now. The “body” of the event shows as:

…Client name
…Project name
…Task name

I do still have the problem whereby changes aren’t being saved. For example, if I change the start time, duration or day, the body changes to all-bold (which presumably means that it “knows” something’s changed) but on refreshing the page, I see the same data as before

Coming to handling tasks…it’s re-think time.

I think perhaps my best option is to add buttons next to the drop-downs which will pop up a form to create new items for the relevant drop-down, i.e. a new client, project or task.

With regard to d/b performance, your sample takes just as long as my code so…[shrugs] As I say, that’s a job for another day. For now, I’m going to crack on.

At the risk of repeating myself, I really am most grateful to you for your help. I would’ve been struggling with this for weeks without it.

[VBScab]

The d/b log shows very fast response:[code]====================================
Log started, 23/02/2017 03:55:18

SELECT TimesheetID,StartDate,EndDate,Notes,TaskID,UserAssignedToID,SubmittedOn,AuthorisedOn,AuthorisedBy,AuthoriseEmailSent FROM t_timesheet WHERE StartDate < ‘2017-02-27’ AND EndDate > ‘2017-02-20’

SELECT ClientID AS key, Name AS label FROM t_client
SELECT ProjectID AS key, Name AS label, ClientID FROM t_project
SELECT TaskID AS key, Name AS label, ProjectID, Description,StatusID,PriorityID FROM t_task

Done in 0.01909613609314s[/code] so I’m stumped as to where the delay is occuring.

[Aliaksandr]

Hi,
my pleasure)

Regarding not saving changes - when you see event font changed to bold it means that dhtmlx dataProcessor (a module that sends POST requests with insert/delete/update requests) have sent the update to the server and waits for a response. Once it receives it the font weight will be changed back to normal. If it stays in bold font it means either it’s still waiting for server response, or that there was something wrong with the response.
Try checking the ajax response from those request in browser dev tools, it should give a clue - screencast.com/t/zLrpFdiAEMhs
Connector logs should also store an info on that operations

Regarding db delay - do you have database on localhost or on a remote server? Try logging time for opening db connection - probably it spends most of the time trying to establish db connection.

And as for the tasks/timesheets - it’s hard to say, I doubt I’ll be able to come up with a good design suggestion with a time and an info i have:)
Please note that if you’ll want to add some buttons to select controls, it could probably be done only by creating your own version of the select editor, maybe using not a simple html select but some kind of autocomplete dropdown. It’s not very complicated but still requires some js coding
docs.dhtmlx.com/scheduler/custo … ditor.html
github.com/DHTMLX/scheduler/blo … r.js#L5980

[VBScab]

Regarding db delay - do you have database on localhost or on a remote server?

Well, you can officially paint me “stunned”!!

My d/b is hosted locally. That statement got me thinking so in my database connection PHP file I changed the reference from ‘localhost’ to ‘127.0.0.1’. Now, to retrieve 3 records, we have now gone from between 20 and 22 seconds to sub 10 micro-seconds! Result!!

I have no idea why that would make such a difference. Does anyone else?

[Aliaksandr]

Hi,
maybe it’s dns lookup issue, something similar is discussed here stackoverflow.com/questions/1292 … er-so-slow