DatawarehouseUtil.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.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
import org.turro.dossier.db.DossierPU;
import org.turro.dossier.entity.Dossier;
import org.turro.dossier.entity.Issue;
import org.turro.jpa.Dao;
import org.turro.sql.SqlClause;
 
public class DatawarehouseUtil {
  
  private static final Lock lock = new ReentrantLock();
  
  public static void generateData() {
    Dao dao = new DossierPU();
    lock.lock();
    try {
      if(checkModifications(dao)) {
        generateDossiers(dao);
        generateIssues(dao);
      }
    } finally {
      lock.unlock();
    }
  }
 
  // TODO: Afegir participant de categories i treue els SUBJECT
  private static void generateDossiers(Dao dao) {
    //dao.executeNativeUpdate("truncate table DWDossier");
    dao.executeUpdate("delete from DWDossier where dossierId > 0");
    //dao.executeNativeUpdate("alter table DWDossier AUTO_INCREMENT=1");
    dao.executeUpdate(
            "insert into DWDossier(dossierId, versionId, creation, description, categoryPath, participantId, type, status, publishable, phase) " +
            "select distinct d.id, v.id, d.creation, cast(d.description as string), d.category.fullDescription, p.idContact, d.type, d.status, d.publishable, pr.phase " +
            "from Dossier d left join d.participants p left join d.versions v left join d.project pr");
  }
 
  private static void generateIssues(Dao dao) {
    //dao.executeNativeUpdate("truncate table DWIssue");
    dao.executeUpdate("delete from DWIssue where issueId > 0");
    //dao.executeNativeUpdate("alter table DWIssue AUTO_INCREMENT=1");
    dao.executeUpdate(
            "insert into DWIssue(issueId, dossierId, versionId, startDate, solvedDate, description, participantId, participantRole, categoryPath, type, priority, status, resolution, pexpenses, phours, pprice, commentCount, rexpenses, rhours, rprice) " +
            "select i.id, i.dossier.id, i.version.id, COALESCE(i.startDate, i.issueDate), i.solvedDate, cast(i.description as string), p.idContact, p.role, i.dossier.category.fullDescription, i.type, i.priority, i.status, i.resolution, i.expenses, i.hours, i.price, 0L, 0.0, 0.0, 0.0 " +
            "from Issue i left join i.participants p");
    dao.executeUpdate(
            "update DWIssue as i " +
            "set commentCount = (select count(id) from IssueComment where issue.id = i.issueId), " +
            "rexpenses = coalesce((select sum(coalesce(expenses,0)) from IssueComment where issue.id = i.issueId),0), " +
            "rhours = coalesce((select sum(coalesce(hours,0)) from IssueComment where issue.id = i.issueId),0), " +
            "rprice = coalesce((select sum(coalesce(price,0)) from IssueComment where issue.id = i.issueId),0)");
  }
 
  private static boolean checkModifications(Dao dao) {
    Long count = (Long) dao.getSingleResultOrNull(
            "select count(distinct d.id) from DWIssue d " +
            "where commentCount <> (" +
            "select count(ic.id) from IssueComment ic " +
            "where ic.issue.id = d.issueId)");
    /*if(count == null || count == 0) {
      count = (Long) dao.getSingleResultOrNull(
            "select count(i) from Issue i, DWIssue d " +
            "where i.id = d.issueId " +
            "and (d.startDate <> COALESCE(i.startDate, i.issueDate) " +
            "or d.solvedDate <> i.solvedDate " + 
            "or (d.solvedDate is null and i.solvedDate is not null))");
    }*/
    if(count == null || count == 0) {
      count = (Long) dao.getSingleResultOrNull(
            "select count(i) from Issue i " +
            "where not exists( " +
            "  select d from DWIssue d where i.id = d.issueId " +
            "  and i.status = d.status " +
            ")");
    }
    if(count == null || count == 0) {
      count = (Long) dao.getSingleResultOrNull(
            "select count(i) from Dossier i " +
            "where not exists( " +
            "  select d from DWDossier d where i.id = d.dossierId " +
            "  and i.category.fullDescription = d.categoryPath " +
            /*"  and i.publishable = d.publishable " +*/
            ")");
    }
    if(count == null || count == 0) {
      count = (Long) dao.getSingleResultOrNull(
            "select count(i) from Project i " +
            "where not exists( " +
            "  select d from DWDossier d where i.dossier.id = d.dossierId and i.phase = d.phase " +
            ") and i.dossier is not null");
    }
    return count != null && count > 0;
  }  
 
  public static void remove(Dao dao, Dossier dossier) {
    SqlClause.delete("DWDossier")
            .where().equal("dossierId", dossier.getId())
            .dao(dao)
            .execute();
  }
 
  public static void remove(Dao dao, Issue issue) {
    SqlClause.delete("DWIssue")
            .where().equal("issueId", issue.getId())
            .dao(dao)
            .execute();
  }
 
}