package com.yihu.hos.rest.common.dao;

import com.yihu.hos.core.datatype.StringUtil;
import com.yihu.hos.rest.models.rs.DtoJobDataset;
import com.yihu.hos.rest.models.rs.RsJobDataset;
import com.yihu.hos.web.framework.constrant.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 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 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 (!StringUtil.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 = super.queryListBySql("select * from rs_job_dataset where job_id='"+jobId+"'",RsJobDataset.class);
        //判断是否存在数据
        if(list!=null && list.size()>0)
        {
            for(RsJobDataset obj :list)
            {
                super.deleteEntity(obj);
            }
        }
    }

    /**
     * 获取任务详细日志列表
     */
    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 (!StringUtil.isEmpty(conditionMap.get("jobId"))) {
            sb.append(" and job_id ='"+conditionMap.get("jobId")+"'");
        }
        if (!StringUtil.isEmpty(conditionMap.get("jobDatasetId"))) {
            sb.append(" and job_dataset_id ='"+conditionMap.get("jobDatasetId")+"'");
        }
        if (!StringUtil.isEmpty(conditionMap.get("jobStatus"))) {
            sb.append(" and jobStatus ='"+conditionMap.get("jobStatus")+"'");
        }
        if (!StringUtil.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 (!StringUtil.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 (!StringUtil.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);
    }
}
