DatawarehouseVM.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2015 Lluis Turró Cutiller <http://www.turro.org/>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
 
package org.turro.dossier.dw;
 
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.amic.util.date.CheckDate;
import org.turro.string.Strings;
import org.turro.action.Contacts;
import org.turro.dossier.db.DossierPU;
import org.turro.dossier.entity.Category;
import org.turro.dossier.entity.Dossier;
import org.turro.dossier.entity.IssueParticipantRole;
import org.turro.dossier.entity.IssueResolution;
import org.turro.dossier.entity.IssueStatus;
import org.turro.dossier.entity.IssueType;
import org.turro.elephant.db.WhereClause;
import org.turro.jpa.Dao;
import org.turro.plugin.contacts.IContact;
import org.turro.util.Chars;
import org.zkoss.bind.annotation.BindingParam;
import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.Init;
import org.zkoss.bind.annotation.NotifyChange;
import org.zkoss.zul.SimpleCategoryModel;
import org.zkoss.zul.SimplePieModel;
 
public class DatawarehouseVM {
  
  private Date start, end;
  private Category category;
  private Dossier dossier;
  private IContact contact;
  private DWChartEngine engine;
  private List<DWChart> charts;
  private DWReportType report;
  
  public Date getStart() {
    return start;
  }
 
  public void setStart(Date start) {
    this.start = start;
  }
 
  public Date getEnd() {
    return end;
  }
 
  public void setEnd(Date end) {
    this.end = end;
  }
 
  public Category getCategory() {
    return category;
  }
 
  public void setCategory(Category category) {
    this.category = category;
  }
 
  public Dossier getDossier() {
    return dossier;
  }
 
  public void setDossier(Dossier dossier) {
    this.dossier = dossier;
  }
 
  public IContact getContact() {
    return contact;
  }
 
  public void setContact(IContact contact) {
    this.contact = contact;
  }
 
  @Init
  public void init() {
    engine = new DWChartEngine();
    start = new CheckDate().addYears(-5).setDay(1).getDate();
    end = new CheckDate().addMonths(1).setDay(31).getDate();
    charts = new ArrayList<>();
    report = DWReportType.DW_TIMELINE;
  }
  
  @NotifyChange("*")
  @Command("update")
  public void update() {}
  
  @NotifyChange("model")
  @Command
  public void selectEntity(@BindingParam("entity") Object entity) {
    if(entity instanceof Category) {
      this.category = (Category) entity;
      this.dossier = null;
    } else if(entity instanceof Dossier) {
      this.dossier = (Dossier) entity;
      this.category = null;
    } else {
      this.dossier = null;
      this.category = null;
    }
  }
  
  @NotifyChange("model")
  @Command
  public void selectContact(@BindingParam("contact") IContact contact) {
    setContact(contact);
  }
  
  @NotifyChange("model")
  @Command
  public void selectReport(@BindingParam("type") DWReportType type) {
    this.report = type;
  }
  
  public List<DWChart> getModel() {
    charts.clear();
    if(report.equals(DWReportType.DW_TIMELINE)) {
      charts.add(getTimelineByStatus(null));
      charts.add(getTimelineByType(null));
      charts.add(getTimelineByResolution(null));
    } else if(report.equals(DWReportType.DW_PARTICIPANTS)) {
      charts.add(getIssuesByParticipant(IssueParticipantRole.ISSUE_RESPONSIBLE));
      charts.add(getIssuesByParticipant(IssueParticipantRole.ISSUE_REPORTER));
      charts.add(getIssuesByParticipant(IssueParticipantRole.ISSUE_QA));
    } else if(report.equals(DWReportType.DW_ISSUES)) {
      charts.add(getByStatus(null));
      charts.add(getByType(null));
      charts.add(getByResolution(null));
    }
    return charts;
  }
  
  public DWChart getIssuesByParticipant(IssueParticipantRole role) {
    SimpleCategoryModel cm = new SimpleCategoryModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select participantId, resolution, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where participantRole = :role");
    wc.addNamedValue("role", role);
    IContact temp = contact;
    contact = null;
    addFilter(wc, "startDate", null);
    contact = temp;
    wc.addClause("group by participantId, resolution");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IContact participant = Contacts.getContactById((String) o[0]);
      if(participant.isValid()) {
        IssueResolution resolution = (IssueResolution) o[1];
        cm.setValue(resolution.toString(), participant.getName(), (Number) o[2]);
      }
    }
    DWChart chart = new DWChart();
    chart.setTitle(role.toString());
    chart.setModel(cm);
    chart.setType("bar");
    chart.setOrient("horizontal");
    chart.setWidth("900");
    chart.setHeight((results.size() * 90 + 40) + "");
    return chart;
  }
 
  public DWChart getByStatus(IssueParticipantRole role) {
    SimplePieModel cm = new SimplePieModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select status, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "startDate", role);
    wc.addClause("group by status");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueStatus status = (IssueStatus) o[0];
      cm.setValue(status.toString(), (Number) o[1]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Status");
    chart.setModel(cm);
    chart.setType("pie");
    chart.setHeight("300");
    chart.setWidth("500");
    return chart;
  }
 
  public DWChart getByType(IssueParticipantRole role) {
    SimplePieModel cm = new SimplePieModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select type, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "startDate", role);
    wc.addClause("group by type");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueType type = (IssueType) o[0];
      cm.setValue(type.toString(), (Number) o[1]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Type");
    chart.setModel(cm);
    chart.setType("pie");
    chart.setHeight("300");
    chart.setWidth("500");
    return chart;
  }
 
  public DWChart getByResolution(IssueParticipantRole role) {
    SimplePieModel cm = new SimplePieModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select resolution, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "startDate", role);
    wc.addClause("group by resolution");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueResolution resolution = (IssueResolution) o[0];
      cm.setValue(resolution.toString(), (Number) o[1]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Resolutions");
    chart.setModel(cm);
    chart.setType("pie");
    chart.setHeight("300");
    chart.setWidth("500");
    return chart;
  }
 
  public DWChart getTimelineByResolution(IssueParticipantRole role) {
    SimpleCategoryModel cm = new SimpleCategoryModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select concat(year(solvedDate), '-', month(solvedDate)), resolution, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "solvedDate", role);
    wc.addClause("group by concat(year(solvedDate), '-', month(solvedDate)), resolution");
    wc.addClause("order by concat(year(solvedDate), '-', month(solvedDate))");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueResolution resolution = (IssueResolution) o[1];
      String month = (String) o[0];
      cm.setValue(resolution.toString(), month, (Number) o[2]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Resolution");
    chart.setModel(cm);
    chart.setType("line");
    chart.setHeight("300");
    chart.setWidth((cm.getCategories().size() * 70 + 40) + "");
    return chart;
  }
 
  public DWChart getTimelineByStatus(IssueParticipantRole role) {
    SimpleCategoryModel cm = new SimpleCategoryModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select concat(year(startDate), '-', month(startDate)), status, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "startDate", role);
    wc.addClause("group by concat(year(startDate), '-', month(startDate)), status");
    wc.addClause("order by concat(year(startDate), '-', month(startDate))");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueStatus status = (IssueStatus) o[1];
      String month = (String) o[0];
      cm.setValue(status.toString(), month, (Number) o[2]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Status");
    chart.setModel(cm);
    chart.setType("line");
    chart.setHeight("300");
    chart.setWidth((cm.getCategories().size() * 70 + 40) + "");
    return chart;
  }
 
  public DWChart getTimelineByType(IssueParticipantRole role) {
    SimpleCategoryModel cm = new SimpleCategoryModel();
    WhereClause wc = new WhereClause();
    wc.addClause("select concat(year(startDate), '-', month(startDate)), type, count(distinct issueId)");
    wc.addClause("from DWIssue");
    wc.addClause("where 1=1");
    addFilter(wc, "startDate", role);
    wc.addClause("group by concat(year(startDate), '-', month(startDate)), type");
    wc.addClause("order by concat(year(startDate), '-', month(startDate))");
    List<Object[]> results = getDao().getResultList(wc);
    for(Object o[] : results) {
      IssueType type = (IssueType) o[1];
      String month = (String) o[0];
      cm.setValue(type.toString(), month, (Number) o[2]);
    }
    DWChart chart = new DWChart();
    chart.setTitle("Type");
    chart.setModel(cm);
    chart.setType("line");
    chart.setHeight("300");
    chart.setWidth((cm.getCategories().size() * 70 + 40) + "");
    return chart;
  }
 
  public DWChartEngine getEngine() {
    return engine;
  }
  
  private Dao _dao;
  
  private Dao getDao() {
    if(_dao == null) {
      DatawarehouseUtil.generateData();
      _dao = new DossierPU();
    }
    return _dao;
  }
  
  private void addFilter(WhereClause wc, String dateField, IssueParticipantRole role) {
    if(!Strings.isBlank(dateField)) {
      wc.addClause("and " + dateField + " >= :start");
      wc.addClause("and " + dateField + " <= :end");
      wc.addNamedValue("start", start);
      wc.addNamedValue("end", end);
    }
    if(category != null) {
      wc.addClause("and (");
      wc.addClause("categoryPath = :fullCatDesc");
      wc.addClause("or categoryPath like concat(:fullCatDesc, '" + Chars.backward().spaced() + "%')");
      wc.addNamedValue("fullCatDesc", category.getFullDescription());
      wc.addClause(")");
    }
    if(dossier != null) {
      wc.addClause("and dossierId = :dossierId");
      wc.addNamedValue("dossierId", dossier.getId());
    }
    if(contact != null && contact.isValid()) {
      wc.addClause("and (");
      wc.addClause("participantId = :contactId");
      wc.addNamedValue("contactId", contact.getId());
      if(role != null) {
        wc.addClause("and participantRole = :contactRole");
        wc.addNamedValue("contactRole", role);
      }
      wc.addClause(")");
    } 
  }
  
}