package com.defaultcompany.web.gantt.dao;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.uengine.persistence.dao.DAOFactory;
import org.uengine.persistence.dao.GanttChartDAO;
import org.uengine.util.UEngineUtil;
import org.uengine.util.dao.DefaultConnectionFactory;
import org.uengine.util.dao.GenericDAO;
import com.defaultcompany.web.gantt.model.GanttChartModel;
import com.defaultcompany.web.strategy.StrategyService;
public class GanttChartWebDAO {
//protected JdbcTemplate jdbcTemplate;
protected DefaultConnectionFactory dcf;
public GanttChartWebDAO(DefaultConnectionFactory dcf) {
this.dcf = dcf;
}
public void init(int viewYear,String endpoint, String pd, String globalCom, String orderby, boolean loggedUserIsMaster, String strategyId){
Calendar nowTemp;
int firstDayOfMonthTemp =0;
int lastDateOfMonthTemp=0;
Calendar firstDateTemp;
Calendar lastDateTemp;
int monthOfNowTemp;
nowTemp = Calendar.getInstance();
nowTemp.add(Calendar.YEAR, viewYear+1);
setNowDate(nowTemp);
firstDateTemp = (Calendar)nowTemp.clone();
firstDateTemp.set(Calendar.DATE, 1);
setFirstDate(firstDateTemp);
firstDayOfMonthTemp = firstDateTemp.get(Calendar.DAY_OF_WEEK) - 2;
setFirstDayOfMonth(firstDayOfMonthTemp);
lastDateTemp = (Calendar)nowTemp.clone();
lastDateTemp.set(Calendar.DATE, 27);
monthOfNowTemp = nowTemp.get(Calendar.MONTH);
setMonthOfNow(monthOfNowTemp);
while(lastDateTemp.get(Calendar.MONTH)==monthOfNowTemp){
lastDateOfMonthTemp = lastDateTemp.get(Calendar.DATE);
lastDateTemp.setTimeInMillis(lastDateTemp.getTimeInMillis() + 86400000L);
}
setLastDayOfMonth(lastDateOfMonthTemp);
lastDateTemp.setTimeInMillis(lastDateTemp.getTimeInMillis() - 86400000L);
setLastDate(lastDateTemp);
setEndpoint(endpoint);
setPd(pd);
setOrderby(orderby);
setLoggedUserIsMaster(loggedUserIsMaster);
setGlobalCom(globalCom);
List<Integer> strategyIds = new ArrayList<Integer>();
if(UEngineUtil.isNotEmpty(strategyId)){
StrategyService strategyService = new StrategyService();
strategyIds = strategyService.getStrategyIdListById(strategyId);
strategyIds.add(Integer.parseInt(strategyId)); //add itself again
}
//setStrategyIds(strategyIds);
}
List strategyIds;
public void setStrategyIds(List strategyIds){
this.strategyIds = strategyIds;
}
public List getStrategyIds(){
return strategyIds;
}
Calendar firstDate;
public void setFirstDate(Calendar firstDate){
this.firstDate = firstDate;
}
public Calendar getFirstDate(){
return firstDate;
}
Calendar lastDate;
public void setLastDate(Calendar lastDate){
this.lastDate = lastDate;
}
public Calendar getLastDate(){
return lastDate;
}
Calendar now;
public void setNowDate(Calendar nowDate){
this.now = nowDate;
}
public Calendar getNowDate(){
return now;
}
int firstDayOfMonth;
public void setFirstDayOfMonth(int month){
this.firstDayOfMonth = month;
}
public int getFirstDayOfMonth(){
return firstDayOfMonth;
}
int lastDateOfMonth;
public void setLastDayOfMonth(int month){
this.lastDateOfMonth = month;
}
public int getLastDayOfMonth(){
return lastDateOfMonth;
}
int monthOfNow;
public void setMonthOfNow(int month){
this.monthOfNow = month;
}
public int getMonthOfNow(){
return monthOfNow;
}
String endpoint;
public void setEndpoint(String endpoint){
this.endpoint = endpoint;
}
public String getEndpoint(){
return endpoint;
}
String pd;
public void setPd(String pd){
this.pd = pd;
}
public String getPd(){
return pd;
}
String orderby;
public void setOrderby(String orderby){
this.orderby = orderby;
}
public String getOrderby(){
return orderby;
}
boolean loggedUserIsMaster;
public void setLoggedUserIsMaster(boolean loggedUserIsMaster){
this.loggedUserIsMaster = loggedUserIsMaster;
}
public boolean getLoggedUserIsMaster(){
return loggedUserIsMaster;
}
String globalCom;
public void setGlobalCom(String globalCom){
this.globalCom = globalCom;
}
public String getGlobalCom(){
return globalCom;
}
private String getDateFunction() throws Exception {
boolean useOracle = DAOFactory.getInstance(null).getDBMSProductName().startsWith("Oracle");
boolean useMySQL = DAOFactory.getInstance(null).getDBMSProductName().startsWith("MySQL");
boolean useMsSQL = DAOFactory.getInstance(null).getDBMSProductName().startsWith("MSsql");
boolean useHSQL = DAOFactory.getInstance(null).getDBMSProductName().startsWith("HSQL");
String dateFunction = null;
if (useOracle) dateFunction = "SYSDATE";
else if (useMsSQL) dateFunction = "getdate()";
else {
dateFunction = "CAST(curdate() AS "+(useMySQL?"DATE":"TIMESTAMP")+")";
}
return dateFunction;
}
private String makeGanttChartCountWebQuery() throws Exception {
String FIRST_DATE = Integer.toString( firstDate.get(Calendar.YEAR) );
FIRST_DATE += "-";
FIRST_DATE += "01";
FIRST_DATE += "-";
FIRST_DATE += "01";
String LAST_DATE = Integer.toString( lastDate.get(Calendar.YEAR) );
LAST_DATE += "-";
LAST_DATE += "12";
LAST_DATE += "-";
LAST_DATE += "31";
StringBuilder filtering = new StringBuilder();
if (!"".equals(endpoint)) {
String[] endpoints = endpoint.split(";");
filtering.append(" and bpm_worklist.endpoint in (");
StringBuilder ss = new StringBuilder();
for (int i=0; i<endpoints.length; i++) {
if (ss.length() > 0) ss.append(", ");
ss.append("?endpoint").append(String.valueOf(i));
}
filtering.append(ss).append(")");
}
if (!"".equals(pd)) {
String[] pds = pd.split(";");
filtering.append(" and bpm_procinst.defid in (");
StringBuilder ss = new StringBuilder();
for (int i=0; i<pds.length; i++) {
if (ss.length() > 0) ss.append(", ");
ss.append("?pd").append(String.valueOf(i));
}
filtering.append(ss).append(")");
}
// if (strategyIds.size() > 0) {
// filtering.append(" and bpm_procinst.strategyId in (");
//
// StringBuilder ss = new StringBuilder();
// for (int i=0; i<strategyIds.size(); i++) {
// if (ss.length() > 0) ss.append(", ");
// ss.append(String.valueOf(strategyIds.get(i)));
// }
//
// filtering.append(ss).append(")");
// }
StringBuilder sql = new StringBuilder();
sql.append(" SELECT count(1) TotalCount");
sql.append(" from bpm_worklist \n");
sql.append(" ,bpm_procinst \n");
sql.append(" where bpm_procinst.isdeleted=0 \n");
//sql.append( "".equals(pd) ? "" : "and bpm_procinst.defid = ?pd ");
sql.append(" and bpm_worklist.instId = bpm_procinst.instId \n");
sql.append(" and bpm_worklist.status<>'CANCELLED' \n");
sql.append(" and CASE WHEN bpm_worklist.startdate > cast('"+FIRST_DATE+"' as date) \n");
sql.append(" THEN bpm_worklist.startdate \n");
sql.append(" ELSE cast('"+FIRST_DATE+"' as date) \n");
sql.append(" END <= cast('"+LAST_DATE+"' as date) \n");
sql.append(" and CASE \n");
sql.append(" WHEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then " + getDateFunction() + " \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) < cast('"+LAST_DATE+"' as date) \n");
sql.append(" THEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then " + getDateFunction() + " \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) \n");
sql.append(" ELSE \n");
sql.append(" cast('"+LAST_DATE+"' as date) \n");
sql.append(" END >= cast('"+FIRST_DATE+"' as date) \n");
sql.append(filtering);
if(!loggedUserIsMaster)
{
sql.append(" AND bpm_procinst.defid in (SELECT distinct bpm_procdef.defid FROM bpm_procdef WHERE comcode = ?globalcom)");
}
return sql.toString();
}
private String makeGanttChartWebQuery() throws Exception {
String FIRST_DATE = Integer.toString( firstDate.get(Calendar.YEAR) );
FIRST_DATE += "-";
FIRST_DATE += "01";
FIRST_DATE += "-";
FIRST_DATE += "01";
String LAST_DATE = Integer.toString( lastDate.get(Calendar.YEAR) );
LAST_DATE += "-";
LAST_DATE += "12";
LAST_DATE += "-";
LAST_DATE += "31";
StringBuilder filtering = new StringBuilder();
if (!"".equals(endpoint)) {
String[] endpoints = endpoint.split(";");
filtering.append(" and bpm_worklist.endpoint in (");
StringBuilder ss = new StringBuilder();
for (int i=0; i<endpoints.length; i++) {
if (ss.length() > 0) ss.append(", ");
ss.append("?endpoint").append(String.valueOf(i));
}
filtering.append(ss).append(")");
}
if (!"".equals(pd)) {
String[] pds = pd.split(";");
filtering.append(" and bpm_procinst.defid in (");
StringBuilder ss = new StringBuilder();
for (int i=0; i<pds.length; i++) {
if (ss.length() > 0) ss.append(", ");
ss.append("?pd").append(String.valueOf(i));
}
filtering.append(ss).append(")");
}
// if (strategyIds.size() > 0) {
// filtering.append(" and bpm_procinst.strategyId in (");
//
// StringBuilder ss = new StringBuilder();
// for (int i=0; i<strategyIds.size(); i++) {
// if (ss.length() > 0) ss.append(", ");
// ss.append(String.valueOf(strategyIds.get(i)));
// }
//
// filtering.append(ss).append(")");
// }
StringBuilder sql = new StringBuilder();
sql.append(" select TASKID \n");
sql.append(" ,TITLE \n");
sql.append(" ,DESCRIPTION \n");
sql.append(" ,ENDPOINT \n");
sql.append(" ,bpm_worklist.STATUS \n");
//sql.append(" ,concat(bpm_procinst.status,'') as inststatus \n");
sql.append(" ,CASE \n");
sql.append(" WHEN bpm_procinst.status <> 'COMPLETED' AND (case when bpm_procinst.duedate is null \n");
sql.append(" then "+getDateFunction()+" \n");
sql.append(" else bpm_procinst.duedate \n");
sql.append(" end ) < "+getDateFunction()+" \n");
sql.append(" THEN \n");
sql.append(" 'delay' \n");
sql.append(" ELSE \n");
sql.append(" bpm_procinst.status \n");
sql.append(" END inststatus \n");
/*
sql.append(" CASE \n");
sql.append(" WHEN bpm_procinst.status <> 'COMPLETED' AND ( \n");
sql.append(" case when bpm_procinst.duedate is null \n");
sql.append(" then CAST(curdate() AS DATE) \n");
sql.append(" else bpm_procinst.duedate \n");
sql.append(" end ) < CAST(curdate() AS DATE) \n");
\n");
sql.append(" THEN \n");
sql.append(" 'delay' \n");
sql.append(" ELSE
sql.append(" bpm_procinst.status
sql.append(" END inststatus
*/
sql.append(" ,PRIORITY \n");
sql.append(" ,startdate \n");
sql.append(" ,CASE \n");
sql.append(" WHEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then "+getDateFunction()+" \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) < cast('"+LAST_DATE+"' as date) \n");
sql.append(" THEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then "+getDateFunction()+" \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) \n");
sql.append(" ELSE \n");
sql.append(" cast('"+LAST_DATE+"' as date) \n");
sql.append(" END enddate \n");
sql.append(" ,bpm_worklist.DUEDATE \n");
sql.append(" ,bpm_worklist.INSTID \n");
sql.append(" ,bpm_worklist.DEFID \n");
sql.append(" ,bpm_worklist.DEFNAME \n");
sql.append(" ,TRCTAG \n");
sql.append(" ,TOOL \n");
sql.append(" ,PARAMETER \n");
sql.append(" ,GROUPID \n");
sql.append(" ,GROUPNAME \n");
sql.append(" ,bpm_worklist.EXT1 \n");
sql.append(" ,bpm_worklist.EXT2 \n");
sql.append(" ,bpm_worklist.EXT3 \n");
sql.append(" ,ISURGENT \n");
sql.append(" ,HASATTACHFILE \n");
sql.append(" ,HASCOMMENT \n");
sql.append(" ,DOCUMENTCATEGORY \n");
sql.append(" ,bpm_worklist.ISDELETED \n");
sql.append(" ,bpm_worklist.ROOTINSTID \n");
sql.append(" ,DISPATCHOPTION \n");
sql.append(" ,DISPATCHPARAM1 \n");
sql.append(" ,ROLENAME \n");
sql.append(" ,RESNAME \n");
sql.append(" ,REFROLENAME \n");
sql.append(" ,EXECSCOPE \n");
sql.append(" ,SAVEDATE \n");
sql.append(" ,DEFVERID \n");
sql.append(" ,DEFPATH \n");
sql.append(" ,DEFMODDATE \n");
sql.append(" ,STARTEDDATE \n");
sql.append(" ,FINISHEDDATE \n");
sql.append(" ,INFO \n");
sql.append(" ,NAME \n");
sql.append(" ,ISADHOC \n");
sql.append(" ,ISARCHIVE \n");
sql.append(" ,ISSUBPROCESS \n");
sql.append(" ,ISEVENTHANDLER \n");
sql.append(" ,MAININSTID \n");
sql.append(" ,MAINDEFVERID \n");
sql.append(" ,MAINACTTRCTAG \n");
sql.append(" ,MAINEXECSCOPE \n");
sql.append(" ,ABSTRCPATH \n");
sql.append(" ,DONTRETURN \n");
sql.append(" ,INITEP \n");
sql.append(" ,INITRSNM \n");
sql.append(" ,CURREP \n");
sql.append(" ,CURRRSNM \n");
sql.append(" from bpm_worklist \n");
sql.append(" ,bpm_procinst \n");
sql.append(" where bpm_procinst.isdeleted=0 \n");
//sql.append( "".equals(pd) ? "" : "and bpm_procinst.defid = ?pd ");
sql.append(" and bpm_worklist.instId = bpm_procinst.instId \n");
sql.append(" and bpm_worklist.status<>'CANCELLED' \n");
sql.append(" and CASE WHEN bpm_worklist.startdate > cast('"+FIRST_DATE+"' as date) \n");
sql.append(" THEN bpm_worklist.startdate \n");
sql.append(" ELSE cast('"+FIRST_DATE+"' as date) \n");
sql.append(" END <= cast('"+LAST_DATE+"' as date) \n");
sql.append(" and CASE \n");
sql.append(" WHEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then "+getDateFunction()+" \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) < cast('"+LAST_DATE+"' as date) \n");
sql.append(" THEN \n");
sql.append(" (case when bpm_worklist.enddate is null \n");
sql.append(" then "+getDateFunction()+" \n");
sql.append(" else bpm_worklist.enddate \n");
sql.append(" end ) \n");
sql.append(" ELSE \n");
sql.append(" cast('"+LAST_DATE+"' as date) \n");
sql.append(" END >= cast('"+FIRST_DATE+"' as date) \n");
sql.append(filtering);
if(!loggedUserIsMaster)
{
sql.append(" AND bpm_procinst.defid in (SELECT distinct bpm_procdef.defid FROM bpm_procdef WHERE comcode = ?globalcom)");
}
sql.append(
" order by bpm_worklist.instId desc, bpm_worklist.taskid,bpm_worklist.resname ");
return sql.toString();
}
public GanttChartModel makeGanttChart() throws Exception {
String queryStr = makeGanttChartWebQuery();
GanttChartDAO task = (GanttChartDAO)GenericDAO.createDAOImpl(DefaultConnectionFactory.create(), queryStr.toString(), GanttChartDAO.class);
task.set("pd", pd);
String[] endpoints = endpoint.split(";");
for (int i=0; i<endpoints.length; i++) {
task.set("endpoint"+i, endpoints[i]);
}
String[] pds = pd.split(";");
for (int i=0; i<pds.length; i++) {
task.set("pd"+i, pds[i]);
}
//task.set("endpoint", endpoint);
task.set("globalcom", globalCom);
task.select();
GanttChartModel ganttChartModel=new GanttChartModel();
ganttChartModel.setEndpoint(getEndpoint());
ganttChartModel.setFirstDate(getFirstDate());
ganttChartModel.setFirstDayOfMonth(getFirstDayOfMonth());
ganttChartModel.setLastDayOfMonth(getLastDayOfMonth());
ganttChartModel.setGanntChartDao(task);
ganttChartModel.setLastDate(getLastDate());
ganttChartModel.setLoggedUserIsMaster(getLoggedUserIsMaster());
ganttChartModel.setMonthOfNow(getMonthOfNow());
ganttChartModel.setNowDate(getNowDate());
ganttChartModel.setOrderby(getOrderby());
ganttChartModel.setPd(getPd());
return ganttChartModel;
}
public int getGanttChartCount() throws Exception {
String queryStr = makeGanttChartCountWebQuery();
GanttChartDAO task = (GanttChartDAO)GenericDAO.createDAOImpl(DefaultConnectionFactory.create(), queryStr.toString(), GanttChartDAO.class);
task.set("pd", pd);
task.set("globalcom", globalCom);
String[] endpoints = endpoint.split(";");
for (int i=0; i<endpoints.length; i++) {
task.set("endpoint"+i, endpoints[i]);
}
String[] pds = pd.split(";");
for (int i=0; i<pds.length; i++) {
task.set("pd"+i, pds[i]);
}
task.select();
int totalSize=0;
while(task.next()){
totalSize=task.getTotalCount();
}
return totalSize;
}
}