<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.steerinfo.ems.trmworkprocmaterialvalue.mapper.TRmWorkprocMaterialValueMapper">
  <resultMap id="BaseResultMap" type="com.steerinfo.ems.trmworkprocmaterialvalue.model.TRmWorkprocMaterialValue">
    <id column="CLOCK" jdbcType="VARCHAR" property="clock" />
    <id column="TIMEGRANID" jdbcType="VARCHAR" property="timegranid" />
    <id column="ITEMID" jdbcType="VARCHAR" property="itemid" />
    <result column="SUMVALUE" jdbcType="DECIMAL" property="sumvalue" />
    <result column="ISLOCK" jdbcType="VARCHAR" property="islock" />
    <result column="LOCKID" jdbcType="VARCHAR" property="lockid" />
    <result column="ACTUALVALUE" jdbcType="DECIMAL" property="actualvalue" />
    <result column="CORRECTVALUE" jdbcType="DECIMAL" property="correctvalue" />
    <result column="APPORTVALUE" jdbcType="DECIMAL" property="apportvalue" />
    <result column="UCVALUE" jdbcType="DECIMAL" property="ucvalue" />
    <result column="SUMUCVALUE" jdbcType="DECIMAL" property="sumucvalue" />
    <result column="BZ" jdbcType="VARCHAR" property="bz" />
    <result column="UPTIME" jdbcType="TIMESTAMP" property="uptime" />
    <result column="UPMAN" jdbcType="VARCHAR" property="upman" />
    <result column="UPDATETIME" jdbcType="VARCHAR" property="updatetime" />
    <result column="USERID" jdbcType="VARCHAR" property="userid" />
    <result column="JTZ" jdbcType="DECIMAL" property="jtz"/>
    <result column="PKL" jdbcType="DECIMAL" property="pkl"/>
  </resultMap>
  <delete id="deleteByPrimaryKey" parameterType="map">
    delete from T_RM_WORKPROC_MATERIAL_VALUE
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </delete>
  <delete id="deleteBySelectiveElement" parameterType="java.util.HashMap">
    delete from T_RM_WORKPROC_MATERIAL_VALUE
    where 1!=1
      <if test="timegranid != null and timegranid != ''">
        and TIMEGRANID = #{timegranid}
      </if>
      <if test="itemid != null and itemid != ''">
        and ITEMID = #{itemid}
      </if>
      <if test="sumvalue != null">
        and SUMVALUE = #{sumvalue}
      </if>
      <if test="islock != null and islock != ''">
        and ISLOCK = #{islock}
      </if>
      <if test="lockid != null and lockid != ''">
        and LOCKID = #{lockid}
      </if>
      <if test="actualvalue != null">
        and ACTUALVALUE = #{actualvalue}
      </if>
      <if test="correctvalue != null">
        and CORRECTVALUE = #{correctvalue}
      </if>
      <if test="apportvalue != null">
        and APPORTVALUE = #{apportvalue}
      </if>
      <if test="ucvalue != null">
        and UCVALUE = #{ucvalue}
      </if>
      <if test="sumucvalue != null">
        and SUMUCVALUE = #{sumucvalue}
      </if>
      <if test="bz != null and bz != ''">
        and BZ = #{bz}
      </if>
  </delete>
  <insert id="insert" parameterType="com.steerinfo.ems.trmworkprocmaterialvalue.model.TRmWorkprocMaterialValue">
    insert into T_RM_WORKPROC_MATERIAL_VALUE (CLOCK, TIMEGRANID, ITEMID,
      SUMVALUE, ISLOCK, LOCKID,
      ACTUALVALUE, CORRECTVALUE, APPORTVALUE,
      UCVALUE, SUMUCVALUE, BZ, UPTIME, UPMAN, USERID, UPDATETIME
      )
    values (#{clock,jdbcType=VARCHAR}, #{timegranid,jdbcType=VARCHAR}, #{itemid,jdbcType=VARCHAR},
      #{sumvalue,jdbcType=DECIMAL}, #{islock,jdbcType=VARCHAR}, #{lockid,jdbcType=VARCHAR},
      #{actualvalue,jdbcType=DECIMAL}, #{correctvalue,jdbcType=DECIMAL}, #{apportvalue,jdbcType=DECIMAL},
      #{ucvalue,jdbcType=DECIMAL}, #{sumucvalue,jdbcType=DECIMAL}, #{bz,jdbcType=VARCHAR},
    to_date ( #{uptime,jdbcType=TIME} , 'YYYY-MM-DD HH24:MI:SS' ), #{upman,jdbcType=VARCHAR}, #{userid,jdbcType=VARCHAR},
    #{updatetime,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.steerinfo.ems.trmworkprocmaterialvalue.model.TRmWorkprocMaterialValue">
    insert into T_RM_WORKPROC_MATERIAL_VALUE
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="clock != null">
        CLOCK,
      </if>
      <if test="timegranid != null">
        TIMEGRANID,
      </if>
      <if test="itemid != null">
        ITEMID,
      </if>
      <if test="sumvalue != null">
        SUMVALUE,
      </if>
      <if test="islock != null">
        ISLOCK,
      </if>
      <if test="lockid != null">
        LOCKID,
      </if>
      <if test="actualvalue != null">
        ACTUALVALUE,
      </if>
      <if test="correctvalue != null">
        CORRECTVALUE,
      </if>
      <if test="apportvalue != null">
        APPORTVALUE,
      </if>
      <if test="ucvalue != null">
        UCVALUE,
      </if>
      <if test="sumucvalue != null">
        SUMUCVALUE,
      </if>
      <if test="bz != null">
        BZ,
      </if>
      <if test="upman != null">
        UPMAN,
      </if>
      <if test="uptime != null">
        UPTIME,
      </if>
      <if test="userid != null">
        USERID,
      </if>
      <if test="updatetime != null">
        UPDATETIME,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="clock != null">
        #{clock,jdbcType=VARCHAR},
      </if>
      <if test="timegranid != null">
        #{timegranid,jdbcType=VARCHAR},
      </if>
      <if test="itemid != null">
        #{itemid,jdbcType=VARCHAR},
      </if>
      <if test="sumvalue != null">
        #{sumvalue,jdbcType=DECIMAL},
      </if>
      <if test="islock != null">
        #{islock,jdbcType=VARCHAR},
      </if>
      <if test="lockid != null">
        #{lockid,jdbcType=VARCHAR},
      </if>
      <if test="actualvalue != null">
        #{actualvalue,jdbcType=DECIMAL},
      </if>
      <if test="correctvalue != null">
        #{correctvalue,jdbcType=DECIMAL},
      </if>
      <if test="apportvalue != null">
        #{apportvalue,jdbcType=DECIMAL},
      </if>
      <if test="ucvalue != null">
        #{ucvalue,jdbcType=DECIMAL},
      </if>
      <if test="sumucvalue != null">
        #{sumucvalue,jdbcType=DECIMAL},
      </if>
      <if test="bz != null">
        #{bz,jdbcType=VARCHAR},
      </if>
      <if test="upman != null">
        #{upman,jdbcType=VARCHAR},
      </if>
      <if test="uptime != null">
        to_date ( #{uptime,jdbcType=TIME} , 'YYYY-MM-DD HH24:MI:SS' )
      </if>
      <if test="userid != null">
        #{userid,jdbcType=VARCHAR},
      </if>
      <if test="updatetime != null">
        #{updatetime,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

  <update id="updateValue" parameterType="java.util.HashMap">
    update T_RM_WORKPROC_MATERIAL_VALUE
    set
      ACTUALVALUE = ACTUALVALUE + #{qty,jdbcType=DECIMAL},
      APPORTVALUE = APPORTVALUE + #{qty,jdbcType=DECIMAL},
      BZ = BZ || ',' || #{fid,jdbcType=VARCHAR}
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </update>

  <insert id="insertValue" parameterType="java.util.HashMap">
      INSERT INTO T_RM_WORKPROC_MATERIAL_VALUE (
        CLOCK,
        TIMEGRANID,
        SUMVALUE,
        ISLOCK,
        LOCKID,
        ITEMID,
        ACTUALVALUE,
        CORRECTVALUE,
        APPORTVALUE,
        UCVALUE,
        SUMUCVALUE,
        BZ,
        USERID,
        UPDATETIME
      )
      VALUES
        (
          #{clock,jdbcType=VARCHAR},
          #{timegranid,jdbcType=VARCHAR},
          0,
          NULL,
          NULL,
          #{itemid,jdbcType=VARCHAR},
          #{qty,jdbcType=DECIMAL},
          0,
          #{qty,jdbcType=DECIMAL},
          0,
          0,
          'MES' || '-' || to_char(SYSDATE,'yyyy/MM/dd HH24:mi:ss') || ' fid:' || #{fid,jdbcType=VARCHAR},
          #{userid,jdbcType=VARCHAR},
          #{updatetime,jdbcType=VARCHAR}
        )
  </insert>

  <update id="updateByPrimaryKey" parameterType="com.steerinfo.ems.trmworkprocmaterialvalue.model.TRmWorkprocMaterialValue">
    update T_RM_WORKPROC_MATERIAL_VALUE
    set SUMVALUE = #{sumvalue,jdbcType=DECIMAL},
      ISLOCK = #{islock,jdbcType=VARCHAR},
      LOCKID = #{lockid,jdbcType=VARCHAR},
      ACTUALVALUE = #{actualvalue,jdbcType=DECIMAL},
      CORRECTVALUE = #{correctvalue,jdbcType=DECIMAL},
      APPORTVALUE = #{apportvalue,jdbcType=DECIMAL},
      UCVALUE = #{ucvalue,jdbcType=DECIMAL},
      SUMUCVALUE = #{sumucvalue,jdbcType=DECIMAL},
      BZ = #{bz,jdbcType=VARCHAR}
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.steerinfo.ems.trmworkprocmaterialvalue.model.TRmWorkprocMaterialValue">
    update T_RM_WORKPROC_MATERIAL_VALUE
    <set>
      <if test="sumvalue != null">
        SUMVALUE = #{sumvalue,jdbcType=DECIMAL},
      </if>
      <if test="islock != null">
        ISLOCK = #{islock,jdbcType=VARCHAR},
      </if>
      <if test="lockid != null">
        LOCKID = #{lockid,jdbcType=VARCHAR},
      </if>
      <if test="actualvalue != null">
        ACTUALVALUE = #{actualvalue,jdbcType=DECIMAL},
      </if>
      <if test="correctvalue != null">
        CORRECTVALUE = #{correctvalue,jdbcType=DECIMAL},
      </if>
      <if test="apportvalue != null">
        APPORTVALUE = #{apportvalue,jdbcType=DECIMAL},
      </if>
      <if test="ucvalue != null">
        UCVALUE = #{ucvalue,jdbcType=DECIMAL},
      </if>
      <if test="sumucvalue != null">
        SUMUCVALUE = #{sumucvalue,jdbcType=DECIMAL},
      </if>
      <if test="jtz != null">
        JTZ = #{jtz,jdbcType=DECIMAL},
      </if>
      <if test="bz != null">
        BZ = #{bz,jdbcType=VARCHAR},
      </if>
      <if test="upman != null">
        UPMAN = #{upman,jdbcType=VARCHAR},
      </if>
      <if test="uptime != null">
       UPTIME = to_date ( #{uptime,jdbcType=TIME} , 'YYYY-MM-DD HH24:MI:SS' )
      </if>
      <if test="userid != null">
       USERID = #{userid,jdbcType=VARCHAR},
      </if>
      <if test="updatetime != null">
        UPDATETIME = #{updatetime,jdbcType=VARCHAR},
      </if>
    </set>
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </update>
  <select id="selectByPrimaryKey" parameterType="map" resultMap="BaseResultMap">
    select CLOCK, TIMEGRANID, ITEMID, SUMVALUE, ISLOCK, LOCKID, ACTUALVALUE, CORRECTVALUE,
    APPORTVALUE, UCVALUE, SUMUCVALUE, BZ, UPMAN, UPTIME, USERID, UPDATETIME
    from T_RM_WORKPROC_MATERIAL_VALUE
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </select>
  <select id="selectByUPrimaryKey" parameterType="map" resultMap="BaseResultMap">
    select CLOCK, TIMEGRANID, ITEMID, SUMVALUE, ISLOCK, LOCKID, ACTUALVALUE, CORRECTVALUE,
    APPORTVALUE, UCVALUE, SUMUCVALUE, BZ
    from T_RM_WORKPROC_MATERIAL_VALUE
    where CLOCK = #{clock,jdbcType=VARCHAR}
      and TIMEGRANID = #{timegranid,jdbcType=VARCHAR}
      and ITEMID = #{itemid,jdbcType=VARCHAR}
  </select>
  <select id="selectByParameters" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    select CLOCK, TIMEGRANID, ITEMID, SUMVALUE, ISLOCK, LOCKID, ACTUALVALUE, CORRECTVALUE,
    APPORTVALUE, UCVALUE, SUMUCVALUE, BZ, UPMAN, UPTIME, USERID, UPDATETIME
    from T_RM_WORKPROC_MATERIAL_VALUE
    <where>
      <if test="timegranid != null and timegranid != ''">
        and TIMEGRANID = #{timegranid}
      </if>
      <if test="itemid != null and itemid != ''">
        and ITEMID = #{itemid}
      </if>
      <if test="sumvalue != null">
        and SUMVALUE = #{sumvalue}
      </if>
      <if test="clock != null and clock != ''">
        and CLOCK = #{clock}
      </if>
      <if test="islock != null and islock != ''">
        and ISLOCK = #{islock}
      </if>
      <if test="lockid != null and lockid != ''">
        and LOCKID = #{lockid}
      </if>
      <if test="actualvalue != null">
        and ACTUALVALUE = #{actualvalue}
      </if>
      <if test="correctvalue != null">
        and CORRECTVALUE = #{correctvalue}
      </if>
      <if test="apportvalue != null">
        and APPORTVALUE = #{apportvalue}
      </if>
      <if test="ucvalue != null">
        and UCVALUE = #{ucvalue}
      </if>
      <if test="sumucvalue != null">
        and SUMUCVALUE = #{sumucvalue}
      </if>
      <if test="bz != null and bz != ''">
        and BZ = #{bz}
      </if>

    </where>
  </select>
  <select id="selectLikeByParameters" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    select CLOCK, TIMEGRANID, ITEMID, SUMVALUE, ISLOCK, LOCKID, ACTUALVALUE, CORRECTVALUE,
    APPORTVALUE, UCVALUE, SUMUCVALUE, BZ, UPMAN, UPTIME, USERID, UPDATETIME
    from T_RM_WORKPROC_MATERIAL_VALUE
    <where>
      <if test="timegranid != null and timegranid != ''">
        and TIMEGRANID LIKE '%${timegranid}%'
      </if>
      <if test="itemid != null and itemid != ''">
        and ITEMID LIKE '%${itemid}%'
      </if>
      <if test="sumvalue != null">
        and SUMVALUE = #{sumvalue}
      </if>
      <if test="clock != null and clock != ''">
        and CLOCK LIKE '%${clock}%'
      </if>
      <if test="islock != null and islock != ''">
        and ISLOCK LIKE '%${islock}%'
      </if>
      <if test="lockid != null and lockid != ''">
        and LOCKID LIKE '%${lockid}%'
      </if>
      <if test="actualvalue != null">
        and ACTUALVALUE = #{actualvalue}
      </if>
      <if test="correctvalue != null">
        and CORRECTVALUE = #{correctvalue}
      </if>
      <if test="apportvalue != null">
        and APPORTVALUE = #{apportvalue}
      </if>
      <if test="ucvalue != null">
        and UCVALUE = #{ucvalue}
      </if>
      <if test="sumucvalue != null">
        and SUMUCVALUE = #{sumucvalue}
      </if>
      <if test="bz != null and bz != ''">
        and BZ LIKE '%${bz}%'
      </if>
    </where>
  </select>
  <insert id="batchInsert" parameterType="java.util.List">
    insert into T_RM_WORKPROC_MATERIAL_VALUE
      (CLOCK,
      TIMEGRANID, ITEMID, SUMVALUE,
      ISLOCK, LOCKID, ACTUALVALUE,
      CORRECTVALUE, APPORTVALUE, UCVALUE,
      SUMUCVALUE, BZ)
    ( <foreach collection="list" item="item" separator="union all">
   select
      #{item.clock,jdbcType=VARCHAR},
      #{item.timegranid,jdbcType=VARCHAR}, #{item.itemid,jdbcType=VARCHAR}, #{item.sumvalue,jdbcType=DECIMAL},
      #{item.islock,jdbcType=VARCHAR}, #{item.lockid,jdbcType=VARCHAR}, #{item.actualvalue,jdbcType=DECIMAL},
      #{item.correctvalue,jdbcType=DECIMAL}, #{item.apportvalue,jdbcType=DECIMAL}, #{item.ucvalue,jdbcType=DECIMAL},
      #{item.sumucvalue,jdbcType=DECIMAL}, #{item.bz,jdbcType=VARCHAR} from dual
   </foreach> )
  </insert>
  <update id="batchUpdate" parameterType="java.util.List">
     update T_RM_WORKPROC_MATERIAL_VALUE
     set
       CLOCK=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.clock,jdbcType=VARCHAR}
       </foreach>
       ,TIMEGRANID=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.timegranid,jdbcType=VARCHAR}
       </foreach>
       ,ITEMID=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.itemid,jdbcType=VARCHAR}
       </foreach>
       ,SUMVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.sumvalue,jdbcType=DECIMAL}
       </foreach>
       ,ISLOCK=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.islock,jdbcType=VARCHAR}
       </foreach>
       ,LOCKID=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.lockid,jdbcType=VARCHAR}
       </foreach>
       ,ACTUALVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.actualvalue,jdbcType=DECIMAL}
       </foreach>
       ,CORRECTVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.correctvalue,jdbcType=DECIMAL}
       </foreach>
       ,APPORTVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.apportvalue,jdbcType=DECIMAL}
       </foreach>
       ,UCVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.ucvalue,jdbcType=DECIMAL}
       </foreach>
       ,SUMUCVALUE=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.sumucvalue,jdbcType=DECIMAL}
       </foreach>
       ,BZ=
       <foreach collection="list" item="item" index="index" separator=" " open="case CLOCK" close="end">
          when #{item.clock,jdbcType=VARCHAR} then #{item.bz,jdbcType=VARCHAR}
       </foreach>
     where CLOCK in
     <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
    #{item.clock,jdbcType=VARCHAR}
     </foreach>
  </update>
  <delete id="batchDelete" parameterType="java.util.List">
    delete from T_RM_WORKPROC_MATERIAL_VALUE
    where CLOCK in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
      #{id}
    </foreach>
  </delete>

    <!-- 能源平衡管理-工序与原材料数据维护页面，查询数据 -->
    <select id="getWorkprocMaterialDetail" parameterType="java.util.HashMap" resultType = "Map">
      select * from VM_WORKPROC_MATERIAL_DETAIL
      <where>
	      <if test="itemid != null and itemid != ''">
	        and ITEMID = #{itemid}
	      </if>
	      <if test="itemname != null and itemname != ''">
	           and ITEMNAME = #{itemname}
	         </if>
	      <if test="timegranid != null and timegranid != ''">
	        and TIMEGRANID = #{timegranid}
	      </if>
	      <if test="clock != null and clock != ''">
	        ${clock}
	      </if>
	      <if test="workprocid != null and workprocid != ''">
	        ${workprocid}
	      </if>
	      <if test="materialid != null and materialid != ''">
	        ${materialid}
	      </if>
      </where>
      order by clock, itemid
    </select>

    <!--获取最终值-->
    <select id="getSumvalue" parameterType="java.util.HashMap" resultType="double">
        SELECT NVL(SUM(APPORTVALUE),0) FROM T_RM_WORKPROC_MATERIAL_VALUE
        <where>
            <if test="itemid !=null and itemid != '' ">
                AND  ITEMID=#{itemid}
            </if>
            <if test="clock !=null and clock !='' ">
                AND CLOCK LIKE '${clock}%'
            </if>
            <if test="timegranid !=null and timegranid !='' ">
                AND  TIMEGRANID=#{timegranid}
            </if>
        </where>
    </select>

    <!--获取月实绩产量和月累计产量-->
    <select id="getMonthOutPut" parameterType="java.util.HashMap" resultType="Map">
        SELECT
          nvl( b.cl, 0 ) cl,
          nvl( b.lj, 0 ) lj
        FROM
            (
            SELECT
                t.itemid,
                substr( v.clock, 0, 7 ) mm,
                sum(v.apportvalue ) "原材料消耗",
                workprocid
            FROM
                T_RM_WORKPROC_MATERIAL t
                INNER JOIN T_RM_WORKPROC_MATERIAL_VALUE v ON t.itemid = v.itemid

            <where>
                <if test="workprocid != null and workprocid !='' ">
                    t.workprocid = #{workprocid}
                </if>
                <if test="timegranid != null and timegranid !='' ">
                    AND v.timegranid = #{timegranid}
                </if>
                <if test="clock != null and clock != ''">
                    AND substr(v.clock, 0, 7 ) = #{clock}
                </if>
                <if test="itemid != null and itemid != '' ">
                    AND t.itemid = #{itemid}
                </if>
            </where>
            GROUP BY
                t.itemid,
                substr(v.clock, 0, 7),
                t.workprocid
            ) a
            LEFT JOIN (
            SELECT
                workprocid,
                sum( w.apportvalue ) cl,
                sum(w.SUMVALUE ) lj,
                substr( w.clock, 0, 7 ) mm
            FROM
                t_rm_workproc_product_value w
                INNER JOIN t_rm_workproc_product p ON w.itemid = p.itemid
            <where >
                <if test="timegranid != null and  timegranid != ''">
                    w.timegranid = #{timegranid}
                </if>
                <if test="workprpcid != null and workprpcid != ''">
                    AND p.isprimary = '1'
                    AND P.WORKPROCID = #{workprpcid}
                </if>
                <if test="clock != null and clock != '' ">
                    AND substr( w.clock, 0, 7 ) = substr( #{clock}, 0, 7 )
                </if>
            </where>
            GROUP BY
                p.workprocid,
            substr(w.clock, 0, 7 )) b
            ON a.mm = b.mm
            AND a.workprocid = b.workprocid
    </select>

    <!-- 获取项目值（从IF_MES_EMS_SWAPFILE表,status=1的已处理数据） -->
    <select id="getItemval" parameterType="java.util.HashMap" resultType="Map">
        select nvl(sum(qty),0) val,listagg(fid,',') within group(order by fid) ids
        from IF_MES_EMS_SWAPFILE where status = 1 and type = 1
        <if test="clock !=null and clock != ''">
            and to_char(PRODATE,'yyyy-MM-dd') = #{clock}
        </if>
        <if test="materialcode !=null and materialcode !='' ">
            and materialcode = #{materialcode}
        </if>
        <if test="prodline !=null and prodline !='' ">
            and instr(',${prodline},', ','||prodline||',')>0
        </if>
    </select>

    <!-- 获取项目值（从IF_MES_EMS_SWAPFILE表） -->
    <select id="getAllItemval" parameterType="java.util.HashMap" resultType="Map">
        select nvl(sum(qty),0) val,listagg(fid,',') within group(order by fid) ids
        from IF_MES_EMS_SWAPFILE where type = 1
        <if test="clock !=null and clock != ''">
            and to_char(PRODATE,'yyyy-MM-dd') = #{clock}
        </if>
        <if test="materialcode !=null and materialcode !='' ">
            and materialcode = #{materialcode}
        </if>
        <if test="prodline !=null and prodline !='' ">
            and instr(',${prodline},', ','||prodline||',')>0
        </if>
    </select>

  <!-- 从报表获取数据 -->
  <select id="getReportValue" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    SELECT TO_CHAR(DATES,'yyyy-MM-dd') AS CLOCK,
    CONCAT(PROCEDURE,CONCAT(MATERIAL,'R')) AS ITEMID,
    WEIGHT AS APPORTVALUE FROM V_INPUTOUTPUT WHERE 1=1 AND MATERIAL LIKE '%Y%' AND WEIGHT IS NOT NULL
      <if test="clock != null and clock != ''">
       AND DATES >= TO_DATE(#{clock}, 'yyyy-MM-dd')
      </if>
    <if test="clocke != null and clocke != ''">
      AND DATES &lt;= TO_DATE(#{clocke}, 'yyyy-MM-dd')
    </if>
  </select>

  <!--根据原料对应表，获取报表数据-->
  <select id="getMaterialValue" parameterType="java.util.HashMap" resultMap="BaseResultMap">
    SELECT * FROM V_MATERIAL_VALUE WHERE  clock &gt;=#{clock} AND clock &lt;=#{clocke}
  </select>
  <select id="getpkdata" parameterType="java.util.HashMap" resultType="Map">
            SELECT *
            FROM (SELECT T.TIMEGRANID,
            T.SUMVALUE,
            T.BZ,
            T.ISLOCK,
            T.LOCKID,
            T.CLOCK,
            T.ITEMID,
            T.ACTUALVALUE,
            T.CORRECTVALUE,
            T.APPORTVALUE,
            T.JTZ,
            T.UCVALUE,
            T.SUMUCVALUE,
            T1.WORKPROCID,
            T1.MATERIALID,
            T1.SEQNO,
            T1.USEFLAG,
            T1.PRIORITY,
            T1.UNITID,
            T1.ITEMTYPE,
            T2.NAME         AS MATERIALNAME,
            T3.NAME         AS WORKPROCNAME
            FROM t_rm_workproc_material_value T
            LEFT JOIN T_RM_WORKPROC_MATERIAL T1
            ON T.ITEMID = T1.ITEMID
            LEFT JOIN T_RM_MATERIAL T2
            ON T1.MATERIALID = T2.ID
            LEFT JOIN T_RM_WORKPROC T3
            ON T1.WORKPROCID = T3.ID
            WHERE T.TIMEGRANID = 'DAY') T
            <where>
              <if test="clock != null and clock != ''">
               AND to_char(to_date(T.CLOCK, 'yyyy-mm-dd'),'yyyy-mm') = #{clock}
              </if>
              <if test="workprocid != null and workprocid != ''">
                AND T.WORKPROCID =#{workprocid}
              </if>
              <if test="materialid != null and materialid != ''">
                AND T.MATERIALID =#{materialid}
              </if>
            </where>
  </select>
</mapper>
