eip_manage/src/main/java/com/yihu/hos/datacollect/dao/DatacollectDao.java

package com.yihu.hos.datacollect.dao;

import com.yihu.hos.datacollect.model.DtoJobDataset;
import com.yihu.hos.datacollect.model.RsJobDataset;
import com.yihu.hos.resource.model.RsDatasourceDataset;
import com.yihu.hos.web.framework.constant.DateConvert;
import com.yihu.hos.web.framework.dao.SQLGeneralDAO;
import com.yihu.hos.web.framework.model.DataGridResult;
import com.yihu.hos.web.framework.model.SimpleChartItem;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

/**
 * Created by hzp on 2016/1/11.
 */
@Repository("DatacollectDao")
public class DatacollectDao extends SQLGeneralDAO {

    public static final String BEAN_ID = "DatacollectDao";

    /**
     * 通过机构代码获取版本号
     */
    
    public String getVersionByQLC(String orgCode) throws Exception
    {
        try{
            String sql = "select version from adapter_scheme_version v "+
                         " left join system_organization o on o.qlc_adapter_version=v.id "+
                         " where o.qlc_org_code ='"+orgCode+"'";
            return super.scalarBySql(sql, String.class);
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return "";
        }
    }

    /**
     * 根据JobId获取Cron表达式
     */
    
    public String getCronByJobId(String jobId)
    {
        try{
            String sql = "select CRON_EXPRESSION from qrtz_cron_triggers where TRIGGER_NAME='"+jobId+"'";
            return super.scalarBySql(sql, String.class);
        }
        catch (Exception e)
        {
            return "";
        }
    }

    /**
     * 获取任务列表
     * @return
     */
    
    public DataGridResult getJobList(Map<String, Object> conditionMap, Integer page, Integer pageSize) throws Exception
    {
        StringBuilder sb = new StringBuilder();

        sb.append("from RsJobConfig t where 1=1 ");
        if (!StringUtils.isEmpty(conditionMap.get("jobName")))
        {
            sb.append(" and t.jobName like '%" + conditionMap.get("jobName") + "%'");
        }

        return getDataGridResult(sb.toString(),page,pageSize);
    }

    /**
     * 获取任务数据集
     * @return
     * @throws Exception
     */
    
    public List<RsJobDataset> getJobDataset(String jobId) throws Exception
    {
        String sql = "select * from rs_job_dataset where job_id='"+jobId+"'";
        return super.queryListBySql(sql, RsJobDataset.class);
    }


    /**
     * 获取版本名称
     */
    
    public String getSchemeAndVersion(String schemeVersionId) throws Exception
    {
        String sql = "select IFNULL( (select  CONCAT_WS(' -- ',s.name,v.name)  from adapter_scheme_version v " +
                "left join adapter_scheme s on s.id = v.scheme_id " +
                "where v.version='"+schemeVersionId+"'),'')";
        return super.scalarBySql(sql, String.class);
    }

    /**
     * 修改任务状态
     */
    
    public void validJob(String id,String valid) throws Exception
    {
        String sql = "update rs_job_config set valid='"+valid+"' where Id='"+id+"'";
        super.execute(sql);
    }

    /**
     * 删除任务关联数据集
     */
    public void deleteJobDatasetByJobId(String jobId) throws Exception
    {
        List<RsJobDataset> list = (List<RsJobDataset>) super.hibernateTemplate.find("from RsJobDataset s where s.jobId=? ", jobId);
        //判断是否存在数据
        if(list!=null && list.size()>0)
        {
            for(RsJobDataset obj :list)
            {
                super.deleteEntity(obj);
            }
        }
    }


    /**
     * 通过版本获取配置的数据集--数据源
     * @throws Exception
     */
    public List<RsDatasourceDataset> getDatasourceDataset(String stdVersion) throws Exception
    {
        String sql = "select * from rs_datasource_dataset where std_version='"+stdVersion+"'";
        return super.queryListBySql(sql, RsDatasourceDataset.class);
    }

    /**
     * 删除数据集数据源关联
     */
    public void deleteDatasourceDatasetById(String id) throws Exception
    {
        String sql = "delete from rs_datasource_dataset where id='"+id+"'";
        super.execute(sql);
    }

    /**
     * 通过任务ID获取相关数据集
     */
    public List<DtoJobDataset> getDatacollectDataset(String jobId) throws Exception
    {
        //通过jobId获取机构版本号
        String sqlVersion ="select a.adapter_std_version\n" +
                "from rs_job_config c,(SELECT s.adapter_std_version,v.version from adapter_scheme s,adapter_scheme_version v where s.id=v.scheme_id) a\n" +
                "where a.version = c.scheme_version\n" +
                "and c.id='"+jobId+"'";
        String version = super.scalarBySql(sqlVersion,String.class);

        String sql = "select d.*,\n" +
                "t.datasource_id,t.datasource_name,t.config,t.type,t.org_code\n" +
                "from rs_job_dataset d\n" +
                "LEFT JOIN (select s.dataset_id,s.datasource_id,ss.name as datasource_name,ss.config,ss.type,ss.org_code \n" +
                "\tfrom rs_datasource_dataset s,(select ds.id,ds.name,ds.config,ds.type,o.code as org_code from system_datasource ds,system_organization o where ds.org_id=o.id) ss where s.datasource_id=ss.id and s.Std_version='"+version+"') t \n" +
                "on d.job_dataset_id = t.dataset_id \n" +
                "where d.job_id='"+jobId+"'\n" +
                "order by d.sort";
        return super.queryListBySql(sql,DtoJobDataset.class);
    }


    /**
     * 获取任务详细日志列表
     */
    
    public DataGridResult getJobLogDetail(Map<String, Object> conditionMap, Integer page, Integer pageSize) throws Exception
    {
        StringBuilder sb = new StringBuilder();
        sb.append("from RsJobLogDetail where 1=1 ");
        if (!StringUtils.isEmpty(conditionMap.get("jobId"))) {
            sb.append(" and job_id ='"+conditionMap.get("jobId")+"'");
        }
        if (!StringUtils.isEmpty(conditionMap.get("jobDatasetId"))) {
            sb.append(" and job_dataset_id ='"+conditionMap.get("jobDatasetId")+"'");
        }
        if (!StringUtils.isEmpty(conditionMap.get("jobStatus"))) {
            sb.append(" and jobStatus ='"+conditionMap.get("jobStatus")+"'");
        }
        if (!StringUtils.isEmpty(conditionMap.get("jobTimeFrom"))) {
            Date time = DateConvert.toDate(conditionMap.get("jobTimeFrom").toString());
            time.setHours(0);
            time.setMinutes(0);
            time.setSeconds(0);
            sb.append(" and startTime >= '"+DateConvert.toString(time)+"'");
        }
        if (!StringUtils.isEmpty(conditionMap.get("jobTimeTo"))) {
            Date time = DateConvert.toDate(conditionMap.get("jobTimeTo").toString());
            time.setHours(0);
            time.setMinutes(0);
            time.setSeconds(0);
            GregorianCalendar gc=new GregorianCalendar();
            gc.setTime(time);
            gc.add(5, 1);
            sb.append(" and startTime < '"+DateConvert.toString(gc.getTime())+"'");
        }
        sb.append("order by startTime desc");
        return getDataGridResult(sb.toString(),page,pageSize);
    }

    /**
     * 获取任务日志列表
     */
    
    public DataGridResult getJobLog(Map<String, Object> conditionMap, Integer page, Integer pageSize) throws Exception
    {
        StringBuilder sb = new StringBuilder();
        sb.append("from RsJobLog where 1=1 ");
        if (!StringUtils.isEmpty(conditionMap.get("jobId"))) {
            sb.append(" and jobId ='"+conditionMap.get("jobId")+"'");
        }
        sb.append("order by jobStartTime desc");

        return getDataGridResult(sb.toString(),page,pageSize);
    }

    /**
     * 获取采集日志统计
     */
    public List<Map<String, Object>> getJobLogCount(String logId) throws Exception
    {
        String sql ="select d.job_log_id,sum(d.Job_dataset_rows) as count,\n" +
                "sum(case d.job_status\n" +
                "    when '1' THEN\n" +
                "         d.Job_dataset_rows\n" +
                "    ELSE\n" +
                "         0\n" +
                "    END) as success,\n" +
                "sum(case d.job_status\n" +
                "    when '3' THEN\n" +
                "         d.Job_dataset_rows\n" +
                "    ELSE\n" +
                "         0\n" +
                "    END) as repeat_num\n" +
                "from rs_job_log_detail d\n";
        if(logId!=null && logId.length()>0)
        {
            sql += "where d.job_log_id ='"+logId+"'\n";
        }
        sql+="group by d.job_log_id ";

        return super.queryListBySql(sql);
    }

    /**
     * 任务详细根据数据集分组
     */
    public List<SimpleChartItem> getJobLogDataset(String logId) throws Exception
    {
        String sql ="select d.job_dataset_name as x,sum(d.Job_dataset_rows) as y1,\n" +
                "                sum(case d.job_status\n" +
                "                    when '1' THEN\n" +
                "                         d.Job_dataset_rows\n" +
                "                    ELSE\n" +
                "                         0\n" +
                "                    END) as y2,\n" +
                "                sum(case d.job_status\n" +
                "                    when '3' THEN\n" +
                "                         d.Job_dataset_rows\n" +
                "                    ELSE\n" +
                "                         0\n" +
                "                    END) as y3\n" +
                "                from rs_job_log_detail d\n" +
                "where d.job_log_id = '"+logId+"'\n" +
                "GROUP BY d.Job_dataset_name";


        return super.queryListBySql(sql,SimpleChartItem.class);
    }
}