<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mes.oee.mapper.OeeMapper">

  <resultMap id="oeeMap" type="com.zioinfo.mes.oee.MesOeeRecord">
    <id property="id" column="id"/>
    <result property="equipmentCode" column="equipment_code"/>
    <result property="recordDate" column="record_date"/>
    <result property="plannedTimeMin" column="planned_time_min"/>
    <result property="runTimeMin" column="run_time_min"/>
    <result property="downtimeMin" column="downtime_min"/>
    <result property="downtimeCode" column="downtime_code"/>
    <result property="idealCycleMin" column="ideal_cycle_min"/>
    <result property="totalCount" column="total_count"/>
    <result property="goodCount" column="good_count"/>
    <result property="createdBy" column="created_by"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="oeeMap">
    SELECT * FROM mes_oee
    <where><if test="equipmentCode != null and equipmentCode != ''">AND equipment_code = #{equipmentCode}</if></where>
    ORDER BY record_date DESC, id DESC LIMIT #{limit}
  </select>

  <select id="findById" resultMap="oeeMap">SELECT * FROM mes_oee WHERE id = #{id}</select>

  <insert id="insert" parameterType="com.zioinfo.mes.oee.MesOeeRecord" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mes_oee (equipment_code, record_date, planned_time_min, run_time_min, downtime_min,
      downtime_code, ideal_cycle_min, total_count, good_count, created_by)
    VALUES (#{equipmentCode}, #{recordDate}, #{plannedTimeMin}, #{runTimeMin}, #{downtimeMin},
      #{downtimeCode}, #{idealCycleMin}, #{totalCount}, #{goodCount}, #{createdBy})
  </insert>

  <select id="downtimePareto" resultType="map">
    SELECT COALESCE(downtime_code,'UNKNOWN') AS downtime_code, COALESCE(SUM(downtime_min),0) AS downtime_min
    FROM mes_oee
    WHERE downtime_min > 0 AND record_date &gt;= CURRENT_DATE - (#{days} || ' days')::interval
    GROUP BY downtime_code ORDER BY downtime_min DESC
  </select>

</mapper>
