In this project, the data I need is in another database, and I get the data by querying and converting it to the form of keyValueEntity.
The data is a list of devices and their associated historical state records. The device and state are a one-to-many relationship.
I need to put the data in the gantt chart, but I haven’t found the right way.
Thank!
Hello @Yao7,
Could you share the test project? Please, describe your data model in more detail (which attributes have entities, which attributes will be displayed in a chart).
Also, you can see an example of using a gantt chart.
Regards,
Gleb
Hi @durygin,
There is a project called mes, I need to get data from his database tc-mes, and I need two classes: Device and DeviceStatusCollect.
The mes project:
package com.shsaimo.tcmes.entity.EquipmentManagement;
import com.haulmont.chile.core.annotations.NamePattern;
import com.haulmont.cuba.core.app.dynamicattributes.PropertyType;
import com.haulmont.cuba.core.entity.StandardEntity;
import com.shsaimo.tcmes.entity.ProcessManagement.WorkStation;
import javax.persistence.*;
import java.util.Date;
import java.util.List;
@NamePattern("%s|name")
@Table(name = "TCMES_DEVICE")
@Entity(name = "tcmes_Device")
public class Device extends StandardEntity {
private static final long serialVersionUID = -108435949441327370L;
@Column(name = "NAME")
protected String name;
@Column(name = "DEVICET_NUMBER")
protected String devicetNumber;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "WORK_STATION_ID")
protected WorkStation workStation;
@Column(name = "STATUS")
protected String status;
@Column(name = "TYPE_")
protected String type;
@Column(name = "POSITION_")
protected String position;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "PURCHASING_TIME")
protected Date purchasingTime;
@Column(name = "REMARK")
protected String remark;
@OneToMany(mappedBy = "device")
protected List<DeviceStatusCollect> deviceStatusCollect;
public List<DeviceStatusCollect> getDeviceStatusCollect() {
return deviceStatusCollect;
}
public void setDeviceStatusCollect(List<DeviceStatusCollect> deviceStatusCollect) {
this.deviceStatusCollect = deviceStatusCollect;
}
public void setStatus(DeviceStatusEnum status) {
this.status = status == null ? null : status.getId();
}
public DeviceStatusEnum getStatus() {
return status == null ? null : DeviceStatusEnum.fromId(status);
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public Date getPurchasingTime() {
return purchasingTime;
}
public void setPurchasingTime(Date purchasingTime) {
this.purchasingTime = purchasingTime;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public PropertyType getType() {
return type == null ? null : PropertyType.fromId(type);
}
public void setType(PropertyType type) {
this.type = type == null ? null : type.getId();
}
public WorkStation getWorkStation() {
return workStation;
}
public void setWorkStation(WorkStation workStation) {
this.workStation = workStation;
}
public String getDevicetNumber() {
return devicetNumber;
}
public void setDevicetNumber(String devicetNumber) {
this.devicetNumber = devicetNumber;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.shsaimo.tcmes.entity.EquipmentManagement;
import com.haulmont.cuba.core.entity.StandardEntity;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import java.util.Date;
@Table(name = "TCMES_DEVICE_STATUS_COLLECT")
@Entity(name = "tcmes_DeviceStatusCollect")
public class DeviceStatusCollect extends StandardEntity {
private static final long serialVersionUID = 8788855542518124225L;
@NotNull
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "DEVICE_ID")
protected Device device;
@Column(name = "LAST_STATUS")
protected String lastStatus;
@Column(name = "CURRENT_STATUS")
protected String currentStatus;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "COLLECT_TIME")
protected Date startTime;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "LAST_STATUS_KEEP_TIME")
protected Date endTime;
@Column(name = "START_")
protected Integer start;
@Column(name = "DURATION")
protected Integer duration;
@Column(name = "INDEX_")
protected Integer index;
@Column(name = "REMARK")
protected String remark;
@Column(name = "COLOR")
protected String color;
@Column(name = "PLC_KEY")
protected String plcKey;
public Integer getStart() {
return start;
}
public void setStart(Integer start) {
this.start = start;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
public Integer getIndex() {
return index;
}
public void setIndex(Integer index) {
this.index = index;
}
public Integer getDuration() {
return duration;
}
public void setDuration(Integer duration) {
this.duration = duration;
}
public void setEndTime(Date endTime) {
this.endTime = endTime;
}
public Date getEndTime() {
return endTime;
}
public String getPlcKey() {
return plcKey;
}
public void setPlcKey(String plcKey) {
this.plcKey = plcKey;
}
public Date getStartTime() {
return startTime;
}
public void setStartTime(Date startTime) {
this.startTime = startTime;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public DeviceStatusEnum getCurrentStatus() {
return currentStatus == null ? null : DeviceStatusEnum.fromId(currentStatus);
}
public void setCurrentStatus(DeviceStatusEnum currentStatus) {
this.currentStatus = currentStatus == null ? null : currentStatus.getId();
}
public DeviceStatusEnum getLastStatus() {
return lastStatus == null ? null : DeviceStatusEnum.fromId(lastStatus);
}
public void setLastStatus(DeviceStatusEnum lastStatus) {
this.lastStatus = lastStatus == null ? null : lastStatus.getId();
}
public Device getDevice() {
return device;
}
public void setDevice(Device device) {
this.device = device;
}
}
The gantt chart shows the historical status of the device,I got results from the mes program.As shown in the following figure.
The question now is how do I display the tc-mes data on another project’s page.Because the project has no entity.The data I got from the SQL statement cannot be displayed correctly on the page.
I need to display the device name, device history status, and its time on the page.I did the following work.
Statistics page:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<window xmlns="http://schemas.haulmont.com/cuba/screen/window.xsd"
caption="msg://deviceStatusCollectScreenId"
messagesPack="com.company.tcreport.web.screens" xmlns:chart="http://schemas.haulmont.com/charts/charts.xsd">
<data>
<keyValueCollection id="deviceEntityDc">
<loader id="" firstResult="0"/>
<properties>
<property name="deviceName" datatype="string"/>
<property name="deviceNumber" datatype="string"/>
<property name="statusCollect" datatype="byteArray"/>
</properties>
</keyValueCollection>
<keyValueCollection id="deviceStatusDc">
<properties>
<property name="index" datatype="int"/>
<property name="start" datatype="int"/>
<property name="duration" datatype="int"/>
<property name="color" datatype="string"/>
<property name="remark" datatype="string"/>
</properties>
<loader/>
</keyValueCollection>
</data>
<layout expand="tableSheetId">
<tabSheet id="tableSheetId" height="100%" width="100%">
<tab id="deviceStatusGanttId" margin="true,false,false,false" spacing="true"
caption="msg://deviceStatusGanttId">
<buttonsPanel id="buttonsPanedId" width="100%">
<dateField id="startDayFieldId" datatype="date" resolution="DAY"/>
</buttonsPanel>
<chart:ganttChart id="ganttChart"
balloonDateFormat="JJ:NN"
additionalSegmentFields="remark"
dataContainer="deviceEntityDc"
brightnessStep="10"
categoryField="deviceNumber"
colorField="color"
columnWidth="0.5"
durationField="duration"
marginRight="70"
period="MINUTES"
rotate="true"
segmentsField="deviceStatusCollect"
startDate="2020-02-01"
startField="start"
theme="LIGHT"
width="100%">
<chart:graph balloonText="<stong>[[remark]]</strong>:[[open]] [[value]]"
fillAlphas="1"/>
<chart:valueAxis maximum="1440"
minimum="0"
type="DATE"/>
<chart:valueScrollbar autoGridCount="true"
color="BLACK"/>
<chart:chartCursor cursorAlpha="0"
cursorColor="#55bb76"
fullWidth="true"
valueLineAlpha="0.5"
valueBalloonsEnabled="false"
valueLineBalloonEnabled="true"
valueLineEnabled="true"
valueZoomable="true"
zoomable="false"/>
<chart:export/>
</chart:ganttChart>
</tab>
</tabSheet>
</layout>
</window>
DeviceEntity:
import com.company.tcreport.service.DeviceService;
import com.haulmont.cuba.core.entity.KeyValueEntity;
import com.haulmont.cuba.core.global.AppBeans;
import com.haulmont.cuba.gui.data.impl.CustomValueCollectionDatasource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.util.*;
public class DeviceEntity extends CustomValueCollectionDatasource {
private static final Log log = LogFactory.getLog(DeviceEntity.class);
private DeviceService deviceService = AppBeans.get(DeviceService.NAME);
@Override
protected Collection<KeyValueEntity> getEntities(Map<String, Object> params) {
List<KeyValueEntity> entityList = new ArrayList<>();
Collection<Object[]> deviceList = deviceService.getDeviceCollection();
if (deviceList == null) {
return null;
}
DeviceStatusCollectEntity deviceStatusCollectEntity = new DeviceStatusCollectEntity();
for (Object[] object : deviceList) {
String deviceNumber = object[0] == null ? "" : String.valueOf(object[0]);
log.info("entity-number: " + deviceNumber);
Map paramsMap = new HashMap();
paramsMap.put("deviceNumber", deviceNumber);
Collection<KeyValueEntity> deviceStatusCollect = deviceStatusCollectEntity.getEntities(paramsMap);
String deviceName = object[1] == null ? "" : String.valueOf(object[1]);
String workStation = object[2] == null ? "" : String.valueOf(object[2]);
String status = object[3] == null ? "" : String.valueOf(object[3]);
KeyValueEntity keyValueEntity = createKeyValueEntity(deviceNumber, deviceName, workStation, status, deviceStatusCollect);
entityList.add(keyValueEntity);
}
return entityList;
}
private KeyValueEntity createKeyValueEntity(String deviceNumber, String deviceName, String workStation, String status,Collection<KeyValueEntity> deviceStatusCollect) {
KeyValueEntity keyValueEntity = new KeyValueEntity();
keyValueEntity.setValue("deviceNumber", deviceNumber);
keyValueEntity.setValue("deviceName", deviceName);
keyValueEntity.setValue("workStation", workStation);
keyValueEntity.setValue("status", status);
keyValueEntity.setValue("deviceStatusCollect",deviceStatusCollect);
return keyValueEntity;
}
}
DeviceStatusCollectEntity:
package com.company.tcreport.web.screens.device;
import com.company.tcreport.service.DeviceService;
import com.haulmont.cuba.core.entity.KeyValueEntity;
import com.haulmont.cuba.core.global.AppBeans;
import com.haulmont.cuba.gui.data.impl.CustomValueCollectionDatasource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.text.SimpleDateFormat;
import java.util.*;
import static org.checkerframework.checker.units.UnitsTools.s;
public class DeviceStatusCollectEntity extends CustomValueCollectionDatasource {
private DeviceService deviceService = AppBeans.get(DeviceService.NAME);
private static final Log log = LogFactory.getLog(DeviceStatusCollectEntity.class);
@Override
protected Collection<KeyValueEntity> getEntities(Map<String, Object> params) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<KeyValueEntity> entityList = new ArrayList<>();
Collection<Object[]> deviceStatusCollectGanttbyDeviceNumberList = deviceService.getDeviceStatusCollectGantt(params);
if (deviceStatusCollectGanttbyDeviceNumberList == null) {
return null;
}
for (Object[] object : deviceStatusCollectGanttbyDeviceNumberList) {
String name = object[0] == null ? "" : (String) object[0];
String devicetNumber = object[1] == null ? "" : (String) object[1];
String currentStatus = object[2] == null ? "" : (String) object[2];
int index = (object[3] == null) ? 0 : (int) object[3];
int start = (object[4] == null) ? 0 : (int) object[4];
int duration = (object[5] == null) ? 0 : (int) object[5];
String color = object[6] == null ? "" : (String) object[6];
String remark = object[7] == null ? "" : (String) object[7];
KeyValueEntity keyValueEntity = createDeviceStatusCollectGantt(name, devicetNumber, currentStatus, index, start, duration, color, remark);
entityList.add(keyValueEntity);
}
return entityList;
}
private KeyValueEntity createDeviceStatusCollectGantt(String name, String devicetNumber, String currentStatus,
int index, int start, int duration, String color, String remark) {
KeyValueEntity keyValueEntity = new KeyValueEntity();
keyValueEntity.setValue("name", name);
keyValueEntity.setValue("devicetNumber", devicetNumber);
keyValueEntity.setValue("currentStatus", currentStatus);
keyValueEntity.setValue("index", index);
keyValueEntity.setValue("start", start);
keyValueEntity.setValue("duration", duration);
keyValueEntity.setValue("color", color);
keyValueEntity.setValue("remark", remark);
return keyValueEntity;
}
}
DeviceServiceBean:
package com.company.tcreport.service;
import com.haulmont.cuba.core.EntityManager;
import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Query;
import com.haulmont.cuba.core.Transaction;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import javax.inject.Inject;
import java.math.BigDecimal;
import java.util.*;
@Service(DeviceService.NAME)
public class DeviceServiceBean implements DeviceService {
private static final Log log = LogFactory.getLog(DeviceServiceBean.class);
@Autowired
private JdbcTemplate jdbcTemplate;
@Inject
private Persistence persistence;
@Override
public Collection<Object[]> getDeviceCollection() {
Transaction tx = persistence.createTransaction();
try {
String sql = "select devicet_number, name, (select name as workName from tcmes_work_station where id = work_station_id), " +
" status from tcmes_device where delete_ts is null ";
Query query = persistence.getEntityManager().createNativeQuery(sql);
log.info("zyl + sql: " + sql);
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
Map map = new HashMap();
List<Object[]> list = new ArrayList();
for (int i = 0; i < resultList.size(); i++) {
map = resultList.get(i);
log.info("service-number: " + map.get("devicet_number"));
Object[] objects = new Object[]{map.get("devicet_number"),map.get("name"),map.get("workName"),map.get("status")};
list.add(objects);
}
log.info("zyl + servicebean: " + list.size());
tx.commit();
return list;
}finally {
tx.end();
}
}
@Override
public Collection<Object[]> getDeviceStatusCollectGantt(Map<String, Object> paramsMap) {
String deviceNumber = (String) paramsMap.get("deviceNumber");
List list = new ArrayList();
Map map = new HashMap();
try (Transaction tx = persistence.createTransaction()) {
EntityManager entityManager = persistence.getEntityManager();
String sql = "select name, devicet_number, current_status, index_, start_, duration, color, s.remark " +
" from tcmes_device_status_collect s join tcmes_device d on s.device_id = d.id and devicet_number = '" + deviceNumber + "'";
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
for (int i = 0; i < resultList.size(); i++) {
map = resultList.get(i);
Object[] objects = new Object[]{map.get("name"),map.get("devicet_number"),map.get("current_status"),map.get("index_"),map.get("index_"),
map.get("duration"),map.get("color"),map.get("s.remark")};
list.add(objects);
}
tx.commit();
return list;
}
}
The results are not good:
Hi @Yao7
Please explain what data is displayed in this screenshot? Is this part of the correct data?
It looks as if the start and duration fields values are not calculated correctly.
Regards,
Gleb