package com.ruoyi.web.sales.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.web.sales.domain.SalesPriceItem;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface SalesPriceItemMapper extends BaseMapper<SalesPriceItem> {
    @Select({
            "<script>",
            "SELECT",
            "  spi.id, spi.org_id, spi.price_num, spi.material_num, spi.level, spi.variety, spi.unit,",
            "  spi.unit_price, spi.pickup_price, spi.remark, spi.del_flag, spi.create_by, spi.create_time, spi.update_by, spi.update_time,",
            "  sp.price_type AS priceType,",
            "  bpt.price_type_name AS priceTypeName,",
            "  sp.effective_date AS effectiveDate,",
            "  sp.expire_date AS expireDate",
            "FROM sales_price_item spi",
            "JOIN sales_price sp",
            "  ON sp.org_id = spi.org_id",
            " AND sp.price_num = spi.price_num",
            "LEFT JOIN base_price_type bpt",
            "  ON bpt.org_id = sp.org_id",
            " AND bpt.price_type_num = sp.price_type",
            "WHERE spi.org_id = #{orgId}",
            "  AND spi.del_flag = '0'",
            "  AND sp.del_flag = '0'",
            "<if test='priceType != null and priceType != \"\"'>",
            "  AND sp.price_type = #{priceType}",
            "</if>",
            "<if test='effectiveDateStart != null'>",
            "  AND sp.effective_date &gt;= #{effectiveDateStart}",
            "</if>",
            "<if test='effectiveDateEnd != null'>",
            "  AND sp.effective_date &lt;= #{effectiveDateEnd}",
            "</if>",
            "ORDER BY sp.effective_date DESC, sp.id DESC, spi.id ASC",
            "</script>"
    })
    List<SalesPriceItem> listByJoin(@Param("orgId") String orgId,
                                    @Param("priceType") String priceType,
                                    @Param("effectiveDateStart") Date effectiveDateStart,
                                    @Param("effectiveDateEnd") Date effectiveDateEnd);
}
