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

import cn.estsh.i3plus.ext.mes.apiservice.dao.IPackageExtDao;
import cn.estsh.i3plus.ext.mes.pojo.model.SxPackSnReportModel;
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 PackageExtDao implements IPackageExtDao {

    @Autowired
    private EntityManager entityManager;

    @Override
    public int queryStandardWorkOrderPackSnPagerCount(SxPackSnReportModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select count(pe.id) ");

        packHqlForQueryStandardWorkOrderPackSn(hql, model);

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

        packParameterForQueryStandardWorkOrderPackSn(query, model);

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

    @Override
    public List<SxPackSnReportModel> queryStandardWorkOrderPackSnPager(SxPackSnReportModel model, Pager pager) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + SxPackSnReportModel.class.getName() + "(pe.id, pe.packageNo, pe.partNo, pe.partNameRdd, " +
                " pe.qty, pe.packSpecQty, pe.unit, pe.lotNo, pe.isSealed, pe.printStatus, pe.workOrderNo, pe.workCenterCode, " +
                " pe.workCellCode, pe.ctNo, pe.organizeCode, pe.createUser, pe.createDatetime, pe.modifyUser, pe.modifyDatetime, pd.serialNumber ) ");

        packHqlForQueryStandardWorkOrderPackSn(hql, model);

        hql.append(" order by pe.packageNo asc ");
        Query query = entityManager.createQuery(hql.toString(), SxPackSnReportModel.class);

        packParameterForQueryStandardWorkOrderPackSn(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 packHqlForQueryStandardWorkOrderPackSn(StringBuffer hql, SxPackSnReportModel model) {
        hql.append(" from MesPackageExt as pe ");
        hql.append(" left join MesPackageDetail as pd on (pe.packageNo = pd.packageNo and pd.organizeCode = :organizeCode and pd.isValid = :isValid and pd.isDeleted = :isDeleted) ");
        if (!StringUtils.isEmpty(model.getPartCategory()) && !StringUtils.isEmpty(MesExtEnumUtil.REPORT_PART_CATEGORY.valueOfDescription(model.getPartCategory()))) {
            hql.append(" left join MesPart as part on (pe.partNo = part.partNo and part.organizeCode = :organizeCode and part.isValid = :isValid and part.isDeleted = :isDeleted) ");
        }
        hql.append(" where pe.organizeCode = :organizeCode ");
        hql.append(" and pe.isValid = :isValid ");
        hql.append(" and pe.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getPackageNo())) {
            hql.append(" and pe.packageNo like :packageNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            hql.append(" and pe.workOrderNo like :workOrderNo ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and pe.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and pe.workCenterCode = :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getCtNo())) {
            hql.append(" and pe.ctNo = :ctNo ");
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeStart())) {
            hql.append(" and pe.createDatetime >= :createDatetimeStart ");
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeEnd())) {
            hql.append(" and pe.createDatetime <= :createDatetimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getModifyDatetimeStart())) {
            hql.append(" and pe.modifyDatetime >= :modifyDatetimeStart ");
        }
        if (!StringUtils.isEmpty(model.getModifyDatetimeEnd())) {
            hql.append(" and pe.modifyDatetime <= :modifyDatetimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeStart())) {
            hql.append(" and pe.lotNo >= :workTimeStart ");
        }
        if (!StringUtils.isEmpty(model.getWorkTimeEnd())) {
            hql.append(" and pe.lotNo <= :workTimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getSerialNumber())) {
            hql.append(" and pd.serialNumber like :serialNumber ");
        }
        if (!StringUtils.isEmpty(model.getPartCategory()) && !StringUtils.isEmpty(MesExtEnumUtil.REPORT_PART_CATEGORY.valueOfDescription(model.getPartCategory()))) {
            if (MesExtEnumUtil.REPORT_PART_CATEGORY.PART.getValue().equals(model.getPartCategory())) {
                hql.append(" and ( part.categoryCode2 = '' or part.categoryCode2 is null ) ");
            } else {
                hql.append(" and part.categoryCode2 = :categoryCode2 ");
            }
        }
    }

    private void packParameterForQueryStandardWorkOrderPackSn(Query query, SxPackSnReportModel 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());
        if (!StringUtils.isEmpty(model.getPackageNo())) {
            query.setParameter(MesExtConstWords.PACKAGENO, "%" + model.getPackageNo() + "%");
        }
        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.getCtNo())) {
            query.setParameter(MesExtConstWords.CT_NO, model.getCtNo());
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeStart())) {
            query.setParameter(MesExtConstWords.CREATE_DATE_TIME_START, model.getCreateDatetimeStart());
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeEnd())) {
            query.setParameter(MesExtConstWords.CREATE_DATE_TIME_END, model.getCreateDatetimeEnd());
        }
        if (!StringUtils.isEmpty(model.getModifyDatetimeStart())) {
            query.setParameter(MesExtConstWords.MODIFY_DATE_TIME_START, model.getModifyDatetimeStart());
        }
        if (!StringUtils.isEmpty(model.getModifyDatetimeEnd())) {
            query.setParameter(MesExtConstWords.MODIFY_DATE_TIME_END, model.getModifyDatetimeEnd());
        }
        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.getSerialNumber())) {
            query.setParameter(MesExtConstWords.SERIAL_NUMBER, "%" + model.getSerialNumber() + "%");
        }
        if (!StringUtils.isEmpty(model.getPartCategory()) && !StringUtils.isEmpty(MesExtEnumUtil.REPORT_PART_CATEGORY.valueOfDescription(model.getPartCategory()))) {
            if (!MesExtEnumUtil.REPORT_PART_CATEGORY.PART.getValue().equals(model.getPartCategory())) {
                query.setParameter(MesExtConstWords.CATEGORY_CODE2, model.getPartCategory());
            }
        }
    }

    @Override
    public int queryRepairWorkOrderPackSnPagerCount(SxPackSnReportModel model) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select count(psr.id) ");

        packHqlForQueryRepairWorkOrderPackSn(hql, model);

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

        packParameterForQueryRepairWorkOrderPackSn(query, model);

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

    @Override
    public List<SxPackSnReportModel> queryRepairWorkOrderPackSnPager(SxPackSnReportModel model, Pager pager) {
        StringBuffer hql = new StringBuffer();
        hql.append(" select new " + SxPackSnReportModel.class.getName() + "(psr.serialNumber, psr.workOrderNo, psr.ctNo, psr.lotNo, " +
                " pe.id, pe.packageNo, pe.partNo, pe.partNameRdd, pe.qty, pe.packSpecQty, pe.unit, pe.isSealed, pe.printStatus, pe.workOrderNo as initWorkOrderNo, " +
                " psr.workCenterCode, psr.workCellCode, pe.organizeCode, pe.createUser, pe.createDatetime, pe.modifyUser, pe.modifyDatetime) ");

        packHqlForQueryRepairWorkOrderPackSn(hql, model);

        hql.append(" order by pe.packageNo asc ");
        Query query = entityManager.createQuery(hql.toString(), SxPackSnReportModel.class);

        packParameterForQueryRepairWorkOrderPackSn(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 packHqlForQueryRepairWorkOrderPackSn(StringBuffer hql, SxPackSnReportModel model) {
        hql.append(" from MesProduceSnRepair as psr ");
        hql.append(" right join MesPackageDetail as pd on (psr.serialNumber = pd.serialNumber and pd.organizeCode = :organizeCode and pd.isValid = :isValid and pd.isDeleted = :isDeleted) ");
        hql.append(" right join MesPackageExt as pe on (pd.packageNo = pe.packageNo and pe.organizeCode = :organizeCode and pe.isValid = :isValid and pe.isDeleted = :isDeleted) ");
        hql.append(" where psr.organizeCode = :organizeCode ");
        hql.append(" and psr.isValid = :isValid ");
        hql.append(" and psr.isDeleted = :isDeleted ");
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            hql.append(" and psr.workOrderNo like :workOrderNo ");
        }
        if (!StringUtils.isEmpty(model.getSerialNumber())) {
            hql.append(" and psr.serialNumber like :serialNumber ");
        }
        if (!StringUtils.isEmpty(model.getPartNo())) {
            hql.append(" and psr.partNo = :partNo ");
        }
        if (!StringUtils.isEmpty(model.getWorkCenterCode())) {
            hql.append(" and psr.workCenterCode = :workCenterCode ");
        }
        if (!StringUtils.isEmpty(model.getCtNo())) {
            hql.append(" and psr.ctNo = :ctNo ");
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeStart())) {
            hql.append(" and pe.createDatetime >= :createDatetimeStart ");
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeEnd())) {
            hql.append(" and pe.createDatetime <= :createDatetimeEnd ");
        }
        if (!StringUtils.isEmpty(model.getPackageNo())) {
            hql.append(" and pd.packageNo like :packageNo ");
        }
    }

    private void packParameterForQueryRepairWorkOrderPackSn(Query query, SxPackSnReportModel 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());
        if (!StringUtils.isEmpty(model.getWorkOrderNo())) {
            query.setParameter(MesExtConstWords.WORK_ORDER_NO, "%" + model.getWorkOrderNo() + "%");
        }
        if (!StringUtils.isEmpty(model.getSerialNumber())) {
            query.setParameter(MesExtConstWords.SERIAL_NUMBER, "%" + model.getSerialNumber() + "%");
        }
        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.getCtNo())) {
            query.setParameter(MesExtConstWords.CT_NO, model.getCtNo());
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeStart())) {
            query.setParameter(MesExtConstWords.CREATE_DATE_TIME_START, model.getCreateDatetimeStart());
        }
        if (!StringUtils.isEmpty(model.getCreateDatetimeEnd())) {
            query.setParameter(MesExtConstWords.CREATE_DATE_TIME_END, model.getCreateDatetimeEnd());
        }
        if (!StringUtils.isEmpty(model.getPackageNo())) {
            query.setParameter(MesExtConstWords.PACKAGENO, "%" + model.getPackageNo() + "%");
        }
    }
}
