<?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.pqs9900.mapper.State.MonthYearDayFlowMapper">

    <resultMap type="com.pqs9900.pojo.State.MonthYearDayFlow" id="MonthYearDayFlowMapperMap" autoMapping="true">
    </resultMap>
    <select id="dayByMonth" resultMap="MonthYearDayFlowMapperMap">
	select
	t.mdate timeId,
	t.actflow actFlow,
	sum(t.actflow) over(order by t.mdate asc) as dayMuchFlow
	from
	(
	SELECT
	A .mdate mdate,
	NVL (SUM(B.ACTUALFLOW/1024/1024), 0) AS actflow
	FROM
	(
	SELECT
	TO_CHAR (
	TO_DATE (#{yearmonth}, 'yyyy-mm') + LEVEL - 1,
	'yyyy-mm-dd'
	) mdate
	FROM
	dual CONNECT BY LEVEL &lt;= TO_NUMBER (
	TO_CHAR (
	LAST_DAY (
	TO_DATE (#{yearmonth}, 'yyyy-mm')
	),
	'dd'
	)
	)
	) A
	LEFT JOIN PQS_ACTUALFLOW B ON A .mdate = TO_CHAR (TIMEID, 'yyyy-mm-dd')
	AND b.DEV_INDEX = #{devIndex}
	GROUP BY
	A .mdate
	ORDER BY
	A .mdate
	)  t
</select>

    <select id="getAvgFlow" resultType="float">
    select (baseFlow+expend)/allday from
	(SELECT
	nvl(d.fm_number,( SELECT FM_NUMBER FROM PQS_FLOWMEAL WHERE type = 0 AND SECOND_TYPE = 0 )) baseFlow,
	nvl(e.fm_number,0) expend,
	( SELECT TO_CHAR ( LAST_DAY( TO_DATE ( #{yearmonth}, 'yyyy-MM' ) ), 'dd' ) FROM DUAL ) allday
    FROM
	pq_device f
	LEFT join pqs_devfmass a on f.dev_index = a.dev_index
	LEFT JOIN pqs_flowmeal d ON a.fm_base_index = d.fm_index
	LEFT JOIN pqs_flowmeal e ON a.fm_ream_index = e.fm_index
    WHERE
	f.dev_index = #{devIndex}
	) tt
	</select>
</mapper>
