工业互联网平台
blame | 历史 | 补丁 | 提交 | 提交对比 | 忽略空白
dengzedong
2025-05-21 93b0088bc687bb9b33d226706892e90e4346d608 
 iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/ind/collection/utils/IndSqlUtils.java


@@ -13,6 +13,7 @@


import com.iailab.module.data.ind.item.vo.IndItemAtomVO;


import com.iailab.module.data.ind.item.vo.IndItemDerVO;


import com.iailab.module.data.ind.value.dto.QuerySourceValueDTO;


import lombok.extern.slf4j.Slf4j;


import org.apache.commons.lang3.StringUtils;


import org.springframework.beans.factory.annotation.Autowired;


import org.springframework.stereotype.Component;


@@ -26,6 +27,7 @@


 * @Description


 * @createTime 2024年10月04日


 */


@Slf4j


@Component


public class IndSqlUtils {




@@ -151,20 +153,35 @@


        switch (timeLimit) {


            case TODAY:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("='");


                whereSql.append(" >= '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" < '");


                calendar.add(Calendar.DAY_OF_YEAR, 1);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("'");


                break;


            case YESTERDAY:


                calendar.add(Calendar.DAY_OF_YEAR, -1);


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("='");


                whereSql.append(" < '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" >= '");


                calendar.add(Calendar.DAY_OF_YEAR, -1);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("'");


                break;


            case LAST_DAY_7:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" <= '");


                whereSql.append(" < '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


@@ -175,7 +192,9 @@


                break;


            case LAST_DAY_30:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" <= '");


                whereSql.append(" < '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


@@ -186,7 +205,9 @@


                break;


            case LAST_DAY_90:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" <= '");


                whereSql.append(" < '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


@@ -197,30 +218,36 @@


                break;


            case THIS_MONTH:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("='");


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));


                whereSql.append("'");


                break;


            case LAST_MONTH:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("' < '");


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));


                whereSql.append(" < '");


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" >= '");


                calendar.add(Calendar.MONTH, -1);


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("'");


                break;


            case LAST_MONTH:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" < '");


                calendar.add(Calendar.MONTH, -1);


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" >= '");


                calendar.add(Calendar.MONTH, -1);


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("'");


                break;


            case LAST_MONTH_12:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" <= '");


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));


                whereSql.append(" < '");


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("' >= '");


                whereSql.append(" >= '");


                calendar.add(Calendar.MONTH, -12);


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart()));


                whereSql.append(getYearMonth(calendar,indItem.getMonthStart(),datePattern));


                whereSql.append("'");


                break;


            case THIS_YEAR_MONTH:


@@ -236,15 +263,27 @@


                whereSql.append("'");


                break;


            case LAST_YEAR:


                calendar.add(Calendar.YEAR, -1);


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("='");


                whereSql.append(" <= '");


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" >= '");


                calendar.add(Calendar.YEAR, -1);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("'");


                break;


            case THIS_YEAR:


                whereSql.append(indItem.getTimeLabel());


                whereSql.append("='");


                whereSql.append(" >= '");


                calendar.set(Calendar.MINUTE, 0);


                calendar.set(Calendar.HOUR_OF_DAY, 0);


                calendar.set(Calendar.DAY_OF_YEAR, 1);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("' AND ");


                whereSql.append(indItem.getTimeLabel());


                whereSql.append(" < '");


                calendar.add(Calendar.YEAR, 1);


                whereSql.append(DateUtils.format(calendar.getTime(), datePattern));


                whereSql.append("'");


                break;


@@ -275,6 +314,7 @@


            default:


                break;


        }


        log.info("whereSql:" + whereSql);


        result.setWhereSql(whereSql.toString());




        // 拼接GROUP


@@ -293,23 +333,17 @@


        return result;


    }




    public static String getYearMonth(Calendar now,Integer monthStart) {


    public static String getYearMonth(Calendar calendar,Integer monthStart,String datePattern) {


        Calendar now = (Calendar) calendar.clone();


        int day = now.get(Calendar.DAY_OF_MONTH);




        // 当前月


        if (monthStart > 0) {


            if (day < monthStart) {


                now.add(Calendar.MONTH, -1);


            }


        if (day >= Math.abs(monthStart)) {


            now.add(Calendar.MONTH, 1);


        }




        if (monthStart < 0) {


            if (day >= Math.abs(monthStart)) {


                now.add(Calendar.MONTH, 1);


            }


        }


        now.set(Calendar.DAY_OF_MONTH,Math.abs(monthStart));


        // 定义日期格式化对象，指定格式为 yyyy-MM


        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_MON);


        SimpleDateFormat sdf = new SimpleDateFormat(datePattern);


        // 格式化日期并返回结果


        return sdf.format(now.getTime());


    }