package com.sunda.spmsoversea.mapper;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaInvCheckSearchDTO;
import com.sunda.spmsoversea.dto.OverseaReceiptAndDeliverySearchDTO;
import com.sunda.spmsoversea.dto.OverseaRequisitionQueryDTO;
import com.sunda.spmsoversea.dto.OverseaRequisitionReportDTO;
import com.sunda.spmsoversea.entity.OverseaRequisition;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 领用申请表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-22
 */
@Mapper
public interface OverseaRequisitionMapper extends BaseMapper<OverseaRequisition> {

	@Select("<script>" +
			"SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
			"       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
			"       ORQ.SPMS_ID AS \"spmsId\",\n" +
			"       ORQ.CREATE_DATE AS \"createDate\",\n" +
			"       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
			"       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
			"       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
			"       ORQ.CREATE_USERID AS \"createUserid\",\n" +
			"       SU.LAST_NAME AS \"createUsername\",\n" +
			"       ORQ.WERKS AS \"werks\",\n" +
			"       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
			"       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
			"       ORQ.COST_CENTER AS \"costCenter\",\n" +
			"       ORQ.COST_CENTER_NAME AS \"costCenterName\",\n" +
			"       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
			"       ORQ.REMARKS AS \"remarks\",\n" +
			"       ORQ.EAM_APP_TYPE AS \"eamAppType\",\n" +
			"       ORQ.REQUESTNUM AS \"requestNum\",\n" +
			"       ORQ.COMMENTS AS \"comments\",\n" +
			"       ORQ.CREATE_TIME AS \"createTime\",\n" +
			"       ORQ.OA_NUMBER AS \"oaNumber\",\n" +
			"       ORQ.UPDATE_TIME AS \"updateTime\"\n" +
			"FROM OVERSEA_REQUISITION ORQ " +
			"   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
			"WHERE ORQ.SPMS_STATUS != '0' " +
			" <when test='dto.uuidRequisition!=null and dto.uuidRequisition != \"\"'> AND ORQ.UUID_REQUISITION = #{dto.uuidRequisition}</when>" +
			" <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND ORQ.SPMS_ID = #{dto.spmsId}</when>" +
			" <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND ORQ.SPMS_STATUS = #{dto.spmsStatus}</when>" +
			" <when test='dto.werks!=null and dto.werks != \"\"'> AND ORQ.WERKS = #{dto.werks}</when>" +
			" <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND ORQ.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
			" <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND ORQ.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
			" <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND ORQ.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
			" <when test='dto.endDate!=null and dto.endDate != \"\"'> AND ORQ.CREATE_DATE &lt;= #{dto.endDate}</when>" +
			" <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
			"   AND INSTR(ORQ.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
			" </when>" +
			" <when test='dto.werksList != null'> AND ORQ.WERKS in \n" +
			"    <foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
			"       #{item} \n" +
			"    </foreach>\n" +
			" </when>\n" +
			" ORDER BY ORQ.SPMS_STATUS ASC, ORQ.UPDATE_TIME DESC " +
			"</script>")
	List<Map<String, Object>> getOverseaRequisitionIPage(IPage page, @Param("dto") OverseaRequisitionQueryDTO dto);


	@Select("<script>" +
			"SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
			"       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
			"       ORQ.WERKS AS \"werks\",\n" +
			"       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
			"       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"       SU.LAST_NAME AS \"createUsername\",\n" +
			"       ORQ.CREATE_TIME AS \"createTime\",\n" +
			"       ORQ.UPDATE_TIME AS \"updateTime\",\n" +
			"       ORQ.SPMS_ID AS \"relateDocument\",\n" +
			"       ORQ.EAM_APP_TYPE AS \"eamAppType\",\n" +
			"       ORD.MATERIAL_NO AS \"materialNo\",\n" +
			"       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\", \n" +
			"       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\", \n" +
			"       ORD.APPLY_QTY_BASIC_UNIT AS \"applyQtyBasicUnit\",\n" +
			"       ORD.ACTUAL_QTY_BASIC_UNIT AS \"actualQtyBasicUnit\",\n" +
			"       ORD.BASIC_UNIT AS \"basicUnit\",\n" +
			"       OWO.CREATE_TIME AS \"outTime\",\n" +
			"       OWO.SPMS_ID AS \"spmsId\",\n" +
			"       OWO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
			"       OWO.MOVEMENT_TYPE AS \"movementType\",\n" +
			"       OWO.CANCEL_REASON AS \"cancelReason\",\n" +
			"       OWO.COST_CENTER AS \"costCenter\",\n" +
			"       CC.COST_CENTER_NAME AS \"costCenterName\",\n" +
			"       ORD.PROJECT_OA_NUMBER AS \"projectOaNumber\", \n" +
			"       ORD.COUNTRY AS \"country\", \n" +
			"       ORD.LICENSE_NO AS \"licenseNo\", \n" +
			"       ORQ.REMARKS AS \"comments\"\n" +
			"FROM OVERSEA_REQUISITION_DTL ORD " +
			"   LEFT JOIN OVERSEA_REQUISITION ORQ ON ORQ.UUID_REQUISITION = ORD.UUID_REQUISITION\n" +
			"   LEFT JOIN OVERSEA_WHS_OUT_DTL OWOD ON OWOD.RELATE_DOCUMENT = ORQ.SPMS_ID AND  OWOD.RELATE_DOCUMENT_ITEM = ORD.ITEM \n" +
			"   LEFT JOIN OVERSEA_WHS_OUT OWO ON OWOD.UUID_WHS_OUT = OWO.UUID_WHS_OUT \n" +
			"   LEFT JOIN COST_CENTER CC ON CC.COST_CENTER_CODE = OWO.COST_CENTER   \n" +
			"   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
			"   LEFT JOIN MATERIAL_SAP SAP ON ORD.MATERIAL_NO = SAP.MATERIAL_NO\n" +
			"WHERE ORQ.SPMS_STATUS != '0' " +
			"AND  OWO.SPMS_STATUS = '3' " +
			" <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND ORQ.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
			" <when test='dto.endDate!=null and dto.endDate != \"\"'> AND ORQ.CREATE_DATE &lt;= #{dto.endDate}</when>" +
			" <when test='dto.movementType!=null and dto.movementType != \"\"'> AND OWO.MOVEMENT_TYPE = #{dto.movementType}</when>" +
			" <when test='dto.costCenter!=null and dto.costCenter != \"\"'> AND OWO.COST_CENTER = #{dto.costCenter}</when>" +
			" <when test='dto.werks!=null and dto.werks != \"\"'> AND ORQ.WERKS = #{dto.werks}</when>" +
			" <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND ORQ.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
			" <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND ORQ.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
			" <when test='dto.relateDocument!=null and dto.relateDocument != \"\"'> AND ORQ.SPMS_ID = #{dto.relateDocument}</when>" +
			" <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND OWO.SPMS_ID = #{dto.spmsId}</when>" +
			" <when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND ORD.MATERIAL_NO = #{dto.materialNo}</when>" +
			" <when test='dto.sapVoucherNumber!=null and dto.sapVoucherNumber != \"\"'> AND OWO.SAP_VOUCHER_NUMBER = #{dto.sapVoucherNumber}</when>" +
			" ORDER BY  ORQ.CREATE_TIME DESC " +
			"</script>")
	List<Map<String, Object>> getOverseaRequisitionReport(IPage page, @Param("dto") OverseaRequisitionReportDTO doc);


	@Select("SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
			"       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
			"       ORQ.SPMS_ID AS \"spmsId\",\n" +
			"       ORQ.CREATE_DATE AS \"createDate\",\n" +
			"       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
			"       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
			"       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
			"       ORQ.CREATE_USERID AS \"createUserid\",\n" +
			"       ORQ.EAM_APP_TYPE AS \"eamAppType\",\n" +
			"       SU.LAST_NAME AS \"createUsername\",\n" +
			"       SU.DEPT_NAME AS \"deptName\",\n" +
			"       ORQ.WERKS AS \"werks\",\n" +
			"       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
			"       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
			"       ORQ.COST_CENTER AS \"costCenter\",\n" +
			"       CC.COST_CENTER_NAME AS \"costCenterName\",\n" +
			"       CC.COST_CENTER_EN_NAME AS \"costCenterEnName\",\n" +
			"       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
			"       ORQ.REMARKS AS \"remarks\",\n" +
			"       ORQ.COMMENTS AS \"comments\",\n" +
			"       ORQ.CREATE_TIME AS \"createTime\",\n" +
			"       ORQ.UPDATE_TIME AS \"updateTime\"\n" +
			"FROM OVERSEA_REQUISITION ORQ " +
			"   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
			"	LEFT JOIN COST_CENTER CC ON CC.COST_CENTER_CODE = ORQ.COST_CENTER\n" +
			"WHERE ORQ.SPMS_STATUS != '0' " +
			"   AND ORQ.UUID_REQUISITION = #{uuidRequisition}")
	Map<String, Object> getOverseaRequisition(@Param("uuidRequisition") String uuidRequisition);

	@Select("SELECT ORQ.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
			"       ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
			"       ORQ.SPMS_ID AS \"spmsId\",\n" +
			"       ORQ.CREATE_DATE AS \"createDate\",\n" +
			"       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
			"       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
			"       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
			"       ORQ.CREATE_USERID AS \"createUserid\",\n" +
			"       ORQ.EAM_APP_TYPE AS \"eamAppType\",\n" +
			"       SU.LAST_NAME AS \"createUsername\",\n" +
			"       SU.DEPT_NAME AS \"deptName\",\n" +
			"       ORQ.WERKS AS \"werks\",\n" +
			"       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
			"       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
			"       ORQ.COST_CENTER AS \"costCenter\",\n" +
			"       CC.COST_CENTER_NAME AS \"costCenterName\",\n" +
			"       CC.COST_CENTER_EN_NAME AS \"costCenterEnName\",\n" +
			"       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
			"       ORQ.REMARKS AS \"remarks\",\n" +
			"       ORQ.COMMENTS AS \"comments\",\n" +
			"       ORQ.CREATE_TIME AS \"createTime\",\n" +
			"       ORQ.UPDATE_TIME AS \"updateTime\"\n" +
			"FROM OVERSEA_REQUISITION ORQ " +
			"   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
			"	LEFT JOIN COST_CENTER CC ON CC.COST_CENTER_CODE = ORQ.COST_CENTER\n" +
			"   Where ORQ.UUID_REQUISITION = #{uuidRequisition}")
	Map<String, Object> getAllOverseaRequisition(@Param("uuidRequisition") String uuidRequisition);

	@Select("<script>" +
			" SELECT ROWNUM AS \"rowId\",\n" +
			"       RE.UUID_REQUISITION AS \"uuidRequisition\",\n" +
			"       R.AUTO_ID_REQUISITION AS \"autoIdRequisition\",\n" +
			"       R.SPMS_ID AS \"spmsId\",\n" +
			"       R.CREATE_DATE AS \"createDate\",\n" +
			"       R.BUSINESS_TYPE AS \"businessType\",\n" +
			"       R.SPMS_STATUS AS \"spmsStatus\",\n" +
			"       R.CREATE_USERID AS \"createUserid\",\n" +
			"       SU.LAST_NAME AS \"createUsername\",\n" +
			"       R.WERKS AS \"werks\",\n" +
			"       R.WORKSHOP_CODE AS \"workshopCode\",\n" +
			"       R.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
			"       R.COST_CENTER AS \"costCenter\",\n" +
			"       R.DATA_VERSION AS \"dataVersion\",\n" +
			"       R.REMARKS AS \"remarks\",\n" +
			"       R.COMMENTS AS \"comments\",\n" +
			"       R.APPROVAL_DATE AS \"approvalDate\"\n" +
			"FROM (\n" +
			"    SELECT UUID_REQUISITION, APPROVAL_DATE\n" +
			"    FROM OVERSEA_REQUISITION ORQ\n" +
			"    WHERE ORQ.SPMS_STATUS = '3'\n" +
			"    <when test='werks !=null and werks != \"\"'>AND WERKS = #{werks} </when>\n" +
			"      AND\n" +
			"          TO_NUMBER(TO_DATE(#{currentDate}, 'yyyy-MM-dd') -\n" +
			"                    TO_DATE(ORQ.APPROVAL_DATE, 'yyyy-MM-dd')) >= 3\n" +
			"    UNION\n" +
			"    SELECT UUID_REQUISITION, APPROVAL_DATE\n" +
			"    FROM OVERSEA_REQUISITION ORQ\n" +
			"    WHERE ORQ.SPMS_STATUS = '5'\n" +
			"      AND\n" +
			"          TO_NUMBER(TO_DATE(#{currentDate}, 'yyyy-MM-dd') -\n" +
			"                    TO_DATE(ORQ.APPROVAL_DATE, 'yyyy-MM-dd')) >= 3\n" +
			"      AND ORQ.SPMS_ID IN (\n" +
			"          SELECT OWO.RELATE_DOCUMENT FROM OVERSEA_WHS_OUT OWO\n" +
			"          WHERE OWO.SPMS_STATUS = '1'\n" +
			"            AND OWO.WERKS = #{werks}\n" +
			"          ) ORDER BY APPROVAL_DATE ASC\n" +
			"    ) RE\n" +
			"    LEFT JOIN OVERSEA_REQUISITION R ON RE.UUID_REQUISITION = R.UUID_REQUISITION\n" +
			"    LEFT JOIN SPMS_USER SU ON R.CREATE_USERID = SU.WORK_CODE " +
			"</script>")
	/**
	 领用申请单状态为 3(审批通过) 审批通过日期已超过 3 天；
	 领用申请单状态为 5(已生成出库任务) 审批通过日期已超过 3 天；无有效出库任务 或 有状态为 1 进行中的出库任务； */
	List<Map<String, Object>> getRequisitionOverdue3(@Param("werks") String werks, @Param("currentDate") String currentDate);

	@Select("<script>" +
			"SELECT FIELD_KEY AS \"fieldKey\",\n " +
			"	FIELD_NAME AS \"fieldName\",\n" +
			"	KEY_CODE AS \"keyCode\",\n" +
			"	KEY_CODE_DESC AS \"keyCodeDesc\",\n" +
			"	KEY_CODE_EN_DESC AS \"keyCodeEnDesc\" \n" +
			"FROM ORDER_KEY_VALUE_CONFIG WHERE ENABLED = '1' AND FIELD_KEY = #{fieldKey}" +
			"</script>")
	List<Map<String, Object>> selectOrderKeyValueConfigList(@Param("fieldKey") String fieldKey);

	@Select("<script>" +
			"SELECT \n " +
			"	OWO.SPMS_ID AS \"spmsId\",\n" +
			"	DTL.RELATE_DOCUMENT AS \"relateDocument\",\n" +
			"	DTL.RELATE_DOCUMENT_ITEM AS \"relateDocumentItem\" \n" +
			"FROM OVERSEA_WHS_OUT_DTL DTL \n" +
			"	LEFT JOIN OVERSEA_WHS_OUT OWO ON DTL.UUID_WHS_OUT = OWO.UUID_WHS_OUT \n" +
			"WHERE OWO.SPMS_STATUS != '0' \n" +
			"	AND DTL.RELATE_DOCUMENT = #{spmsId}" +
			"</script>")
	List<Map<String, Object>> selectOversaeWhsOutDtl(String spmsId);

	@Select("select uuid from WHS_STORAGE where WERKS = #{werks} and STORAGE_NO =#{storageNo} and WHS_LOCATION_CODE = #{whsLocationCode} ")
	List<Map<String, String>> selectWhsStorageUUid(@Param("werks") String werks, @Param("storageNo") String storageNo, @Param("whsLocationCode") String whsLocationCode);



	@Select("<script>" +
			"SELECT\n" +
			"  ZD.SHWERKS AS \"receiveWerks\",\n" +
			"  ZD.FHWERKS AS \"werks\",\n" +
			"  ZD.BEDAT AS \"poDate\",\n" +
			"  ZD.EBELN AS \"purchaseNo\",\n" +
			"  ZD.EBELP AS \"poItemNo\",\n" +
			"  ZD.DGDAT AS \"arrivalDate\",\n" +
			"  ZD.BOLNR AS \"boxNote\",\n" +
			"  ZD.CABNO AS \"cabNo\",\n" +
			"  ZD.ZSPMS AS \"sysCode\",\n" +
			"  ZD.VBELN AS \"sapDeliveryNote\",\n" +
			"  ZD.NUMC AS \"sapDevItem\",\n" +
			"  ZD.PACKAGE_CODE AS \"boxNote\",\n" +
			"  ZD.MATNR AS \"materialNo\",\n" +
			"  ZD.MAKTX AS \"materialName\",\n" +
			"  ZD.WAMNG AS \"shippedQty\",\n" +
			"  ZD.WEMNG AS \"receivedQty\",\n" +
			"  ZD.MEINS AS \"baseUnit\",\n" +
			"  ZD.ZWAMNG_XS AS \"shippedNumber\",\n" +
			"  ZD.ZWEMNG_XS AS \"receivedNumber\",\n" +
			"  ZD.ZVRKME_XS AS \"saleUnit\",\n" +
			"  ZD.BEDAT AS \"bedat\"\n" +
			"FROM Z_SPMS_ZAITU ZD \n" +
			"WHERE 1 = 1 \n" +
			"<when test='dto.werks!=null and dto.werks != \"\"'> AND ZD.FHWERKS = #{dto.werks}</when>\n" +
			"<when test='dto.receiveWerks!=null and dto.receiveWerks != \"\"'> AND ZD.SHWERKS = #{dto.receiveWerks}</when>\n" +
			"<when test='dto.boxNote!=null and dto.boxNote != \"\"'> AND ZD.BOLNR = #{dto.boxNote}</when>\n" +
			"<when test='dto.materialNo!=null and dto.materialNo != \"\"'> AND ZD.MATNR = #{dto.materialNo}</when>\n" +
			"<when test='dto.purchaseNo!=null and dto.purchaseNo != \"\"'> AND ZD.EBELN = #{dto.purchaseNo}</when>\n" +
			"<when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND ZD.VBELN = #{dto.sapDeliveryNote}</when>\n" +
			"<when test='dto.beginDate!=null'> AND TO_DATE(ZD.BEDAT, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-MM-dd') </when> \n" +
			"<when test='dto.endDate!=null'> AND TO_DATE(ZD.BEDAT, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-MM-dd') </when> \n" +
			"ORDER BY ZD.CREATE_TIME ASC" +
			"</script>")
	List<Map<String, Object>> getReceiptAndDeliveryReports(@Param("dto") OverseaReceiptAndDeliverySearchDTO dto);
}