package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsorder.entity.OrderWerks;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

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

/**
 * <p>
 * 工厂请购单 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-02-05
 */
@Mapper
@Component
public interface OrderWerksMapper extends BaseMapper<OrderWerks> {

    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "  OW.UUID AS \"orderWerksUuid\",\n" +
            "  OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "  OWD2.UUID AS \"orderWorkshopUuid\",\n" +
            "  OWD2.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "  OWD2.ROW_ID AS \"rowIdWorkshop\",\n" +
            "  OWD2.MATERIAL_NO AS \"materialNo\",\n" +
            "  OWD2.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  OWD2.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  SAP.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  OWD2.REQUEST_QTY AS \"requestQty\",\n" +
            "  OWD2.APPROVAL_QTY AS \"approvalQty\",\n" +
            "  OWD2.BASIC_UNIT AS \"basicUnit\",\n" +
            "  OWD2.BRAND AS \"brand\",\n" +
            "  OWD2.ROW_STATUS AS \"rowStatus\",\n" +
            "  OWD2.ROW_COMMENT AS \"rowComment\",\n" +
            "  OWD2.DEVICE_ID AS \"deviceId\",\n" +
            "  OWD2.DEVICE_CATEGORY AS \"deviceCategory\",\n" +
            "  OWD2.DEVICE_TYPE AS \"deviceType\",\n" +
            "  OWD2.MATERIAL_CATEGORY AS \"materialCategory\",\n" +
            "  OWD2.ROW_COMMENT AS \"rowComment\",\n" +
            "  OWS.WORK_CODE AS \"orderWorkshopUserId\",\n" +
            "  OWS.LAST_NAME AS \"orderWorkshopUsername\",\n" +
            "  OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "  OWS.REQUEST_TYPE AS \"requestType\",\n" +
            "  W.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "  W.WORKSHOP_NAME_ZH AS \"workshopNameZh\",\n" +
            "  mbew.VERPR AS \"materialPrice\",\n" +
            "  W.WORKSHOP_NAME_EN AS \"workshopNameEn\"\n" +
            "FROM ORDER_WERKS OW\n" +
            "  LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "  LEFT JOIN ORDER_WORKSHOP_DTL OWD2 ON OWD.SPMS_WS_ID = OWD2.SPMS_WS_ID AND OWD.ROW_ID_WS = OWD2.ROW_ID\n" +
            "  LEFT JOIN ORDER_WORKSHOP OWS ON OWS.UUID = OWD2.UUID\n" +
            "  LEFT JOIN WORKSHOP W ON OWS.WERKS = W.WERKS AND OWS.WORKSHOP_CODE = W.WORKSHOP_CODE" +
            "  LEFT JOIN MATERIAL_SAP SAP ON OWD2.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "  LEFT JOIN Z_SPMS_GET_MBEW mbew ON OWD2.MATERIAL_NO = mbew.MATNR   and mbew.BWKEY='CN01' \n" +
            "WHERE OW.SPMS_ID = #{spmsId} ORDER BY OWD.ROW_ID_WERKS ASC")
    List<Map<String, Object>> getOrderWerksDtl(String spmsId);

    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "      OW.UUID AS \"orderWerksUuid\",\n" +
            "      OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "      OWD2.UUID AS \"orderWorkshopUuid\",\n" +
            "      OWD2.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "      OWD2.ROW_ID AS \"rowIdWorkshop\",\n" +
            "      OWD2.MATERIAL_NO AS \"materialNo\",\n" +
            "      SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "      SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "      SAP.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "		ZSGKL.LABST366 AS \"labst366\",\n" +
            "		(CASE WHEN owod.ACTUAL_QTY_BASIC_UNIT IS NULL THEN 0 ELSE owod.ACTUAL_QTY_BASIC_UNIT END) AS \"actualQtyBasicUnit\",\n " +
            "      OWD2.REQUEST_QTY AS \"requestQty\",\n" +
            "      OWD2.APPROVAL_QTY AS \"approvalQty\",\n" +
            "      OWD2.BASIC_UNIT AS \"basicUnit\",\n" +
            "      OWD2.BRAND AS \"brand\",\n" +
            "      OWD2.ROW_STATUS AS \"rowStatus\",\n" +
            "      OWD2.ROW_COMMENT AS \"rowComment\",\n" +
            "      OWD2.MATERIAL_CATEGORY AS \"materialCategory\",\n" +
            "      OWD2.ROW_COMMENT AS \"rowComment\",\n" +
            "      OWD2.SUPPLEMENTORNOT AS \"supplementOrNot\",\n" +
            "      OWS.WORK_CODE AS \"orderWorkshopUserId\",\n" +
            "      OWS.LAST_NAME AS \"orderWorkshopUsername\",\n" +
            "      OWS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "      OWS.REQUEST_TYPE AS \"requestType\",\n" +
            "      OWS.ATTACHMENT_NAME AS \"attachmentName\",\n" +
            "      OWS.ATTACHMENT_URL AS \"attachmentUrl\",\n" +
            "      OWS.PROJECT_OA_NUMBER AS \"projectOaNumber\",\n" +
            "      OWS.COUNTRY AS \"country\",\n" +
            "      OWD2.PRICE AS \"price\",\n" +
            "		(CASE WHEN OWS.REQUEST_TYPE = '0' THEN mbew.VERPR WHEN OWS.REQUEST_TYPE = '2' THEN mbew2.VERPR ELSE NULL END) AS \"materialPrice\",\n" +
            "		(CASE WHEN OWS.REQUEST_TYPE = '0' THEN mbew.WAERS WHEN OWS.REQUEST_TYPE = '2' THEN mbew2.WAERS ELSE NULL END) AS \"waers\",\n" +
            "      MP.CURRENCY AS \"currency\",\n" +
            "      mcl.RPTSTART_DATE AS \"prtStartDate\",\n" +
            "      mcl.RPTEND_DATE AS \"prtEndDate\",\n" +
            "      W.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "      W.WORKSHOP_NAME_ZH AS \"workshopNameZh\",\n" +
            "      W.WORKSHOP_NAME_EN AS \"workshopNameEn\",\n" +
            "      SAP.ZMOQ AS \"zmoq\",\n" +
            "      (CASE WHEN SI.SAFETY_QTY_BASIC_UNIT IS NULL THEN 0 ELSE SI.SAFETY_QTY_BASIC_UNIT END) AS \"safetyQtyBasicUnit\",\n" +
            "      (CASE WHEN RE.QUANTITY IS NULL THEN 0 ELSE RE.QUANTITY END) AS \"inventoryQuantity\",\n" +
            "      (CASE WHEN WAY.WAY_QUANTITY IS NULL THEN 0 ELSE WAY.WAY_QUANTITY END) AS \"wayQuantity\"\n" +
            "FROM ORDER_WERKS OW\n" +
            "    LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL OWD2 ON OWD.SPMS_WS_ID = OWD2.SPMS_WS_ID AND OWD.ROW_ID_WS = OWD2.ROW_ID\n" +
            "    LEFT JOIN ORDER_WORKSHOP OWS ON OWS.UUID = OWD2.UUID\n" +
            "    LEFT JOIN WORKSHOP W ON OWS.WERKS = W.WERKS AND OWS.WORKSHOP_CODE = W.WORKSHOP_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON OWD2.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_PRICE MP ON OWD2.MATERIAL_NO = MP.MATERIAL_NO\n" +
        		// 提报类型为备品备件的默认获取CN01工厂下的价格
            "    LEFT JOIN Z_SPMS_GET_MBEW mbew ON OWD2.MATERIAL_NO = mbew.MATNR and mbew.BWKEY='CN01' \n" +
            	// 提报类型为本地采购的获取需求工厂下的价格
            "    LEFT JOIN Z_SPMS_GET_MBEW mbew2 ON OWD2.MATERIAL_NO = mbew2.MATNR and mbew2.BWKEY = OW.WERKS \n" +
            "    LEFT JOIN (select MATERIAL_NO,max(CATEGORY_ID) as CATEGORY_ID from MATERIAL_SPMS GROUP BY MATERIAL_NO) spms ON OWD2.MATERIAL_NO = spms.MATERIAL_NO\n" +
            "    LEFT JOIN MATERIAL_CATEGORY_LABOR mcl ON substr(spms.CATEGORY_ID,0,2) = mcl.CATEGORY_ID \n" +
            "    LEFT JOIN SAFETY_INVENTORY SI ON OW.WERKS = SI.WERKS AND OWD2.MATERIAL_NO = SI.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT WS.WERKS, WSI.MATERIAL_NO, SUM(QUANTITY) AS \"QUANTITY\"\n" +
            "        FROM WHS_STORAGE_INVENTORY WSI\n" +
            "            LEFT JOIN WHS_STORAGE WS ON WSI.UUID = WS.UUID\n" +
            "        GROUP BY WS.WERKS, WSI.MATERIAL_NO\n" +
            "        ) RE ON RE.WERKS = OW.WERKS AND RE.MATERIAL_NO = OWD2.MATERIAL_NO\n" +
            "    LEFT JOIN (\n" +
            "        SELECT SHWERKS, MATNR, SUM(WAMNG) AS \"WAY_QUANTITY\"\n" +
            "        FROM Z_SPMS_ZAITU \n" +
            "        GROUP BY SHWERKS, MATNR\n" +
            "    ) WAY ON OW.WERKS = WAY.SHWERKS AND OWD2.MATERIAL_NO = WAY.MATNR\n" +

            "		LEFT JOIN ( \n" +
            "			SELECT SUM(oi.ACTUAL_QTY_BASIC_UNIT) AS \"ACTUAL_QTY_BASIC_UNIT\", oh.WERKS, oi.MATERIAL_NO \n" +
            "			FROM OVERSEA_WHS_OUT_DTL oi \n" +
            "			LEFT JOIN OVERSEA_WHS_OUT oh ON oh.UUID_WHS_OUT = oi.UUID_WHS_OUT \n" +
            "			GROUP BY oh.WERKS, oi.MATERIAL_NO	\n" +
            "		) owod ON owod.WERKS = OW.WERKS and owod.MATERIAL_NO = OWD2.MATERIAL_NO \n" +

            "		LEFT JOIN Z_SPMS_GET_KULING ZSGKL ON ZSGKL.MATNR = OWD2.MATERIAL_NO AND ZSGKL.WERKS = OW.WERKS \n" +
            "WHERE OW.SPMS_ID = #{spmsId} ORDER BY OWD.ROW_ID_WERKS ASC ")
    List<Map<String, Object>> getOrderWerksDtlWithInventory(@Param("spmsId") String spmsId);

    @Insert("INSERT INTO ORDER_WERKS(\n" +
            "  URGENCY,\n" +
            "  APPLICATION_DATE,\n" +
            "  DATE_REQUIRED,\n" +
            "  SUBJECT,\n" +
            "  STATUS,\n" +
            "  WORK_CODE,\n" +
            "  LAST_NAME,\n" +
            "  WERKS,\n" +
            "  WORKSHOP_CODE,\n" +
            "  DEPT_ID,\n" +
            "  POSITION_ID,\n" +
            "  COMPANY,\n" +
            "  BRANCH_POINT,\n" +
            "  BRANCH_LINE,\n" +
            "  PURCHASE_LOCATION,\n" +
            "  JUSTIFICATION,\n" +
            "  PROJECT_CODE,\n" +
            "  ESTIMATE_COST,\n" +
            "  PROJECT_TYPE,\n" +
            "  PROJECT_STATUS,\n" +
            "  PROJECT_CATEGORY,\n" +
            "  SHIPPING_TYPE,\n" +
            "  SHIPPING_FANGSHI,\n" +
            "  APPLICATION_TYPE,\n" +
            "  DEVICE_CATEGORY,\n" +
            "  DEVICE_TYPE,\n" +
            "  MATERIAL_CATEGORY,\n" +
            "  APPROVAL_COMMENTS,\n" +
            "  OA_ID,\n" +
            "  OA_PROCESS_ID,\n" +
            "  DETAIL_EXPLAIN,\n" +
            "  RELEVANT_PROCESS,\n" +
            "  RELEVANT_DOCUMENT,\n" +
            "  PURCHASE_MANAGER,\n" +
            "  COST_DEPT_ID,\n" +
            "  COST_DEPT_NAME,\n" +
            "  DEVICE_STATEMENT,\n" +
            "  DEVICE_ATTACHMENT,\n" +
            "  COMMENTS,\n" +
            "  RELEVANT_DEPARTMENT,\n" +
            "  ATTACHMENT_LIST,\n" +
            "  FIELD_FOR_OA,\n" +
            "  REQUEST_TYPE,\n" +
            "  MODULE_TYPE,\n" +
            "  IT_ITEM_OR_NOT,\n" +
            "  ITEM_TYPE,\n" +
            "  ESTIMATED_TOTAL_COST,\n" +
            "  ATTACHMENT_NAME,\n" +
            "  ATTACHMENT_URL,\n" +
            "  COUNTRY,\n" +
            "  COUNTRY_NAME,\n" +
            "  PR_SUBJECT,\n" +
            "  UUID) VALUES (\n" +
            "   #{ow.urgency}, \n" +
            "   #{ow.applicationDate}, \n" +
            "   #{ow.dateRequired}, \n" +
            "   #{ow.subject}, \n" +
            "   #{ow.status}, \n" +
            "   #{ow.userId}, \n" +
            "   #{ow.lastName}, \n" +
            "   #{ow.werks}, \n" +
            "   #{ow.workshopCode}, \n" +
            "   #{ow.deptId}, \n" +
            "   #{ow.positionId}, \n" +
            "   #{ow.company}, \n" +
            "   #{ow.branchPoint}, \n" +
            "   #{ow.branchLine}, \n" +
            "   #{ow.purchaseLocation}, \n" +
            "   #{ow.justification}, \n" +
            "   #{ow.projectCode}, \n" +
            "   #{ow.estimateCost}, \n" +
            "   #{ow.projectType}, \n" +
            "   #{ow.projectStatus}, \n" +
            "   #{ow.projectCategory}, \n" +
            "   #{ow.shippingType}, \n" +
            "   #{ow.shippingFangshi}, \n" +
            "   #{ow.applicationType}, \n" +
            "   #{ow.deviceCategory}, \n" +
            "   #{ow.deviceType}, \n" +
            "   #{ow.materialCategory}, \n" +
            "   #{ow.approvalComments}, \n" +
            "   #{ow.oaId}, \n" +
            "   #{ow.oaProcessId}, \n" +
            "   #{ow.detailExplain}, \n" +
            "   #{ow.relevantProcess}, \n" +
            "   #{ow.relevantDocument, jdbcType=VARCHAR}, \n" +
            "   #{ow.purchaseManager}, \n" +
            "   #{ow.costDeptId}, \n" +
            "   #{ow.costDeptName}, \n" +
            "   #{ow.deviceStatement}, \n" +
            "   #{ow.deviceAttachment}, \n" +
            "   #{ow.comments}, \n" +
            "   #{ow.relevantDepartment, jdbcType=VARCHAR}, \n" +
            "   #{ow.attachmentList}, \n" +
            "   #{ow.fieldForOa}, \n" +
            "   #{ow.requestType}, \n" +
            "   #{ow.moduleType}, \n" +
            "   #{ow.itItemOrNot}, \n" +
            "   #{ow.itemType}, \n" +
            "   #{ow.estimatedTotalCost}, \n" +
            "   #{ow.attachmentName}, \n" +
            "   #{ow.attachmentUrl}, \n" +
            "   #{ow.country}, \n" +
            "   #{ow.countryName}, \n" +
            "   #{ow.prSubject}, \n" +
            "   #{ow.uuid} \n" +
            ")")
    int createOrderWerks(@Param("ow") OrderWerks ow);

    @Select("SELECT SPMS_ID FROM ORDER_WERKS WHERE UUID = #{uuid}")
    String getSpmsIdByUuid(@Param("uuid") String uuid);

    /** 根据工厂请购单uuid查询请购单提交SRM表头信息 - ed64f63d663141028cad97a42510da89 */
    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "       OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "       OW.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       OW.URGENCY AS \"urgency\",\n" +
            "       OW.REQUEST_TYPE AS \"requestType\",\n" +
            "       OW.JUSTIFICATION AS \"justification\",\n" +
            "       OW.WERKS AS \"purchaseWerks\"\n" +
            "       FROM ORDER_WERKS OW\n" +
            "WHERE OW.UUID = #{uuid}")
    Map<String, Object> orderWerksInfo(@Param("uuid") String uuid);

    /** 根据工厂请购单uuid查询请购单提交SRM行项目信息 - ed64f63d663141028cad97a42510da89 */
    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "       WS.WERKS AS \"werks\",\n" +
            "       OW.WERKS AS \"purchaseWerks\",\n" +
            "       OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "       WSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       WSD.MATERIAL_ZH_DESC AS \"materialDesc\",\n" +
            "       WSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       WSD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OW.DATE_REQUIRED AS \"dateRequired\",\n" +
            "       OW.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "       WSD.BRAND AS \"brand\",\n" +
            "       WS.WORK_CODE AS \"workCode\",\n" +
            "       WS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       WSD.ROW_STATUS AS \"rowStatus\",\n" +
            "       WSD.ROW_COMMENT AS \"rowComment\",\n" +
            "       OW.SHIPPING_FANGSHI AS \"shippingFangshi\",\n" +
            "       CI.CDI AS \"categoryInfo\"\n" +
            "FROM ORDER_WERKS_DTL OWD\n" +
            "    LEFT JOIN ORDER_WERKS OW ON OWD.UUID = OW.UUID\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL WSD ON OWD.SPMS_WS_ID = WSD.SPMS_WS_ID AND OWD.ROW_ID_WS = WSD.ROW_ID\n" +
            "    LEFT JOIN ORDER_WORKSHOP WS ON WSD.UUID = WS.UUID\n" +
            "    LEFT JOIN (\n" +
            "        SELECT MS.MATERIAL_NO, MC1.CATEGORY_NAME||'-'||MC2.CATEGORY_NAME||'-'||MC3.CATEGORY_NAME AS \"CDI\"\n" +
            "        FROM MATERIAL_SPMS MS\n" +
            "                 LEFT JOIN MATERIAL_CATEGORY MC3 ON MS.CATEGORY_ID = MC3.CATEGORY_ID\n" +
            "                 LEFT JOIN MATERIAL_CATEGORY MC2 ON MC3.PARENT_ID = MC2.CATEGORY_ID\n" +
            "                 LEFT JOIN MATERIAL_CATEGORY MC1 ON MC2.PARENT_ID = MC1.CATEGORY_ID\n" +
            "    ) CI ON CI.MATERIAL_NO = WSD.MATERIAL_NO\n" +
            "WHERE OWD.UUID = #{uuid}\n")
    List<Map<String, Object>> orderWerksDtlInfo(@Param("uuid") String uuid);

    /** 根据工厂请购单 oaBianhao 查询请购单提交SRM表头信息 - 945496 */
    /** 20210409 —— 传给SRM的信息增加 采购工厂-purchaseWerks 字段。
     * 取工厂请购单表头采购地点字段，本地采购/其他——传值当前工厂代码；非本地采购/中国——传值 CN01 */
    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "       OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "       OW.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       OW.URGENCY AS \"urgency\",\n" +
            "       OW.WORK_CODE AS \"workCode\",\n" +
            "       OW.REQUEST_TYPE AS \"requestType\",\n" +
            "       U.LAST_NAME AS \"username\",\n" +
            "       OW.JUSTIFICATION AS \"justification\",\n" +
            "       OW.WERKS AS \"werks\",\n" +
            "       OW.WERKS AS \"purchaseWerks\"\n" +
            "FROM ORDER_WERKS OW LEFT JOIN SPMS_USER U ON OW.WORK_CODE = U.WORK_CODE " +
            " WHERE OW.OA_BIANHAO = #{oaBianhao} ")
    Map<String, Object> orderWerksInfo2(@Param("oaBianhao") String oaBianhao);

    /** 根据工厂请购单 oaBianhao 查询请购单提交SRM行项目信息 - 945496 */
    @Select("SELECT OW.SPMS_ID AS \"spmsId\",\n" +
            "       WS.WERKS AS \"werks\",\n" +
            "       W.WERKS_NAME_ZH AS \"werksNameZh\",\n" +
            "       OW.PROJECT_STATUS AS \"projectStatus\",\n" +
            "       OW.WERKS AS \"purchaseWerks\",\n" +
            "       OWD.ROW_ID_WERKS AS \"rowIdWerks\",\n" +
            "       WSD.MATERIAL_NO AS \"materialNo\",\n" +
            "       WSD.MATERIAL_ZH_DESC AS \"materialDesc\",\n" +
            "       WSD.APPROVAL_QTY AS \"approvalQty\",\n" +
            "       WSD.SUPPLEMENTORNOT AS \"supplementOrNot\",\n" +
            "       WSD.BASIC_UNIT AS \"basicUnit\",\n" +
            "       OW.DATE_REQUIRED AS \"dateRequired\",\n" +
            "       OW.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "       (CASE WHEN OW.FIELD_FOR_OA IS NULL THEN ' '\n" +
            "           ELSE OW.FIELD_FOR_OA END) AS \"fbk37\",\n" +
            "       WSD.BRAND AS \"brand\",\n" +
            "       WS.WORK_CODE AS \"workCode\",\n" +
            "       WS.PROJECT_CODE AS \"projectCode\",\n" +
            "       WS.PROJECT_OA_NUMBER AS \"projectOaNumber\",\n" +
            "       WS.COUNTRY AS \"country\",\n" +
            "       U.LAST_NAME AS \"username\",\n" +
            "       WS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       WSD.ROW_STATUS AS \"rowStatus\",\n" +
            "        (CASE WHEN WSD.ROW_COMMENT IS NULL THEN ' '\n" +
            "            ELSE WSD.ROW_COMMENT END) AS \"rowComment\",\n" +
            "       OW.SHIPPING_FANGSHI AS \"shippingFangshi\"\n" +
            "    FROM ORDER_WERKS_DTL OWD\n" +
            "        LEFT JOIN ORDER_WERKS OW ON OWD.UUID = OW.UUID\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL WSD ON OWD.SPMS_WS_ID = WSD.SPMS_WS_ID AND OWD.ROW_ID_WS = WSD.ROW_ID\n" +
            "        LEFT JOIN ORDER_WORKSHOP WS ON WSD.UUID = WS.UUID\n" +
            "       LEFT JOIN SPMS_USER U ON WS.WORK_CODE = U.WORK_CODE\n" +
            "       LEFT JOIN WERKS W on WS.WERKS = W.WERKS\n" +
            "    WHERE OW.OA_BIANHAO = #{oaBianhao} AND WSD.APPROVAL_QTY > 0 ORDER BY OWD.ROW_ID_WERKS ")
    List<Map<String, Object>> orderWerksDtlInfo2(@Param("oaBianhao") String oaBianhao);

    @Select("SELECT STATUS FROM ORDER_WORKSHOP WHERE UUID  = #{uuid}")
    String getOrderWorkshopStatus(@Param("uuid") String uuid);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.WERKS AS \"werks\",\n" +
            "       RE.MATERIAL_NO  AS \"materialNo\",\n" +
            "       RE.\"numberOfApplications\" AS \"numberOfApplications\",\n" +
            "       RE.\"totalQty\" AS \"totalQty\"," +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\"\n" +
            "FROM (\n" +
            "    SELECT OW.WERKS,\n" +
            "           WSD.MATERIAL_NO,\n" +
            "           COUNT(WSD.MATERIAL_NO) AS \"numberOfApplications\",\n" +
            "           SUM(WSD.APPROVAL_QTY) AS \"totalQty\"\n" +
            "    FROM ORDER_WERKS OW\n" +
            "        LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "        LEFT JOIN ORDER_WORKSHOP_DTL WSD ON OWD.SPMS_WS_ID = WSD.SPMS_WS_ID AND WSD.ROW_ID = OWD.ROW_ID_WS\n" +
            "    WHERE OW.STATUS != '0'\n" +
            "      AND OW.WERKS = #{werks}\n" +
            " <when test='materialNo!=null and materialNo != \"\"'>AND WSD.MATERIAL_NO = #{materialNo}</when>\n" +
            " <when test='beginDate!=null and beginDate != \"\"'>AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd')</when>\n" +
            " <when test='endDate!=null and endDate != \"\"'> AND TO_DATE(OW.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd')</when>" +
            "    GROUP BY OW.WERKS, WSD.MATERIAL_NO\n" +
            "    ORDER BY COUNT(MATERIAL_NO) DESC\n" +
            "    ) RE LEFT JOIN MATERIAL_SAP MS ON RE.MATERIAL_NO = MS.MATERIAL_NO" +
            "</script>")
    /** 高频SKU报表/工厂请购单为基准，申请购买次数非数量排序 */
    List<Map<String, Object>> getFrequencyMaterialNo(@Param("werks") String werks,
                                                     @Param("materialNo") String materialNo,
                                                     @Param("beginDate") String beginDate,
                                                     @Param("endDate") String endDate);
}