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

import cn.estsh.i3plus.ext.mes.apiservice.dao.bu3.ISxBuWorkOrderDao;
import cn.estsh.i3plus.ext.mes.pojo.model.SxPackSnReportModel;
import cn.estsh.i3plus.ext.mes.pojo.model.WorkOrderPageVo;
import cn.estsh.i3plus.ext.mes.pojo.model.WorkOrderQueryDto;
import cn.estsh.i3plus.ext.mes.pojo.util.MesExtConstWords;
import cn.estsh.i3plus.platform.common.tool.TimeTool;
import cn.estsh.i3plus.pojo.base.common.Pager;
import cn.estsh.i3plus.pojo.base.enumutil.CommonEnumUtil;
import cn.estsh.i3plus.pojo.base.enumutil.MesEnumUtil;
import cn.estsh.i3plus.pojo.base.tool.DdlPreparedPack;
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.math.BigInteger;
import java.util.List;

/**
 * @PROJECT_NAME: i3plus-mes-panasonic-dev
 * @DESCRIPTION:
 * @USER: xinwang.yi
 * @DATE: 2023-08-08 16:34
 */
@Service
public class SxBuWorkOrderDao implements ISxBuWorkOrderDao {

    @Autowired
    private EntityManager entityManager;

    @Override
    public BigInteger queryWorkOrderPagerCount(WorkOrderQueryDto workOrderQueryDto) {
        StringBuffer hql = new StringBuffer();

        hql.append(" select count( result.order_no ) from ( ");

        hql.append(" select wo.order_no, count( ups.id ) as unbindSnCount, wo.work_center_code ");

        packHqlForNativeQueryWorkOrder(hql, workOrderQueryDto);

        hql.append(" group by wo.order_no ");

        hql.append(" having wo.work_center_code is not null and  wo.work_center_code != '' ");

        if (workOrderQueryDto.getIsExistUnbindSn() != null && workOrderQueryDto.getIsExistUnbindSn() == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()) {
            hql.append(" and unbindSnCount > 0 ");
        } else if(workOrderQueryDto.getIsExistUnbindSn() != null && workOrderQueryDto.getIsExistUnbindSn() == CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()) {
            hql.append(" and unbindSnCount = 0 ");
        }

        hql.append(" ) as result ");

        Query query = entityManager.createNativeQuery(hql.toString());

        packParameterForQueryWorkOrder(query, workOrderQueryDto);

        return (BigInteger) query.getSingleResult();
    }

    @Override
    public List<WorkOrderPageVo> queryWorkOrderPager(WorkOrderQueryDto workOrderQueryDto, Pager pager) {
        StringBuffer hql = new StringBuffer();


        hql.append(" select new " + WorkOrderPageVo.class.getName() + "( wo.id, wo.createUser, wo.createDatetime, " +
                " wo.modifyUser, wo.modifyDatetime, wo.orderNo , wo.seq, wo.seq1, wo.startTime, wo.endTime, wo.workCenterCode," +
                " wo.workOrderSource, wo.workOrderStatus, wo.workOrderType, wo.qty, wo.planQty, wo.completeQty, wo.repairQty, " +
                " wo.partNo, wo.partNameRdd, wo.areaCode, wo.erpWarehouse, wo.memo, wo.outPutFlag, wo.scheduleDate, count(ups.id) as unbindSnCount)");

        packHqlForQueryWorkOrder(hql, workOrderQueryDto);

        hql.append(" group by wo.orderNo ");

        if (workOrderQueryDto.getIsExistUnbindSn() != null && workOrderQueryDto.getIsExistUnbindSn() == CommonEnumUtil.TRUE_OR_FALSE.TRUE.getValue()) {
            hql.append(" having count(ups.id) > 0 ");
        } else if(workOrderQueryDto.getIsExistUnbindSn() != null && workOrderQueryDto.getIsExistUnbindSn() == CommonEnumUtil.TRUE_OR_FALSE.FALSE.getValue()) {
            hql.append(" having count(ups.id) = 0 ");
        }

        if (StringUtils.isEmpty(workOrderQueryDto.getOrderByParam())) {
            hql.append(" order by wo.modifyDatetime desc ");
        } else {
            if (workOrderQueryDto.getOrderByParam().equals(MesExtConstWords.SEQ) ||
                    workOrderQueryDto.getOrderByParam().equals(MesExtConstWords.ORDER_NO) ) {
                hql.append(" order by wo." + workOrderQueryDto.getOrderByParam() + " " + (workOrderQueryDto.getAscOrDesc() == 1?" asc":" desc") );
            } else {
                hql.append(" order by wo." + workOrderQueryDto.getOrderByParam() + " " + (workOrderQueryDto.getAscOrDesc() == 1?" asc":" desc") + ",wo.seq asc ");
            }
        }

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

        packParameterForQueryWorkOrder(query, workOrderQueryDto);

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

    private void packHqlForQueryWorkOrder(StringBuffer hql, WorkOrderQueryDto model) {
        hql.append(" from MesWorkOrderExt as wo ");
        hql.append(" left join MesUnbindProduceSn as ups on ( ups.partNo = wo.partNo and ups.startTime like :curMonth and ups.organizeCode = :organizeCode and ups.isValid = :isValid and ups.isDeleted = :isDeleted and ups.bindStatus = 20 ) ");
        hql.append(" where wo.organizeCode = :organizeCode ");
        hql.append(" and wo.isValid = :isValid ");
        hql.append(" and wo.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and wo.workCenterCode like :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getOrderNo())) {
            hql.append(" and wo.orderNo like :orderNo ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and wo.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getCreateUser())) {
            hql.append(" and wo.createUser = :createUser ");
        }
        if (!StringUtils.isEmpty(model.getErpWarehouse())) {
            hql.append(" and wo.erpWarehouse = :erpWarehouse ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderStatus())) {
            hql.append(" and wo.workOrderStatus = :workOrderStatus ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            hql.append(" and wo.workOrderType <= :workOrderType ");
        }

        if (!StringUtils.isEmpty(model.getStartTimeStart()) && !StringUtils.isEmpty(model.getStartTimeEnd())) {
            hql.append(" and wo.startTime >= :startTimeStart ");
            hql.append(" and wo.startTime <= :startTimeEnd ");
        }

        if (!StringUtils.isEmpty(model.getEndTimeStart()) && !StringUtils.isEmpty(model.getEndTimeEnd())) {
            hql.append(" and wo.endTime >= :endTimeStart ");
            hql.append(" and wo.endTime <= :endTimeEnd ");
        }
    }

    private void packHqlForNativeQueryWorkOrder(StringBuffer hql, WorkOrderQueryDto model) {
        hql.append(" from mes_work_order wo inner join mes_work_order_ext as woe on ( wo.id = woe.pid ) ");
        hql.append(" left join mes_unbind_produce_sn as ups on ( ups.part_no = wo.part_no and ups.start_time like :curMonth and ups.organize_code = :organizeCode and ups.is_valid = :isValid and ups.is_deleted = :isDeleted and ups.bind_status = 20 ) ");
        hql.append(" where wo.organize_code = :organizeCode ");
        hql.append(" and wo.is_valid = :isValid ");
        hql.append(" and wo.is_deleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and wo.work_center_Code like :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getOrderNo())) {
            hql.append(" and wo.order_no like :orderNo ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and wo.part_no = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getCreateUser())) {
            hql.append(" and wo.create_user = :createUser ");
        }
        if (!StringUtils.isEmpty(model.getErpWarehouse())) {
            hql.append(" and woe.erp_warehouse = :erpWarehouse ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderStatus())) {
            hql.append(" and wo.wo_status = :workOrderStatus ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            hql.append(" and wo.wo_type <= :workOrderType ");
        }

        if (!StringUtils.isEmpty(model.getStartTimeStart()) && !StringUtils.isEmpty(model.getStartTimeEnd())) {
            hql.append(" and wo.start_time >= :startTimeStart ");
            hql.append(" and wo.start_time <= :startTimeEnd ");
        }

        if (!StringUtils.isEmpty(model.getEndTimeStart()) && !StringUtils.isEmpty(model.getEndTimeEnd())) {
            hql.append(" and wo.end_time >= :endTimeStart ");
            hql.append(" and wo.end_time <= :endTimeEnd ");
        }
    }

    private void packParameterForQueryWorkOrder(Query query, WorkOrderQueryDto 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("curMonth", TimeTool.getNowTime(MesExtConstWords.DATE_FORMAT_YM) + "%");

        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            query.setParameter(MesExtConstWords.WORK_CENTER_CODE, model.getWorkCenterCode());
        }
        if (!StringUtils.isEmpty(model.getOrderNo())) {
            query.setParameter(MesExtConstWords.ORDER_NO, "%" + model.getOrderNo() + "%");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            query.setParameter(MesExtConstWords.PART_NO, model.getPartNo());
        }

        if (!StringUtils.isEmpty(model.getCreateUser())) {
            query.setParameter(MesExtConstWords.CREATE_USER, model.getCreateUser());
        }
        if (!StringUtils.isEmpty(model.getErpWarehouse())) {
            query.setParameter(MesExtConstWords.ERP_WARE_HOUSE, model.getErpWarehouse());
        }
        if (!StringUtils.isEmpty(model.getWorkOrderStatus())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_STATUS, model.getWorkOrderStatus());
        }
        if (!StringUtils.isEmpty(model.getWorkOrderType())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_TYPE, model.getWorkOrderType());
        }

        if (!StringUtils.isEmpty(model.getStartTimeStart()) && !StringUtils.isEmpty(model.getStartTimeEnd())) {
            query.setParameter(MesExtConstWords.START_TIME_START, model.getStartTimeStart());
            query.setParameter(MesExtConstWords.START_TIME_END, model.getStartTimeEnd());
        }

        if (!StringUtils.isEmpty(model.getEndTimeStart()) && !StringUtils.isEmpty(model.getEndTimeEnd())) {
            query.setParameter(MesExtConstWords.END_TIME_START, model.getEndTimeStart());
            query.setParameter(MesExtConstWords.END_TIME_END, model.getEndTimeEnd());
        }
    }
}
