package cn.estsh.i3plus.ext.mes.apiservice.daoimpl.jx;

import cn.estsh.i3plus.ext.mes.apiservice.dao.jx.IJxOutPutStatisticsDao;
import cn.estsh.i3plus.ext.mes.pojo.model.jx.JxOqcCheckStatisticsReportModel;
import cn.estsh.i3plus.ext.mes.pojo.model.jx.JxOqcLastCheckDetailReportModel;
import cn.estsh.i3plus.ext.mes.pojo.model.jx.JxOutPutStatisticsQtyModel;
import cn.estsh.i3plus.ext.mes.pojo.model.jx.OqcLastCheckModel;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtConstWords;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtEnumUtil;
import cn.estsh.i3plus.pojo.base.common.Pager;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.List;

/**
 * @Author: wangjie
 * @CreateDate: 2019/11/21 11:29 上午
 * @Description:
 **/
@Service
public class JxOutPutStatisticsDao implements IJxOutPutStatisticsDao {

    @Autowired
    private EntityManager entityManager;

    @Override
    public int queryOutPutStatisticsOqcByPagerCount(OqcLastCheckModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select count(distinct opso.id) ");

        packHqlForQueryOutPutStatisticsOqc(hql, model);

        Query query = entityManager.createQuery(hql.toString(), Long.class);

        packParameterForQueryOutPutStatisticsOqc(query, model);

        return ((Long) query.getSingleResult()).intValue();
    }

    @Override
    public List<OqcLastCheckModel> queryOutPutStatisticsOqcByPager(OqcLastCheckModel model, Pager pager) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + OqcLastCheckModel.class.getName() + "( opso.id, opso.pid, opso.organizeCode, opso.workOrderNo, opso.workCenterCode, " +
                " opso.partNo, opso.partNameRdd, opso.workTime, opso.shiftCode, opso.spotCheckQty, opso.checkLimit, opso.checkNo, opso.checkResult, " +
                " opso.remark, opso.confirmTime, opso.checkUser, opso.workOrderType, opso.isAddReworkRange ) ");
        packHqlForQueryOutPutStatisticsOqc(hql, model);
        hql.append(" order by opso.createDatetime desc ");
        Query query = entityManager.createQuery(hql.toString(), OqcLastCheckModel.class);

        packParameterForQueryOutPutStatisticsOqc(query, model);

        query.setMaxResults(pager.getPageSize());
        if (pager.getCurrentPage() == 0) {
            pager.setCurrentPage(1);
        }
        query.setFirstResult(pager.getCurrentPage() * pager.getPageSize() - pager.getPageSize());
        return query.getResultList();
    }

    private void packHqlForQueryOutPutStatisticsOqc(StringBuffer hql, OqcLastCheckModel model) {
        hql.append(" from MesOutPutStatisticsOqc as opso ");
        hql.append(" left join MesPartExt as p on (opso.partNo = p.partNo and p.organizeCode = :organizeCode and p.isValid = :isValid and p.isDeleted = :isDeleted) ");
        hql.append(" where opso.organizeCode = :organizeCode ");
        hql.append(" and opso.isValid = :isValid ");
        hql.append(" and opso.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            hql.append(" and opso.workOrderNo = :workOrderNo ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and opso.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and opso.workCenterCode = :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getShiftCode())) {
            hql.append(" and opso.shiftCode = :shiftCode ");
        }
        if (!StringUtils.isEmpty(model.getCheckResult())) {
            hql.append(" and opso.checkResult = :checkResult ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            hql.append(" and opso.workTime >= :workTimeStart ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            hql.append(" and opso.workTime <= :workTimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            hql.append(" and opso.workOrderType = :workOrderType ");
        }
        hql.append(" and p.isOqc = :isOqc ");
    }

    private void packParameterForQueryOutPutStatisticsOqc(Query query, OqcLastCheckModel model) {
        query.setParameter(MesExtConstWords.ORGANIZE_CODE, model.getOrganizeCode());
        query.setParameter(MesExtConstWords.IS_VALID, CommonEnumUtil.IS_VAILD.VAILD.getValue());
        query.setParameter(MesExtConstWords.IS_DELETED, MesExtEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
        query.setParameter(MesExtConstWords.IS_OQC, MesExtEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_NO, model.getWorkOrderNo());
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            query.setParameter(MesExtConstWords.PART_NO, model.getPartNo());
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            query.setParameter(MesExtConstWords.WORK_CENTER_CODE, model.getWorkCenterCode());
        }
        if (!StringUtils.isEmpty(model.getShiftCode())) {
            query.setParameter(MesExtConstWords.SHIFT_CODE, model.getShiftCode());
        }
        if (!StringUtils.isEmpty(model.getCheckResult())) {
            query.setParameter(MesExtConstWords.CHECK_RESULT, model.getCheckResult());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            query.setParameter(MesExtConstWords.WORK_TIME_START, model.getWorkTimeStart());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            query.setParameter(MesExtConstWords.WORK_TIME_END, model.getWorkTimeEnd());
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_TYPE, model.getWorkOrderType());
        }
    }

    @Override
    public int queryOqcLastCheckDetailPagerCount(JxOqcLastCheckDetailReportModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select count(distinct opso.id) ");

        packHqlForQueryOqcLastCheckDetail(hql, model);

        Query query = entityManager.createQuery(hql.toString(), Long.class);

        packParameterForQueryOqcLastCheckDetail(query, model);

        return ((Long) query.getSingleResult()).intValue();
    }

    @Override
    public List<JxOqcLastCheckDetailReportModel> queryOqcLastCheckDetailPager(JxOqcLastCheckDetailReportModel model, Pager pager) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + JxOqcLastCheckDetailReportModel.class.getName() + "(opso.workTime, opso.workOrderNo, opso.workOrderType, opso.partNo, opso.partNameRdd, opso.workCenterCode, " +
                " opso.shiftCode, opso.planQty, opso.spotCheckQty, opso.checkLimit, opso.checkResult, opso.checkNo, opso.checkUser, opso.confirmTime, opso.remark, p.colorCode) ");

        packHqlForQueryOqcLastCheckDetail(hql, model);

        hql.append(" order by opso.modifyDatetime desc ");
        Query query = entityManager.createQuery(hql.toString(), JxOqcLastCheckDetailReportModel.class);

        packParameterForQueryOqcLastCheckDetail(query, model);

        query.setMaxResults(pager.getPageSize());
        if (pager.getCurrentPage() == 0) {
            pager.setCurrentPage(1);
        }
        query.setFirstResult(pager.getCurrentPage() * pager.getPageSize() - pager.getPageSize());
        return query.getResultList();
    }

    private void packHqlForQueryOqcLastCheckDetail(StringBuffer hql, JxOqcLastCheckDetailReportModel model) {
        hql.append(" from MesOutPutStatisticsOqc as opso ");
        hql.append(" left join MesPartExt as p on (p.partNo = opso.partNo and p.organizeCode = :organizeCode and p.isValid = :isValid and p.isDeleted = :isDeleted) ");
        hql.append(" where opso.organizeCode = :organizeCode ");
        hql.append(" and opso.isValid = :isValid ");
        hql.append(" and opso.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            hql.append(" and opso.workOrderNo = :workOrderNo ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and opso.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and opso.workCenterCode = :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getCheckNo())) {
            hql.append(" and opso.checkNo = :checkNo ");
        }
        if (!StringUtils.isEmpty(model.getCheckResult())) {
            hql.append(" and opso.checkResult = :checkResult ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            hql.append(" and opso.workTime >= :workTimeStart ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            hql.append(" and opso.workTime <= :workTimeEnd ");
        }
        if (model.getWorkOrderType() != null) {
            hql.append(" and opso.workOrderType = :workOrderType ");
        }
        hql.append(" and p.isOqc = :isOqc ");
    }

    private void packParameterForQueryOqcLastCheckDetail(Query query, JxOqcLastCheckDetailReportModel model) {
        query.setParameter(MesExtConstWords.ORGANIZE_CODE, model.getOrganizeCode());
        query.setParameter(MesExtConstWords.IS_VALID, CommonEnumUtil.IS_VAILD.VAILD.getValue());
        query.setParameter(MesExtConstWords.IS_DELETED, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
        query.setParameter(MesExtConstWords.IS_OQC, MesExtEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_NO, model.getWorkOrderNo());
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            query.setParameter(MesExtConstWords.PART_NO, model.getPartNo());
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            query.setParameter(MesExtConstWords.WORK_CENTER_CODE, model.getWorkCenterCode());
        }
        if (!StringUtils.isEmpty(model.getCheckNo())) {
            query.setParameter(MesExtConstWords.CHECK_NO, model.getCheckNo());
        }
        if (!StringUtils.isEmpty(model.getCheckResult())) {
            query.setParameter(MesExtConstWords.CHECK_RESULT, model.getCheckResult());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            query.setParameter(MesExtConstWords.WORK_TIME_START, model.getWorkTimeStart());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            query.setParameter(MesExtConstWords.WORK_TIME_END, model.getWorkTimeEnd());
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_TYPE, model.getWorkOrderType());
        }
    }

    @Override
    public List<JxOqcCheckStatisticsReportModel> queryOqcCheckStatisticsList(JxOqcCheckStatisticsReportModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + JxOqcCheckStatisticsReportModel.class.getName() + "(opso.workCenterCode, " +
                " opso.workOrderNo, opso.partNo, opso.partNameRdd, opso.workTime, opso.shiftCode, " +
                " opso.spotCheckQty, opso.checkResult, p.salesTo, p.categoryCode3) ");
        hql.append(" from MesOutPutStatisticsOqc as opso ");
        hql.append(" left join MesPartExt as p on (p.partNo = opso.partNo and p.organizeCode = :organizeCode " +
                " and p.isValid = :isValid and p.isDeleted = :isDeleted) ");
        hql.append(" where opso.organizeCode = :organizeCode ");
        hql.append(" and opso.isValid = :isValid ");
        hql.append(" and opso.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and opso.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            hql.append(" and opso.workTime >= :workTimeStart ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            hql.append(" and opso.workTime <= :workTimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getCategoryCode3())) {
            hql.append(" and p.categoryCode3 = :categoryCode3 ");
        }
        if (!StringUtils.isEmpty(model.getSalesTo())) {
            hql.append(" and p.salesTo = :salesTo ");
        }
        if (!StringUtils.isEmpty(model.getNeqWorkOrderType())) {
            hql.append(" and opso.workOrderType != :workOrderType");
        }
        hql.append(" and p.isOqc = :isOqc ");

        Query query = entityManager.createQuery(hql.toString(), JxOqcCheckStatisticsReportModel.class);

        query.setParameter(MesExtConstWords.ORGANIZE_CODE, model.getOrganizeCode());
        query.setParameter(MesExtConstWords.IS_VALID, CommonEnumUtil.IS_VAILD.VAILD.getValue());
        query.setParameter(MesExtConstWords.IS_DELETED, MesExtEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
        query.setParameter(MesExtConstWords.IS_OQC, MesExtEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
        if (!StringUtils.isEmpty(model.getPartNo())) {
            query.setParameter(MesExtConstWords.PART_NO, model.getPartNo());
        }
        if (!StringUtils.isEmpty(model.getCategoryCode3())) {
            query.setParameter(MesExtConstWords.CATEGORY_CODE3, model.getCategoryCode3());
        }
        if (!StringUtils.isEmpty(model.getSalesTo())) {
            query.setParameter(MesExtConstWords.SALES_TO, model.getSalesTo());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            query.setParameter(MesExtConstWords.WORK_TIME_START, model.getWorkTimeStart());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            query.setParameter(MesExtConstWords.WORK_TIME_END, model.getWorkTimeEnd());
        }
        if (!StringUtils.isEmpty(model.getNeqWorkOrderType())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_TYPE, model.getNeqWorkOrderType());
        }

        return query.getResultList();
    }

    @Override
    public List<JxOutPutStatisticsQtyModel> queryOutPutStatisticsListByPart(JxOutPutStatisticsQtyModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + JxOutPutStatisticsQtyModel.class.getName() + "(ops.workOrderNo, ops.workTime, ops.shiftCode, ops.qty) ");
        packHqlForQueryOutPutStatisticsList(hql, model);
        hql.append(" order by ops.createDatetime desc ");
        Query query = entityManager.createQuery(hql.toString(), JxOutPutStatisticsQtyModel.class);

        packParameterForQueryOutPutStatisticsList(query, model);

        return query.getResultList();
    }

    private void packHqlForQueryOutPutStatisticsList(StringBuffer hql, JxOutPutStatisticsQtyModel model) {
        hql.append(" from MesOutPutStatistics as ops ");
        hql.append(" left join MesPartExt as pe on (ops.partNo = pe.partNo and pe.organizeCode = :organizeCode and pe.isValid = :isValid and pe.isDeleted = :isDeleted) ");
        hql.append(" where ops.organizeCode = :organizeCode ");
        hql.append(" and ops.isValid = :isValid ");
        hql.append(" and ops.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and ops.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            hql.append(" and ops.workTime >= :workTimeStart ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            hql.append(" and ops.workTime <= :workTimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getCategoryCode3())) {
            hql.append(" and pe.categoryCode3 = :categoryCode3 ");
        }
        if (!StringUtils.isEmpty(model.getSalesTo())) {
            hql.append(" and pe.salesTo = :salesTo ");
        }
        hql.append(" and pe.isOqc = :isOqc ");
    }

    private void packParameterForQueryOutPutStatisticsList(Query query, JxOutPutStatisticsQtyModel model) {
        query.setParameter(MesExtConstWords.ORGANIZE_CODE, model.getOrganizeCode());
        query.setParameter(MesExtConstWords.IS_VALID, CommonEnumUtil.IS_VAILD.VAILD.getValue());
        query.setParameter(MesExtConstWords.IS_DELETED, CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue());
        query.setParameter(MesExtConstWords.IS_OQC, CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue());
        if (!StringUtils.isEmpty(model.getPartNo())) {
            query.setParameter(MesExtConstWords.PART_NO, model.getPartNo());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            query.setParameter(MesExtConstWords.WORK_TIME_START, model.getWorkTimeStart());
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            query.setParameter(MesExtConstWords.WORK_TIME_END, model.getWorkTimeEnd());
        }
        if (!StringUtils.isEmpty(model.getCategoryCode3())) {
            query.setParameter(MesExtConstWords.CATEGORY_CODE3, model.getCategoryCode3());
        }
        if (!StringUtils.isEmpty(model.getSalesTo())) {
            query.setParameter(MesExtConstWords.SALES_TO, model.getSalesTo());
        }
    }

}
