<?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="cn.com.countrygarden.bi.main.dao.eba.TbmMaintainTaskMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.eba.TbmMaintainTask">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="task_id" jdbcType="VARCHAR" property="taskId"/>
        <result column="category_id" jdbcType="BIGINT" property="categoryId"/>
        <result column="create_date" jdbcType="TIMESTAMP" property="createDate"/>
        <result column="creator" jdbcType="VARCHAR" property="creator"/>
        <result column="curr_id" jdbcType="BIGINT" property="currId"/>
        <result column="operate_date" jdbcType="TIMESTAMP" property="operateDate"/>
        <result column="operator" jdbcType="VARCHAR" property="operator"/>
        <result column="origin_id" jdbcType="BIGINT" property="originId"/>
        <result column="owner_id" jdbcType="BIGINT" property="ownerId"/>
        <result column="sour_id" jdbcType="BIGINT" property="sourId"/>
        <result column="task_state" jdbcType="BIGINT" property="taskState"/>
        <result column="cate_id" jdbcType="BIGINT" property="cateId"/>
        <result column="project_id" jdbcType="BIGINT" property="projectId"/>
        <result column="location" jdbcType="VARCHAR" property="location"/>
        <result column="odd_num" jdbcType="VARCHAR" property="oddNum"/>
        <result column="task_desc" jdbcType="VARCHAR" property="taskDesc"/>
        <result column="select_proc" jdbcType="VARCHAR" property="selectProc"/>
        <result column="conn_phone" jdbcType="VARCHAR" property="connPhone"/>
        <result column="repair_user" jdbcType="VARCHAR" property="repairUser"/>
        <result column="appoint_time" jdbcType="TIMESTAMP" property="appointTime"/>
        <result column="build_location" jdbcType="VARCHAR" property="buildLocation"/>
        <result column="building_id" jdbcType="BIGINT" property="buildingId"/>
        <result column="cancel_date" jdbcType="TIMESTAMP" property="cancelDate"/>
        <result column="cate_number" jdbcType="BIGINT" property="cateNumber"/>
        <result column="complete_date" jdbcType="TIMESTAMP" property="completeDate"/>
        <result column="cp_revoke_state" jdbcType="INTEGER" property="cpRevokeState"/>
        <result column="depart_id" jdbcType="BIGINT" property="departId"/>
        <result column="destory_date" jdbcType="TIMESTAMP" property="destoryDate"/>
        <result column="destroy_order_satisfy_desc" jdbcType="VARCHAR" property="destroyOrderSatisfyDesc"/>
        <result column="destroy_order_satisfy_id" jdbcType="BIGINT" property="destroyOrderSatisfyId"/>
        <result column="device_id" jdbcType="BIGINT" property="deviceId"/>
        <result column="help_userids" jdbcType="VARCHAR" property="helpUserids"/>
        <result column="is_again" jdbcType="INTEGER" property="isAgain"/>
        <result column="is_avail" jdbcType="BIGINT" property="isAvail"/>
        <result column="is_timeout_complete" jdbcType="INTEGER" property="isTimeoutComplete"/>
        <result column="maintain_userid" jdbcType="BIGINT" property="maintainUserid"/>
        <result column="mtain_duration" jdbcType="DOUBLE" property="mtainDuration"/>
        <result column="mtain_users" jdbcType="VARCHAR" property="mtainUsers"/>
        <result column="ql_revoke_state" jdbcType="INTEGER" property="qlRevokeState"/>
        <result column="recieve_cate" jdbcType="BIGINT" property="recieveCate"/>
        <result column="recieve_date" jdbcType="TIMESTAMP" property="recieveDate"/>
        <result column="reject_path" jdbcType="VARCHAR" property="rejectPath"/>
        <result column="respone_type" jdbcType="INTEGER" property="responeType"/>
        <result column="revoke_url" jdbcType="VARCHAR" property="revokeUrl"/>
        <result column="rv_revoke_state" jdbcType="INTEGER" property="rvRevokeState"/>
        <result column="sd_revoke_state" jdbcType="INTEGER" property="sdRevokeState"/>
        <result column="send_date" jdbcType="TIMESTAMP" property="sendDate"/>
        <result column="send_is_auto" jdbcType="BIGINT" property="sendIsAuto"/>
        <result column="task_nature" jdbcType="INTEGER" property="taskNature"/>
        <result column="task_source" jdbcType="BIGINT" property="taskSource"/>
        <result column="the_initator" jdbcType="BIGINT" property="theInitator"/>
        <result column="unSatisfy_count" jdbcType="BIGINT" property="unsatisfyCount"/>
        <result column="urgent" jdbcType="BIGINT" property="urgent"/>
        <result column="verification_id" jdbcType="VARCHAR" property="verificationId"/>
        <result column="remark_id" jdbcType="BIGINT" property="remarkId"/>
        <result column="is_repeat" jdbcType="BIGINT" property="isRepeat"/>
        <result column="mtain_reason" jdbcType="INTEGER" property="mtainReason"/>
        <result column="ration_duration" jdbcType="DOUBLE" property="rationDuration"/>
        <result column="warranty" jdbcType="INTEGER" property="warranty"/>
        <result column="use_material" jdbcType="INTEGER" property="useMaterial"/>
        <result column="check_material" jdbcType="BIGINT" property="checkMaterial"/>
        <result column="build_address" jdbcType="VARCHAR" property="buildAddress"/>
        <result column="device_codes" jdbcType="VARCHAR" property="deviceCodes"/>
        <result column="is_warning" jdbcType="BIGINT" property="isWarning"/>
        <result column="labour_charges" jdbcType="DOUBLE" property="labourCharges"/>
        <result column="material_price" jdbcType="DOUBLE" property="materialPrice"/>
        <result column="mtain_level" jdbcType="INTEGER" property="mtainLevel"/>
        <result column="total_price" jdbcType="DOUBLE" property="totalPrice"/>
        <result column="is_task_checkd" jdbcType="INTEGER" property="isTaskCheckd"/>
        <result column="access_date" jdbcType="TIMESTAMP" property="accessDate"/>
        <result column="addr_id" jdbcType="BIGINT" property="addrId"/>
        <result column="create_warning_date" jdbcType="TIMESTAMP" property="createWarningDate"/>
        <result column="delivery_date" jdbcType="TIMESTAMP" property="deliveryDate"/>
        <result column="device_rule_id" jdbcType="BIGINT" property="deviceRuleId"/>
        <result column="is_warning_count" jdbcType="BIGINT" property="isWarningCount"/>
        <result column="patrol_sys_id" jdbcType="BIGINT" property="patrolSysId"/>
        <result column="recieve_warning_date" jdbcType="TIMESTAMP" property="recieveWarningDate"/>
        <result column="send_warning_date" jdbcType="TIMESTAMP" property="sendWarningDate"/>
        <result column="team_id" jdbcType="BIGINT" property="teamId"/>
        <result column="send_flag" jdbcType="INTEGER" property="sendFlag"/>
        <result column="finish_duration" jdbcType="DOUBLE" property="finishDuration"/>
        <result column="is_return_call" jdbcType="INTEGER" property="isReturnCall"/>
        <result column="reason_main_id" jdbcType="VARCHAR" property="reasonMainId"/>
        <result column="reason_name" jdbcType="VARCHAR" property="reasonName"/>
        <result column="reason_summary" jdbcType="VARCHAR" property="reasonSummary"/>
        <result column="total_cate_number" jdbcType="BIGINT" property="totalCateNumber"/>
        <result column="is_legacy" jdbcType="INTEGER" property="isLegacy"/>
        <result column="man_hour" jdbcType="DOUBLE" property="manHour"/>
        <result column="waring_record_id" jdbcType="VARCHAR" property="waringRecordId"/>
        <result column="from_type" jdbcType="VARCHAR" property="fromType"/>
        <result column="machine_qr_id" jdbcType="VARCHAR" property="machineQrId"/>
        <result column="if_paid" jdbcType="VARCHAR" property="ifPaid"/>
        <result column="img_urls" jdbcType="VARCHAR" property="imgUrls"/>
        <result column="from_bgy" jdbcType="BIT" property="fromBgy"/>
        <result column="is_sendeddestorywarnmsg" jdbcType="INTEGER" property="isSendeddestorywarnmsg"/>
        <result column="is_sendedstorecheckwarnmsg" jdbcType="INTEGER" property="isSendedstorecheckwarnmsg"/>
        <result column="is_third" jdbcType="VARCHAR" property="isThird"/>
        <result column="third_party_areaid" jdbcType="VARCHAR" property="thirdPartyAreaid"/>
        <result column="third_party_houseid" jdbcType="VARCHAR" property="thirdPartyHouseid"/>
        <result column="third_party_ownerid" jdbcType="VARCHAR" property="thirdPartyOwnerid"/>
        <result column="third_party_owneridfhh" jdbcType="VARCHAR" property="thirdPartyOwneridfhh"/>
        <result column="third_party_taskid" jdbcType="VARCHAR" property="thirdPartyTaskid"/>
        <result column="third_party_type" jdbcType="BIGINT" property="thirdPartyType"/>
    </resultMap>

    <resultMap id="AggregateBySourceResultMap" type="cn.com.countrygarden.bi.main.model.eba.TbmMaintainTaskResult">
        <result property="detailName" column="detail_name"/>
        <result property="organName" column="organ_name"/>
        <result property="cateName" column="cate_name"/>
        <result property="createMonth" column="create_month"/>
        <result property="repairUser" column="repair_user"/>
        <result property="taskState" column="task_state"/>
        <result property="taskStateName" column="task_state_name"/>
        <result property="quantity" column="quantity"/>
        <result property="curDateQuantity" column="curDateQuantity"/>
        <result property="takingInTime" column="takingInTime"/>
        <result property="finishedInTime" column="finishedInTime"/>
        <result property="finished" column="finished"/>
        <result property="recieved" column="recieved"/>
        <result property="rationDuration" column="ration_duration"/>
        <result property="finishDuration" column="finish_duration"/>
        <result property="engineering" column="engineering"/>
        <result property="ifPaid" column="if_paid"/>
        <result column="total_price" jdbcType="DOUBLE" property="totalPrice"/>
        <result column="latitude" property="latitude"/>
        <result column="longitude" property="longitude"/>

    </resultMap>

    <select id="aggregate" parameterType="Map" resultType="Map">
        <![CDATA[
SELECT
	COUNT(
IF
	( t.send_date IS NOT NULL AND TIMESTAMPDIFF( SECOND, t.create_date, t.send_date ) <= 600, t.task_id, NULL )
	) AS takingInTime,
	COUNT(
IF
	( t.complete_date IS NOT NULL AND TIMESTAMPDIFF( SECOND, t.create_date, t.complete_date ) <= 172800, t.task_id, NULL )
	) AS finishedInTime,
	COUNT( IF ( t.total_price > 0, t.task_id, NULL ) ) paidQuantity,
	Sum( IF ( t.task_state IN ( 4, 5 ) AND t.total_price > 0, t.total_price, 0 ) ) paidAmount,
	count( IF ( t.task_state IN ( 0, 1, 2, 3 ), t.task_id, NULL ) ) unfinished,
	count( IF ( t.task_state IN ( 4, 5 ), t.task_id, NULL ) ) finished,
	count( t.task_id ) AS quantity
FROM
	tbm_maintain_task AS t
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null and before != null">
                <![CDATA[
            AND t.create_date BETWEEN #{after, jdbcType=VARCHAR} AND #{before, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
    </select>

    <select id="aggregateByOrgan" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
	a.area_name AS organ_name,
	a.area_id,
	a.area_code,
	-- 及时接单数
	count(
IF
	( t.send_date IS NOT NULL AND TIMESTAMPDIFF( SECOND, t.create_date, t.send_date ) <= 600, t.task_id, NULL )
	) takingInTime,
	-- 及时完成单数 60 * 60 * 48
	count(
IF
	(
	t.task_state IN ( 4, 5 )
	AND TIMESTAMPDIFF( SECOND, t.create_date, t.complete_date ) <= 172800,
	t.task_id,
NULL
	)
	) finishedInTime,
	-- 工单数
	COUNT( t.task_id ) quantity,
	-- 当日工单数
	COUNT( IF ( DATE_FORMAT( t.create_date, '%Y-%m-%d' ) = CURDATE( ), t.task_id, NULL ) ) AS curDateQuantity
FROM
	tbm_maintain_task t
	LEFT JOIN project_tab AS p ON ( t.project_id = p.project_id AND p.state = 1 )
	LEFT JOIN tbb_area AS a ON ( p.area_id = a.area_id AND a.state = 1 )
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null and before != null">
                <![CDATA[
            AND t.create_date BETWEEN #{after, jdbcType=VARCHAR} AND #{before, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	a.area_id
ORDER BY
	quantity DESC
        ]]>
    </select>

    <select id="aggregateBySource" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
IF
	( d.detail_name IS NULL, '其他', d.detail_name ) AS detail_name,
	count( t.task_id ) quantity
FROM
	tbm_maintain_task AS t
	LEFT JOIN ts_dict_detail d ON ( t.third_party_type = d.detail_id AND d.state = 1 )
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null and before != null">
                <![CDATA[
            AND t.create_date BETWEEN #{after, jdbcType=VARCHAR} AND #{before, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	t.third_party_type
        ]]>
    </select>

    <select id="aggregateByCategory" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
	c.cate_name,
	count( t.task_id ) AS quantity
FROM
	tbm_maintain_task AS t
	LEFT JOIN tbm_maintain_cate AS c ON ( t.cate_id = c.cate_id AND c.state = 1 )
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null and before != null">
                <![CDATA[
            AND t.create_date BETWEEN #{after, jdbcType=VARCHAR} AND #{before, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	t.cate_id
ORDER BY
	quantity DESC
        ]]>
    </select>

    <select id="aggregateByMonth" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
	LEFT ( t.create_date, 7 ) AS create_month,
	count( t.task_id ) AS quantity,
	count( IF ( t.task_state IN ( 4, 5 ), t.task_id, NULL ) ) AS finished,
	count( IF ( t.send_date IS NOT NULL, t.task_id, NULL ) ) AS recieved,
	sum( IF ( t.ration_duration IS NOT NULL, t.ration_duration, t.finish_duration ) ) AS ration_duration,
	sum( IF ( t.task_state IN ( 4, 5 ), TIMESTAMPDIFF( SECOND, t.create_date, t.complete_date ), 0 ) ) / 3600 AS finish_duration
FROM
	tbm_maintain_task t
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null">
                <![CDATA[
            AND t.create_date >= #{after, jdbcType=VARCHAR}
                ]]>
            </if>
        </where>
        <![CDATA[
GROUP BY
	LEFT ( t.create_date, 7 )
        ]]>
    </select>

    <select id="realtime" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
	concat( p.project_name, t.location, LEFT ( t.repair_user, 1 ), '**' ) AS repair_user,
	t.task_state,
	t.if_paid,
	t.total_price,
	c.cate_name,
CASE

		WHEN t.task_state = 0 THEN
		'未派单'
		WHEN t.task_state = 1 THEN
		'待接单'
		WHEN t.task_state = 2 THEN
		'维修中'
		WHEN t.task_state = 3 THEN
		'待料中'
		WHEN t.task_state = 4 THEN
		'完成待销'
		WHEN t.task_state = 5 THEN
		'已销单'
	END AS task_state_name,
IF
	( ISNULL( p.latitude ), '', p.latitude ) AS latitude,
IF
	( ISNULL( p.longitude ), '', p.longitude ) AS longitude,
	a.area_name
FROM
	tbm_maintain_task t
	LEFT JOIN project_tab p ON ( t.project_id = p.project_id AND p.state = 1 )
	LEFT JOIN tbm_maintain_cate c ON ( t.cate_id = c.cate_id AND c.state = 1 )
	LEFT JOIN tbb_area AS a ON ( p.area_id = a.area_id AND a.state = 1 )
        ]]>
        <where>
            <if test="isAvail == null">
                <![CDATA[
            AND t.is_avail = 1
                ]]>
            </if>
            <if test="isAvail != null">
                <![CDATA[
            AND t.is_avail = #{isAvail, jdbcType=BIGINT}
                ]]>
            </if>
            <if test="after != null and before != null">
                <![CDATA[
            AND t.create_date BETWEEN #{after, jdbcType=VARCHAR} AND #{before, jdbcType=VARCHAR}
                ]]>
            </if>
            <if test="taskState != null and taskState == 0">
                <![CDATA[
            AND t.task_state IN (0, 1)
                ]]>
            </if>
            <if test="taskState != null and taskState == 1">
                <![CDATA[
            AND t.task_state IN (4, 5)
                ]]>
            </if>
        </where>
        <![CDATA[
ORDER BY
	t.operate_date DESC
        ]]>
        <if test="limit > 0">
            <![CDATA[
            LIMIT #{limit, jdbcType=INTEGER}
                ]]>
        </if>
    </select>

    <select id="aggregateEngineering" parameterType="Map" resultMap="AggregateBySourceResultMap">
        <![CDATA[
SELECT
	count( DISTINCT IF ( tdd.detail_code = 'engineering', ut.user_id, NULL ) ) engineering
FROM
	user_tab ut
	INNER JOIN tr_job_user tju ON ut.user_id = tju.user_id
	INNER JOIN ts_job_arch tja ON tju.job_id = tja.job_id
	INNER JOIN ts_dict_detail tdd ON tja.job_dept = tdd.detail_id
	INNER JOIN ts_dict td ON tdd.dict_id = td.dict_id
WHERE
	td.dict_code = 'jobDepartment'
        ]]>
    </select>
</mapper>